Autonumber Lookup

[r. howell]

I see (belatedly) the value of a meaningless primary key, but I'm still
floundering a bit on how to make the user interface friendly.

If I have a meaningless primary key, how do I get my users to select it?

I am guessing that this has something to do with those two column lookup
fields. My concern is with entering a foreign key.

Can I have a user choose the name of a company, and the database enter the
autonumbered primary key of that company? How exactly do I make that happen?

Thanks

 

[Nikos Yannacopoulos]

Assuming you have a table called tblCompanies, two of its fields being:
CompID (PK, autonumber)
CompName

Put a combo box on your form, and make its rowsource a query on the
table, including these two fields (in that order);
Set the combo box's properties as follows:
Property On Tab Setting
Column Count Format 2
Column Widths Format 0;5 (or 0;whatever)
Bound Column Data 1

These property settings make the combo box 'include' both fields (Column
Count = 2), but only show the name (the width for the ID column is 0),
while it returns the ID field value (Bound Column = 1).
Notes on the column widths setting:
1. list separator may be comma instead of semicolon in some systems
2. the second column width setting above is just an example; experiment.

HTH,
Nikos

 

[John Vinson]

I see (belatedly) the value of a meaningless primary key, but I'm still
floundering a bit on how to make the user interface friendly.

If I have a meaningless primary key, how do I get my users to select it?

The user should NEVER EVEN SEE IT.

I am guessing that this has something to do with those two column lookup
fields. My concern is with entering a foreign key.

The table should probably *not* have ANY Lookup Fields, and the user
should never see a table datasheet at all. Table datasheets are good
for design and debugging; they are not designed nor intended for user
interface purposes.

Can I have a user choose the name of a company, and the database enter the
autonumbered primary key of that company? How exactly do I make that happen?

The user should see a Form with a Combo Box control on it. The Combo
Box would be based on the Companies table, and would have two columns;
the numeric ID and the company name. The Column Widths property would
be set so that the user sees only the company name; the bound column
would be the (zero width) ID, and on the Form the combo would be bound
to the numeric CompanyID field.

That way the user sees "Acme Products Inc.", the computer sees 3122,
and they both are happier. <g.

John W. Vinson[MVP]

 

[r. howell]

The user should NEVER EVEN SEE IT.

That's what I wanted, just didn't know how to do it.

The table should probably *not* have ANY Lookup Fields, and the user
should never see a table datasheet at all. Table datasheets are good
for design and debugging; they are not designed nor intended for user
interface purposes.

After I posted this question, I saw that you don't like Lookup Fields in
tables. My users don't work in my tables directly, so I'll use combo boxes
in the future. This "zero width" column business is helpful.

Thanks so much to both of you for helping this novice.