List Box - displays lookup field as number not associated value

[razorking]

Probably bad explanation for the title. What I have is:

I have a table where one of the fields is a lookup field. It looks up a value from another table, like open, cancelled, awarded, lost. Then I have a query that is used on a form for a list box. The lookup field on the list box shows the numeric values associated with the list box items (1,2,3 or 4) instead of the values -open, cancelled, awarded and lost.

Very annoying...what can I do about that?

 

[boblarson]

1. do you have lookups defined at table level? If so, get rid of them.

2. Once you have no lookups at table level, then make sure to base the listbox rowsource on a QUERY and not the table. Include the values from your table which stores the lookup values.

3. Make sure your column count, and column widths are such to include the right fields.

 

[Kryst51]

in your list box query add in the table that contains the pk of the fk in the list box then add the field to the QBE that you want displayed, then adjust your columns in the list box properties. Column number and widths.

 

[razorking]

1. do you have lookups defined at table level? If so, get rid of them.

2. Once you have no lookups at table level, then make sure to base the listbox rowsource on a QUERY and not the table. Include the values from your table which stores the lookup values.

3. Make sure your column count, and column widths are such to include the right fields.

Thank you both for the replies, I am pondering now. I do have one question for Bob:
Are you saying I should not use lookups at the table level? That may be fine but how do enter data in the underlying table when I want to have the user select a value from a combo box? I have a data entry form that a user will use to input data. Right now one of the fields is looking up values from another table by use of the lookup wizard that is in the table design for this field. Is there a better way to do that??

 

[Kryst51]

Deleting the table lookup in your table design will not mess up combo boxes that are already done.

You can also still create lookups on forms that will store fks in your table by the use of the combo box wizard.

 

[boblarson]

Thank you both for the replies, I am pondering now. I do have one question for Bob:
Are you saying I should not use lookups at the table level? That may be fine but how do enter data in the underlying table when I want to have the user select a value from a combo box? I have a data entry form that a user will use to input data. Right now one of the fields is looking up values from another table by use of the lookup wizard that is in the table design for this field. Is there a better way to do that??

Data entry should be done from FORMS (which CAN have combo boxes to get the ID number and store it when the user selects the NAME). Users should not be working directly in tables (and I would say queries too). Use forms as they are the user interface and you have control over things, including events.

 

[boblarson]

And Kryst51 said basically the same thing I said - just didn't see it had been said before me

 

[razorking]

I got it...think I actually knew that once and sort of forgot.