Form list based on a query returns original ID value, not field text

[JamesWB]

This is odd.

I have a table x where the field value is selected via a combo box in a form that is populated from another table z.

When I look in x, it appears to have correctly stored the text from z, not the ID number.

I then built a Query, qX, which looks in x and grabs the fields I want. That query shows the text correctly in each selected field.

Now when I build a form, frmQx and use a List control, it displays some of the fields as ID values from the original table z, not the text values.

How can I get round this? I've searched and searched for an answer, sigh. Maybe I'm just not quite certain how to phrase the search. :banghead:

 

[JamesWB]

Sounds like you have fallen into the trap of using lookup fields in your Tables.

You can correct it by changing your list box.

Open the property sheet of your list box, click on the "format" tab, set column count to two(2), set column widths to 0cm; 2.54cm

No, doing that has no effect and anyway in the List box I want 6 selected columns. Strangely, the only one that was a lookup in the original table displays correctly!

One thing I do notice is that the query on which the list box is drawing also shows the combo box options for each field. I presume it's pulling these via a table relationship in each case. I am puzzled by this. I would have thought a query should just produce a basic static table of results each time?

Here's what I did.

A standard form, frmA...

Contains 6 combo boxes - these are populated from fields in a number of tables tblZ1 - tblZ6. Each of those tables contains two fields, an autonumber ID field and the text string field.
.
A value is selected in each case and loaded into table tblB, fields 1-6.

I then have a simple select query, currently with no criteria, qryEditSelect. This picks up those six values and they display correctly when the query is opened, eg, as the selected text strings chosen in frmA. (I need it to be a query because I intend to add criteria.)

I then base a list box in frmB on qryEditSelect. The columns of the list box shown the position numbers of the combo box selection, not the original text strings.

 

[JamesWB]

Ah right, well it's the same problem in that your table and it looks like your query are showing lookup values however your list box is not.

Open your query edit select query based on tablex I presume, in design view and add all of the tables 1 through 6. Drag the ID field of all the 1 through 6 tables on to the related field in table x.

Now drag text fields from the tables 1 through 6 into the query grid and delete the reference to the id and base your listbox on this. See if that works.

One problem with this is that it will return the text value and not related ID.

Ahh, thanks, yeah, I was beginning to realise it was something like that. The InnerJoin shows up in the SQL when I do a select of the two rows in the related table.

I suppose in a way it's quite neat, because it means it only stores a reference to the combo box value in the main table, but it takes some thinking about for a novice like me!

 

[vbaInet]

Open the table in design view > select the field > select the Lookup tab.

Tell us what type of control is in the Lookup tab.

Edit: Now that was pretty slow!!!