Query and Listbox display not matching

[AnnaFoot]

Hello,

I have a linking table between two other tables that require a many to many relationship. All i am storing in this linking table are the ids from the original table.

My problem is that i need the set of linking data to be displayed in a list box (or combo box if i have to). I've created a query that shows the required data, and instead of displaying the ids displays helpful information, taken from the original tables. However, if i use this query to populate the list box, it only displays the id, not the associated information from the original tables.

to clarify:
1 West Ham is playing 2 QPR, therefore i have stored in my linking table
1 2

However the 1 and the 2 have no intrinsic meaning, and i would like to display
West Ham QPR

This works when i run the query, but in the list box is shown as 1 2.


I'm sure i could overcome it with a few dlookup() statements, but i am beginning to learn that an overuse of these takes up a lot of resources, so am loathe to do it in this case.

Do i have any other options.

Anna

 

[Mile-O]

You need to select the fields with team names into the query too; not just the ID.

 

[AnnaFoot]

Thanks SJ,

What is odd is that when i ran the query itself, the teams were what displayed, because of the relationships. I was just surprised that they didn't display in the list box. Or that what happened in the underlying query wasn't passed across to the listbox.

I have now added the joined tables to the query, and added the team fields. What is amusing is running the query displays
West Ham QPR West Ham QPR!

Still i don't have to understand why, just know that it does!

Anna

 

[Mile-O]

Probably because you have set the fields - at table level - as comboboxes with a hidden column. When you query this will still read as the combobox values but when you put to a listbox or combobox then the actual value is used.

 

[Pat Hartman]

The ability to add a "lookup" to a table field in design view is a crutch for non-professionals who don't understand what a query is and who don't understand how to join tables. Once you start creating your own queries and writing code, this "feature" becomes more confusing than helpful. In reality, the query MUST return the ID value. The fact that it shows you the text value is what causes the confusion.

Get rid of the "lookups" and create queries that join the tables. Your users shouldn't be looking at tables anyway so this isn't a problem for them.