Here is the easy one:
4) That stupid Contact_1 Title. Can't find it anywhere.
Open the form "Site" in design view.
Open the form properties
Click on the data tab
Look at the data source property.
Ta-Da!!
-------------------------------------------------------
1) I can't figure out how to get the "Contact Notes" to stay with the site and only the site. I've done this before, but it's not clicking this go around.
The relationship between the two tables is:
Site.TrialID (PK)------->ContactNotes.SiteID (FK)
The Site.TrialID value MUST be in the ContactNotes.SiteID field to link the records.
Right now the ContactNotes.SiteID field is empty so the records are not linked.
-------------------------------------------------------
2) My queries don't work. I assume it's a one-to-many or many-to-many issue, but again, not clicking (been a long time since I did a DB- as evidenced by my version)
"My queries don't work"....... they appear to not have errors.. They open and seem to be working correctly... what am I missing??
-------------------------------------------------------
3) I'm open to suggestions on the best way for the forms to open (will add buttons for new and search) but not sure of the best way to move through them.
basic code:
Code:
Private Sub btnOpenContacts_Click()
DoCmd.OpenForm "contacts"
End Sub
Not sure what you mean by "move through them"...???
BTW, you have a table named "Site" and a form named "Site". Not good to have objects named the same.
Maybe "tblSite" and "frmSite"??????
-------------------------------------------------------
5) Email hyperlink in concatenated
For the query "ContactsConcatenated" try:
Code:
SELECT Contacts.Title, Contacts.LastName, Contacts.FirstName, Contacts.EmailAddress, Contacts.PhoneNumber, Left(CStr([EmailAddress]),InStr(CStr([EmailAddress]),"#")-1) AS strEmail, [LastName] & ", " & [FirstName] & " " & [Title] & " " & [strEmail] & " " & [PhoneNumber] AS Contact1, [LastName] & ", " & [FirstName] & " " & [Title] & " " & [EmailAddress] & " " & [PhoneNumber] AS Contact2, [LastName] & ", " & [FirstName] & " " & [Title] & " " & [EmailAddress] & " " & [PhoneNumber] AS Contact3, [LastName] & ", " & [FirstName] & " " & [Title] & " " & [EmailAddress] & " " & [PhoneNumber] AS Contact4
FROM Contacts
ORDER BY Contacts.LastName, Contacts.FirstName;
-------------------------------------------------------
Things to be aware of:
table "Site":
"Description" & "Type" are RESERVED words and shouldn't be used for object names
table "ContactNotes":
"DateTime" is a RESERVED word and shouldn't be used for object names
In table "Sites", you have a field "Area" that is a Multi-value field (MFV) and several Look up fields.
I NEVER use MVFs, nor do I use Look up FIELDS (different than look up table).
They violate normalization rules.