show name in a combo box but store id (1 Viewer)

[alvesi]

hi Isac,

you're welcome! wonderful, thanks for letting me now.

> similar parameter configuration for a look up on a table?

not exactly sure what you're asking

... to reference a column of a combobox value, use
=me.controlname.column(#)
in ControlSource of another control such as a textbox

... to specify that the first column with the ID number isn't what shows in the combo, in the ColumnWidths, set the first column width to be 0 (zero);

... if you want to display a SAVED value in a table, you can use a domain aggregate function in the ControlSource of a combo ( =dSum(...), = DFirst(...), etc )

Hi Strive4Peace

Sorry about my delay to reply, it is the time zone diference.

As I am not skilled in MS Access, I might have not explained myself sufficiently clear.

Below I am adding an example.

On table SOURCES I enter the “Data 1a”.

On table CROSS-REFERENCE I have a look up combo box field where I select Data 1 to combine with new data.

On table SCENARIO BUILDER I have another look up combo box field where I select “Data 1a” again but now the one that have been combined with extra data on the table CROSS-REFERENCE.

Happens that when I use the look up combo box on the table to select the “name text” it only show me the “id number”. It happens on both tables, the CROSS-REFERENCE and SCENARIO BUILDER.

My question was what parameter canuse on the “look up combo box” that I configured on a table, that when I select the data that will be stored, it shows the “name text” .

cheers
Isac

 

[Gasman]

DO NOT USE LOOKUP FIELDS ON TABLES.
Use a query for the combos with ID, description. You store the iD, and show the description.

From a simple Google

MS Access 2003: Bind Combo Box to Primary Key but display a Description field

This MSAccess tutorial explains how to bind a combo box to a primary key (ie: autonumber field) but display a description field in the combo box results in Access 2003 (with screenshots and step-by-step instructions).

www.techonthenet.com

 

[alvesi]

DO NOT USE LOOKUP FIELDS ON TABLES.
Use a query for the combos with ID, description. You store the iD, and show the description.

From a simple Google

Hi Gasman

Thank you for your reply.

Please, correct if I am wrong, but the query will be a rather static view of a combination of data, right?

I use tables as I do need to store each of the combinations and play with that changing and adapting combinations all the time.

i understand that when we put a query together, it will run and will creat some kind of snapshot of that combination which can not be dynamically modified but is rather static.
On this way, what would be the alternative ?

Cheers
Isac

 

[Minty]

The query is no more static than a table view. Both are only accurate when you either open it or refresh it.
Lookups in tables create a false sense of "easy programming" but generally achieve the exact opposite.
Have a read here

The Access Web - The Evils of Lookup Fields in Tables

The method Gasman describes is the correct way to achieve your goal.

A form displaying what you need would use the lookup table as a source for a combo and the form/ query should be completely editable.

 

[alvesi]

The query is no more static than a table view. Both are only accurate when you either open it or refresh it.
Lookups in tables create a false sense of "easy programming" but generally achieve the exact opposite.
Have a read here
The method Gasman describes is the correct way to achieve your goal.

A form displaying what you need would use the lookup table as a source for a combo and the form/ query should be completely editable.

Hi Minty

Thank you.
I’ll also try this configuration you mentioned and will inform how it went through.
Cheers
isac

 

[Pat Hartman]

PS, I'm hoping your actual column names are better than what you posted. Because you are using Lookup fields, I'm pretty sure that your relationships are not correct either. It is only confusing to use "ID" as the PK name for each table instead of giving the PK a real name. They could be SourceID, CrossRefID, ScenarioID, ResourcesID. Then the FK should use the same name or if that confuses you, use the PK name with "_FK" as a suffix.

 

[alvesi]

The query is no more static than a table view. Both are only accurate when you either open it or refresh it.
Lookups in tables create a false sense of "easy programming" but generally achieve the exact opposite.
Have a read here

The Access Web - The Evils of Lookup Fields in Tables

The method Gasman describes is the correct way to achieve your goal.

A form displaying what you need would use the lookup table as a source for a combo and the form/ query should be completely editable.

Hi Minty

Especially your last comment "A form displaying what you need would use the lookup table as a source for a combo and the form/ query should be completely editable." Just made all difference and I could understand Gasman proposition. And solve my problem.

Challenges of being a total beginner.

Cheers
Isac

 

[alvesi]

PS, I'm hoping your actual column names are better than what you posted. Because you are using Lookup fields, I'm pretty sure that your relationships are not correct either. It is only confusing to use "ID" as the PK name for each table instead of giving the PK a real name. They could be SourceID, CrossRefID, ScenarioID, ResourcesID. Then the FK should use the same name or if that confuses you, use the PK name with "_FK" as a suffix.

Thanks Pat, it helped really a lot!

 

[Pat Hartman]

You're welcome

 

[strive4peace]

hi @alvesi

re: "when I use the look up combo box on the table to select the “name text” it only show me the “id number”"

You can fix that by setting the ColumnCount, ColumnWidths, and ListWidth properties

Here's a short video to explain it:
Combo Box properties in Microsoft Access (11:25)