Update a refrence table

[Guest]

Hello:
I have three tables: A and C are many to many relationship; B is the
reference table to link A and C.
A B C
A_ID A_ID C_ID
C_ID
I now have a form for A table. In this form, I use a combo box to allow user
to pick C_ID. My question is how I can insert the A_ID and C_ID into table B.
Or, is there any other better way to solve this kind of situation?
Thanks in advance!

 

[John Vinson]

Hello:
I have three tables: A and C are many to many relationship; B is the
reference table to link A and C.
A B C
A_ID A_ID C_ID
C_ID
I now have a form for A table. In this form, I use a combo box to allow user
to pick C_ID. My question is how I can insert the A_ID and C_ID into table B.
Or, is there any other better way to solve this kind of situation?
Thanks in advance!

Remove your combo box.

Instead, put a Subform onto the Form based on A; base the Subform on
B, using A_ID as the master/child link field. Put your B combo box
onto this subform. If you have a C_ID field in table A, or if you're
basing your form on a query joining A and B, you're on the wrong
track!

See the Orders sample form in the Northwind sample database for an
example - it has A = Orders, B = OrderDetails, C = Products.

John W. Vinson[MVP]

 

[Guest]

Hi John:
My table A does not include C_ID. It only has a A_ID linking the table B.
I did try what you suggested at first, but my situation is I would like the
user to be able to choose a C_ID from a dropdown list based on table C
because there are too many records in table C. By using the method you
suggested, the user would have to memorize the C_ID he/she is entering.
Thanks!

 

[John Vinson]

Hi John:
My table A does not include C_ID.

My confusion was because you said you had a combo on the A form, based
on table C. This combo cannot possibly be of any use, since table A
does not contain a C_ID - there is nothing in the table for the combo
box to store its value into.

It only has a A_ID linking the table B.
I did try what you suggested at first, but my situation is I would like the
user to be able to choose a C_ID from a dropdown list based on table C
because there are too many records in table C. By using the method you
suggested, the user would have to memorize the C_ID he/she is entering.

The method I suggested DOES use a dropdown box (on the subform) to
store the C_ID, while displaying the meaningful text from the C table.
Please reread my suggestion. It is absolutely NOT the case that the
user would have to memorize - or even *see* - the C_ID.

John W. Vinson[MVP]

 

[Guest]

Hello:
I got your idea. My original idea was to find a way to insert a combo box
(C_ID in table C) in the form A, and this combo box is purely to provide the
C_ID to users, so that the table B will be updated. I thought that some VB
codes would do it.
It seems my idea is not feasible and I have to use the way similar in the
Northwind database.
Thanks!