Combo Box on Form; Referenced field value rather than ID number

[StephenJ]

I have 3 tables in Access; Table-1, Table-2, Table-3



Table-1 -> Table-2 One to Many
When I build Form-2 for data entry to Table-2 my Combo Box field (built by wizard) displays the actual value (from Table-1) instead of the PK from Table-1, as expected.

Table-2 -> Table-3 One to Many
When I create Form-3 (using same steps used to create Form-2) for data entry to Table-3 my Combo Box field will display the PK number rather than the value. When I run the query for the Combo Box field (in from design view) The result is the actual value but still get ID number on form Combo Box list.

On Form-3 how do I get the value to display in my Combo Box rather than the ID number?

The Combo Box field on Form-3 is the same field referenced in the One to Many from Table-1 -> Table-2

[Micron]

It's normal for a bound combo to have 2 columns in these cases. 1st is bound to pk (usually autonumber) and its width is set to zero. The second column displays the values associated with the pk field. The value that is stored in the table (say when you choose "apples") is the pk value for apples in the underlying table record. The order that the table fields are assigned to the columns is the same order as the table or query fields when reading from left to right - at least in most of the world.

[StephenJ]

Understood
But why can't I get my combo on my form for data entry to Table-3 display the same as the Combo on form used for data entry for Table-2. The Combo on Form-3 is bound to the field in Table-2 which is the same field referenced (on the Many side) of Table-1

[Welshgasman]

Are you using table lookups?

[StephenJ]

Are you using table lookups?

Yes, if you mean using the Lookup Wizard

[Micron]

So in table design you chose a field and set its property to be a lookup field? Then that is likely your issue. Lookup fields are usually avoided by experienced db designers. See the 5th link from the bottom in this thread: https://www.accessforums.net/showthr...773#post521773
IIRC, it is possible to get the value instead of the pk ID in a query if you use the .Value property, although you may want to rethink using lookup fields (not the same as a lookup table).

[Welshgasman]

Yes, if you mean using the Lookup Wizard

That will be why then, as really the field holds the PK, not the display value, which is as it should be, but confusing when using table lookups, as it hides that fact.

[mike60smart]

Hi
If you can upload a zipped copy of your database I will show you how it should be constructed.

[StephenJ]

Hi
If you can upload a zipped copy of your database I will show you how it should be constructed.

Uploaded database
Please see DM, thanks

[mike60smart]

Hi Stephen

You should not be using Lookups in table field names.

See the frmReloadAmmo

[StephenJ]

Hi Stephen

You should not be using Lookups in table field names.

See the frmReloadAmmo

Thank you for the learning experience, greatly appreciated

[mike60smart]

Glad to help