Show Multiple Columns in Lookup Field

[KASmith42]

Again, Steve I appreciate your extensive help. Thank you.

After removing the Contact_1 Title.Value, my concatenated fields again just show the first column. I'm going in circles and losing sanity by the hour.

I note you removed the extraneous hyperlink "junk" around the email address in the concatenated field, which is halfway there. Apparently I can't have that email address (once displayed concatenated in the combo box) be an actual clickable hyperlink? So what about a "send email" button next to the combo box. I assume I'd have to write a macro that directed that button to open an email window addressed to that specific contact. Is that a doable thing?

Some of that incorrect naming was due to my quickly stripping identifiable content but I appreciate the reminders.



The query that doesn't work in the example I posted is the ContactsQuery. It comes up empty. I assume it's because I'm struggling with getting the Site.TrialID value auto-populating in the ContactNotes.SiteID. I.e., When I create a record in the ContactNotesSubform, having it automatically assign the current Site.TrialID to that record. I know there's a way to do this, but I can't remember or come up with the right search terms.

[ssanfu]

I'm struggling with getting the Site.TrialID value auto-populating in the ContactNotes.SiteID.

You MUST link the main form with the sub form.
Open form "Sites" in design view.
Click on the subform control for "Contact Notes". (Orange box around the control.
Open properties.
You should see "Link Master Fields" and "Link Child Fields"
Click in the "Link Master Fields" property.
Another dialog box appears - should have "TrialID" and "SiteID" already entered.
Click OK.
Close and save form.
Open form

Once linked, any new contact notes will be linked to the site.
For existing "ContactNotes" records, you will have to delete existing records and reenter them OR go into the "ContactNotes" table and add the appropriate TrialID number to the SiteID field.


-----------------

After removing the Contact_1 Title.Value, my concatenated fields again just show the first column. I'm going in circles and losing sanity by the hour.

"concatenated fields" where??? Query?? Form???

-----------------

I note you removed the extraneous hyperlink "junk" around the email address in the concatenated field, which is halfway there. Apparently I can't have that email address (once displayed concatenated in the combo box) be an actual clickable hyperlink? So what about a "send email" button next to the combo box. I assume I'd have to write a macro that directed that button to open an email window addressed to that specific contact. Is that a doable thing?

Removing the extraneous hyperlink "junk" around the email address changes it from a hyperlink to a string. Plus, the result of the concatenation is being stored in a Text type field.

Probably not with a macro, but could with VBA. Have to think about it. If you were using a sub form, it would be easy. But you would have to change table "Sites" a little....

[KASmith42]

I had linked the Master and Child fields as you described. This is the error message I get when trying to save more than one note to a site.

"concatenated fields" where??? Query?? Form???

When, in the form, I choose "Contact 1" from the concatenated information in the combo box, it shows all the information in the drop down but then after selecting. it only displays the title of the contact chosen.

But you would have to change table "Sites" a little....

That would be fine as long as I can use a dropdown of sorts where all contacts from the contacts table would be visible and then selecting them would display only that contact's information with a clickable hyperlink for the email address. As I said, I'm not wedded to the design of any of it, but this field is a very specific need.

[ssanfu]

That would be fine as long as I can use a dropdown of sorts where all contacts from the contacts table would be visible and then selecting them would display only that contact's information with a clickable hyperlink for the email address. As I said, I'm not wedded to the design of any of it, but this field is a very specific need.

Are the contacts for a specific Site (TrialID) or can any contact be for any Site? In the table "Contacts", there is a field "SiteID" that I appears to be the FK to the "Sites" table.

The problem with the Contact1 - 4 is that the 4 fields are Text type fields. This means that if the phone number changes, you will have to re-select the contact (where ever it is used) - it will not automatically adjust itself to show the new Ph#.



Look at the attached dB and compare it to where you are having problems with the sub-form linking.

[KASmith42]

I haven't looked at your example yet, but one contact could be for multiple sites and the 4 contacts may not be the same from site to site (A,B,C,D at site 1, B; D, E, F at site 2; A, D, F, G at site 3)

Editing to say: your example is fantastic and handled in a way I didn't even know what possible - so THANK YOU for that!

Now... about that email button...

I hope you have a wonderful weekend.

[ssanfu]

I renamed several controls.
I renamed a few fields: "State", "Type", "Description" are reserved words in Access/ACE/JET/SQL.

I still really hate the MVF for the field "Area". It is going to cause you headaches later (IMO)


Enjoy the weekend


See attached

[KASmith42]

You are a rockstar! A superhero! Nay! A god among men!

[KASmith42]

Steve - Is there a way to get the field for "Full Name" on the "Select Contacts" subform to sort alphabetically? I can't figure it out, since the query needs to sort on contact order (SeqNum) and I've sorted every name field I can think of to no avail.

Thanks again!

[ssanfu]

Sort alphabetically in the dropdown list?
In the selected names?
Is the selected name order important?? Or is the "1,2,3,4" unimportant??

I tried to match the functionality of the original form/action/look.....

[KASmith42]

Yes, sort alphabetically in the drop down list rather than by order of data entry. The alphabetization in the drop down would be more important than the contact order. i can see how my "Contact 1, 2, etc" would be misleading - those were just labels to avoid "contact, contact, contact".

[ssanfu]

Like this???

[KASmith42]

YES EXACTLY! (Oy, I'm embarrassed I didn't think of that ORDER BY).

OK - LAST QUESTION, I promise! (I reserve the right to ask questions if the answer to this question raises more questions)
Is there any way to force the "Add Edit Contacts" subform to update without closing and reopening the "Site" form? As I add new contacts, they don't populate in the "Select Contacts" dropdown unless I close and reopen the main form.

Again, THANK YOU! About this time last year, I was in Anchorage. If I get there again, I'm buying you a beverage or seven!

[ssanfu]

The easiest thing would be to have the Add Contacts be a stand alone form. How often are you adding contacts?

That said, is this close??

(you like my suffixes?? s - sort, r - refresh....)

[KASmith42]

Well... yes. In your example it works flawlessly. But I can't replicate it. I copied your code letter for letter and it doesn't work. Is there something more than:

Private Sub Select_Contacts_Click()
Me.Refresh
End Sub

?


I MAY be adding contacts with each new form but I'd really rather keep it in your brilliant configuration because it makes it much more self-explanatory to others.

[KASmith42]

Never mind! I GOT IT! Woot! THANK YOU FOR ALL YOUR HELP! You are the BEST!