Multiple Column Lookup Display- table & form (1 Viewer)

rdavidmurphy · Apr 16, 2011

I have two simple Access 2007 tables and have successfully created a two field lookup using the wizard. I am able to see the two columns in the table or form, and can select and save records that I wish to bind. However, in my table or form display I can only see the second field although I included two fields widths, after using zero width for the bound ID column.

Lookup Properties

Display Control: Combo Box
Row Source Type: Table/Query
Row Source: SELECT [Expats & Apartments].[ID], [Expats & Apartments].[Building], [Expats & Apartments].[Apartment] FROM [Expats & Apartments] ORDER BY [Building], [Apartment];
Bound Column: 1
Column Count: 3
Column Heads: Yes
Column Widths: 0cm;1.27cm;1.565cm
List Rows: 16
List Width: 2.831cm
Limit to Width: yes
Allow Multiple Values: no
Allow Value List Edits: no
List Items Edit Form:
Show Only Row Source Value: no

I wonder if I am missing the obvious here, or not.

Jeanette Cunningham · Apr 16, 2011

The combo setup looks right.
I wonder if the query for the row source is returning the 3 fields you expect.

Create a separate query for the ID, building and apartment and check if it gives you 3 columns.

missinglinq · Apr 16, 2011

rdavidmurphy said:
...However, in my table or form display I can only see the second field...

If you're saying that you can only see one field after a selection has been made from the Combobox this is the expected behavior.

Linq ;0)>

rdavidmurphy · Apr 16, 2011

Thankyou.
I do get a return of all three fields witha query, and also in the table selection lookup I can see the proper two fields, building and apartment.

rdavidmurphy · Apr 16, 2011

Thanks.
Do you mean that the only purpose of multiple fields in the lookup selection query is for the selection through the lookup field, but the fields cannot be displayed this way in the form or table or table row after the selection?

missinglinq · Apr 16, 2011

Only the left-most field will be displayed in the Combobox after a selection is made. To display other columns you either need to use them to populate Textbox controls on your form, or you'll need to use SQL or a Query to Concatenate the fields together, then use that calculated field in your Combobox.

Here's a very short tutorial on assigning Combobox fields to a number of Textboxes:

*******************************

Set up your combobox using the wizard and include the fields you need, from Left-to-Right.

If in the Combobox they appear as

Field1 Field2 Field3

the code would be

Code:

Private Sub YourComboBox_AfterUpdate() Me.txtField1 = Me.YourComboBox.Column(0) Me.txtField2 = Me.YourComboBox.Column(1) Me.txtField3= Me.YourComboBox.Column(2) End Sub[/B]

Notice that the column index is Zero based.

*******************************

Likewise, here's a short tutorial on using the Concatenating approach:

*******************************
If your Combobox is already based on a Query, go into the Query in Design View. If it isn't, you need to go into Design View for Queries and create a Query with the pertinent data from your table. Now go to a blank field in the query grid and type this in:

CombinedFields: [Field1] & " " & [Field2] & " " & [Field3]

Now go into Design View for your form and delete your old Combobox and create a new one. This time, when the Wizard comes up and asks for the Table or Query to retrieve the data from, select your Query. Next choose CombinedFields as the field for your combobox.

Now, after you make a selection from the Combobox, all of the Concatenated fields will be displayed.

*******************************
If your purpose is to only display the data after selection, either method will work.

If you want to use multiple fields from the Combobox to populate the current record in the form's underlying table, you'd have to use the first method above.

Linq ;0)>

rdavidmurphy · Apr 17, 2011

Thank you for the comprehensive answer, I will try these techinques.

missinglinq · Apr 17, 2011

Glad we could help!

Zackerson · Nov 9, 2017

I think I am the guy that missinglinq's monkey is slapping his forehead about. lol.
This forum is a godsend, thank you!

Multiple Column Lookup Display- table & form (1 Viewer)

rdavidmurphy

New member

Jeanette Cunningham

AWF VIP

missinglinq

AWF VIP

rdavidmurphy

New member

rdavidmurphy

New member

missinglinq

AWF VIP

rdavidmurphy

New member

missinglinq

AWF VIP

Zackerson

New member

Similar threads

Users who are viewing this thread