Combobox and linked tables

[Guest]

This is going to be complicated, kind of.

I have 3 tables (users, certifications, locations) and each has its own PK
ID. The users table is what the other two are joined on to. So the table
structures are like so:

users:
ID_USER (PK), firstName, lastName, cert (FK to ID_CERT), location (FK to
ID_LOC)

certifications:
ID_CERT (PK), certification

locations:
ID_LOC (PK), company, address1, address2, city, state, zip, phone, fax

Now, in my form I have a query that just grabs all the information, no
criteria. Problem is, I need to limit how many "certifications" there are.
So that table is prepopulated with 5 entries.

After I've populated my users table with 5 people (all having different
certifications) then if I go to enter a new user, the certification repeats
itself. This is not what I want.

So the question is this:
Is there a way for me to use a combobox that will show the textual value of
the field certifications.certifications while using the value ID_CERT and
insert the value ID_CERT into the users table and leave the certifications
table untouched?

I know that in mySQL it'd be much easier to do. Essentially, the SQL
statement I need to execute is something like:
INSERT INTO users ('firstName', 'lastName', 'cert', 'location') VALUES
('test', 'user', '3', '9');
INSERT INTO locations ('company', 'address1', 'address2', 'city', 'state',
'zip', 'phone', 'fax') VALUES ('Pseudo', '123 blank way', '', 'Somewhere',
'CA', '12345', '123-456-7890', '000-000-0000');

I'm just having a little trouble figuring why I can't get the combobox to
work as I want. Everything else does!!!

 

[Guest]

I guess you are using the cert table in the query. What you should use is the
cert (FK) field.

In your query use the keyfield nothing else from the cert table.

In your form change the cert field to a combobox. Set it properties to
"Table"= cert table (as rowsource for the combo) with two or three columns
depending on what you want to see. Make sure the ID field is the first field
in the combo.

Because the controlsource is bound to the cert FK field it will write the
selected ID from the combo in the table. This way nothing changes in the cert
table and you can enter as many users you want.

Maurice

 

[Guest]

Wow... totally missed those options in the properties panel. Thanks for the
help. I also figured out that you have to specify which column the combobox
is bound to. So in my case, I left the first column be text, and the next be
the IDs, and bound it to the ID column (2).

Thanks for the pointers!!

~Bret