Bosses can be pretty strange sometimes when they don't want to pay for
training, but are willing to pay for your time of trial and error
self-training.
I refer you to Douglas Steele's earlier reply in the other thread:
"You put Vendor information in the Vendor table. You put Category
information
in the Category table. You link vendors and categories in the junction
table.
"A common approach would be to have a form bound to Vendors, linked to a
subform bound to the junction table. The subform would have a combobox based
on the Category table bound to the Category Id in the table."
You need to have relationships for this to work. Your tables will be
constructed something like the following:
tblVendors
VendorID (autonumber PK)
Vendor name, address, etc.
tblCategories
CategoryID (autonumber PK)
Category
tblJunction
JunctionID (autonumber PK)
VendorID (data type = Number; this will be the foreign key once you have
set up the relationships)
CategoryID (same note as for VendorID)
Years_Experience
I have added Years_Experience as an example of the sort of thing that is
specific to the junction table. In this case, Years_Experience is specific
to THIS category for THIS vendor. If you need such information, you need
the junction table. You can use the junction table even if such information
is not needed.
In the Relationships window, establish a relationship between VendorID in
tblVendors and VendorID in tblJunction. Click Enforce Referential
Integrity. Do the same for CategoryID in the two tables.
Note what Douglas suggested: Make a form (frmVendors) bound to tblVendors
(i.e. tblVendors is the Record Source), and another form (frmJunction) bound
to tblJunction. frmJunction will contain a combo box bound to CategoryID.
That combo box's row source will be based on tblCategory. Use the combo box
wizard to get started. What you need to end up with is a combo box based on
a Row Source query (constructed by way of the three dots next to Row Source
in the combo box property sheet; if you used the wizard, the three dots will
let you view the query). Your row source query will contain two columns:
CategoryID in the first column, and Category in the second. The combo box
bound column will be 1, column count will be 2, column widths 0;1" (or
whatever you need for the second column).
Now, in form design view, drag the icon for frmJunction onto frmVendors.
Your problem was that you were trying to enter something into the
tblCategory, but there was no direct relationship between tblVendors and
tblCategory. If you need to add a record to tblCategory (e.g. a new
category) you will need a separate way to add the category. You could add a
command button to frmJunction to open a form based on tblCategory for those
situations when you need to edit tblCategory.
Finally, if a category name changes (e.g. Auto Mechanic becomes known as
Automotive Technician), do you want existing vendor records to reflect the
new name, or the name at the time it was added to the vendor's records?
Even if it is unlikely, what if? It could be important.
Try everything I have suggested. If it does not work, be very specific in
your reply as to what is happening. Above all, for people here to provide
useful answers, you need to be specific about what you have tried. We don't
need to know that vendor information includes city, state, phone, and all
that, but we do need to know about primary keys. When somebody suggest
something, give it a try and report back. I still don't know if your
junction table contains appropriate foreign keys, for instance.
