Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    KASmith42 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    21

    Question Show Multiple Columns in Lookup Field

    Pardon me if this is addressed elsewhere, but I'm not sure how to search on what it is I am trying to accomplish.



    I have a simple table, Contacts, with ID, Title, Last Name, First Name, Email, and Phone columns. In my main table I have created a lookup which references the Contacts table. This works well enough that when I click the drop down for my contact field in the form, my contacts' information (including all columns) from the Contacts table are listed. However, when I select a contact to populate the field, only the value of the Title column for that contact is displayed. I can change the Bound value to another column, but this doesn't help.

    How do I populate the form field so that all contact information is shown? Thank you in advance.

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I would advise NOT using Look up FIELDS in tables.
    On the form, use combo boxes.


    Edit: see
    The Evils of Look up Fields
    http://access.mvps.org/access/lookupfields.htm

    also
    http://access.mvps.org/access/tencommandments.htm

  3. #3
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    If it is not too late, do what Steve suggests.

  4. #4
    KASmith42 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    21
    Thanks for the quick replies. It's not too late, however, the combo box gives me the same information on populating - just the first column from my Contacts table.

  5. #5
    KASmith42 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    21
    Gah. Posted to the wrong thing...
    Last edited by KASmith42; 08-25-2016 at 03:04 PM. Reason: Errant post

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    A combo box can only display one column when not in dropdown mode - more when it is dropped down.
    If you want to display more than one column, you can concatenate the fields you want to display.

    Example:
    You have a query with 2 columns (the combo box ROW SOURCE):
    mbd_pk
    Expr1: [icd_desc] & ": " & [mbd_desc]

    The bound column is 1
    The column count is 2
    The column widths are 0, 1.5

    Expr1 can be any number of fields.

    (Note that in this example, the concatenated fields are from two different tables in the query.)

    Maybe an example dB will help.......
    Attached Files Attached Files

  7. #7
    KASmith42 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    21
    Quote Originally Posted by ssanfu View Post
    A combo box can only display one column when not in dropdown mode - more when it is dropped down.
    Thank you for your response. I definitely want to display more than one column when not dropped down. In the end, I want to be able to select multiple contacts on a page, each from a drop down list, and have each of the contacts' names and email addresses populate the page so I can go back to that page and click on the email address of the desired contact. How do I go about populating the page with contact information?

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Again, you can concatenate the fields you want to display.

    See the combo boxes in the example dB.

    The combo box Row Source is a query. Look at the query in design view.

  9. #9
    KASmith42 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    21
    I thank you so much for your response and example. Unfortunately, this doesn’t work for my needs, as there seems to be no way to sort names alphabetically. If I’m incorrect, please let me know, but I sorted both the table and the query and once concatenated, it seems to sort them by PK, and contacts won’t be entered alphabetically. With over 100 names, that is problematic. Also, it puts HTML tags around the email addresses and drops the hyperlink, which I need.

    Perhaps my question should be: How would you do this?

    For example - Suppose this is a cleaning business. The main form is for each customer establishment, which holds information specific to that establishment (sq footage, special equipment required, schedule, attached contract, etc). What I need is a table of some sort, on that form, where I can choose the contacts for that establishment. The tricky part is that Joe Jones may own establishment A and C. Jim James is co-owner of A and then owns D. Sally Smith is the bookkeeper for C and D, but Bob Roberts is the bookkeeper for A, etc.

    So for each establishment, I need to be able to identify up to 4 contacts from a pre-existing list of contacts with title, name, email address (with hyperlink) and phone number readily visible on the form.

    My plan was a [s]lookup[/s] combo box referencing the contacts table, and if it would actually show what’s in the dropdown, it would be perfect - all the information, sorted alphabetically, with email address as a hyperlink.

    I’m not set in the design if you have a better idea, but my needs for this feature are pretty specific.

    Your advice and expertise are greatly appreciated.

  10. #10
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    You can adjust various properties of your comboboxes. For instance, you can use the Query Builder to adjust the Rowsource Property of a combo. You can change the sort order of various columns using the Query Builder/Designer.
    .
    Click image for larger version. 

Name:	QueryBuilderCombo.jpg 
Views:	40 
Size:	51.9 KB 
ID:	25583

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    as there seems to be no way to sort names alphabetically. If I’m incorrect, please let me know,
    Yup, incorrect.. ....If you are talking about the sort order of the data for a combo box ROW source.

    I modified the query for the patient combo box row source to: (see attached)
    Code:
    SELECT tblPatients.PatientID_PK, [First_Name] & " " & [Middle_Name] & " " & [Last_Name] & " - " & [Gender] & "   /" & [Phone] AS FullName
    FROM tblPatients
    ORDER BY tblPatients.Last_Name, tblPatients.First_Name;
    I added the fields "Gender" and "Phone" to the concatenated field.
    See the BLUE "ORDER BY" clause? That sets the sort order for the query. (Last name, then first name)

    I used a saved query for the patient combo box row source.
    Open the query "qryPatients" in design view and you can see the sort order for last name and first name.




    Maybe it is me that is misunderstanding?
    Attached Files Attached Files

  12. #12
    KASmith42 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    21
    Outstanding! Thank you so very much, Steve!

    Now two more questions:
    1) what about that pesky hyperlink in the concatenated field? I have the data type in the table set to Hyperlink, but that makes it "email#email#" in the concatenated field and not an actual hyperlink.
    2) when I open my main form, I'm getting a prompt "Enter Parameter Value Contact_1 Title.Value" where do I fix this?

    I really appreciate your help!

  13. #13
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    1) I used a dB that I made up to demonstrate concatenation. So I don't know what your dB structure is.
    Do you have a field name with the hash mark? The hash mark is a date delimiter and shouldn't be used in object names.

    Object names should only be letters and numbers:
    NO spaces, punctuation or special characters (exception is the underscore).
    Do not begin an object names with a number.



    when I open my main form, I'm getting a prompt "Enter Parameter Value Contact_1 Title.Value"
    2) Are you using spaces in your object names? "Contact_1 Title.Value" has a space in it.
    Maybe try adding brackets: "[Contact_1 Title]". Best would be to remove spaces in object names.
    (BTW, ".Value" is not needed because it is the default property)



    You could post the SQL of the query.
    Or post the dB for analysis. Only need a few records. Remove/change sensitive data, do a "Compact and Repair", then Zip it.

  14. #14
    KASmith42 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    21
    Steve, I'm going to send you so many Christmas cookies.

    Here it is, though still quite rough.
    I have several issues still:
    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.
    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)
    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.
    4) That stupid Contact_1 Title. Can't find it anywhere.
    5) Email hyperlink in concatenated

    Again, many, many thanks!
    Attached Files Attached Files

  15. #15
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. If NULL NOT SHOW FOR Multiple Columns (VBA)
    By GCLIFTON in forum Queries
    Replies: 4
    Last Post: 06-17-2016, 09:44 AM
  2. Lookup multiple columns in combo box
    By fcp in forum Programming
    Replies: 1
    Last Post: 12-22-2011, 08:44 PM
  3. Replies: 6
    Last Post: 10-20-2011, 11:27 AM
  4. Lookup Columns Multiple Records in report
    By schultzy in forum Reports
    Replies: 1
    Last Post: 01-02-2010, 12:21 AM
  5. Lookup or list field: how Do I not show all records
    By scott munkirs in forum Forms
    Replies: 4
    Last Post: 04-13-2006, 12:44 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums