Combo box displaying record number and not value (1 Viewer)

[KGaccess]

I have a one field table that has PCB names recorded.
I have another table with several fields one of which is a lookup field to select one of the PCB names from the first table. This populates the PCB name OK.
I have a query built on the 2nd table that reports 4 fields including the PCB names lookup from the previous table. The query runs and shows the PCB names.

I have a third table that runs the query as the row source on a field where I want the PCB name to be stored but instead when I open the tables field combo box all I can see in the combo box is row ID's and not the PCB names.

Why is this?

 

[theDBguy]

Hi. I think this is normal and one of the reasons why we don't recommend using lookup fields at the table level.

 

[CJ_London]

using lookups in tables is a bad idea, just use them in forms and reports. What you are experiencing is a typical issue with using lookups in tables. Just because you can, does not make it a good idea.

see this link http://access.mvps.org/access/lookupfields.htm

 

[KGaccess]

Thanks guys. Bit new to this. Appreciate the help.

So in my second level table if I need users to only select certain options (the PCB names) how do I achieve that?

Do I use a value list?

 

[MajP]

So in my second level table if I need users to only select certain options (the PCB names) how do I achieve that?
Do I use a value list

The user should never enter information directly into a table, they should have a form. Using a pull down list in a form is fine, just not the table.

 

[Pat Hartman]

You would never store the name in a second place. You would always store the ID.

Have you started by removing the table level lookup's?.. All combos should be on forms (not usually on reports although you can) and NEVER on tables.

 

[Uncle Gizmo]

Have a look at my blog on combos:-

https://www.niftyaccess.com/combo-box/

See video number 6 - Combobox - Displaying a Number?

 

[Pat Hartman]

Sorry, forgot to include this erlier.

When a combo shows a number rather than the text string, you need to modify the properties. Usually the first column (the "ID") is the bound field. Set its column width to 0 and add a length for the second one. This example uses only two columns in the RowSource. Your RowSource may have more. In the Column Widths property, use 0 to hide a column and a width to show it. So for 5 columns where you want to show fields 2 and 4, the Column Widths would be = 0,3,0,1.0