Multi-table form 2

[owrsome]

I want to enter data in three related tables through the same form.

The tables are:
Vendors, with fields named vendorID (key), vendorname and city;
Products, with fields named productID (key), productname, and topic; and
WhoSellsWhat, with fields vendorID, productID, and relationship.

The WhoSellWhat table is there to relate vendors who may sell multiple products and products that may be handled by multiple vendors. Each WhoSellsWhat record identifies the relationship between a vendor and a product, e.g. manufacturer, retailer, dealer.

In addition, every product is assigned a Topic (from the Topics table) for sorting purposes.

I can not get the wizard to make a form that permits entering vendorname, city, productname, topic, and relationship. Sometimes it won't take the data, but when it does it won't properly update the three tables and check the Topics table to validate the topic.

Any ideas?

 

[fneily]

Connect your tables through a query (output all fields = yes) then base your form off the query. Your form will have all the fields. Then when you input a productID or a vendorID, it will automatically fill in the fields for your junction table (WhoSellsWhat). Also you could make your topic a dropdown box (keeps things consistent.

Neil

 

[owrsome]

Thanks. It works! But it brings me to another problem. I want each Vendorname and each Productname in the underlying tables Vendors and Products to be unique. This will prevent me entering Vendor A with Product A, forget that I entered it, then enter Vendor A with Product B, and wind up with two vendors named A.

However, I should be able to use my form to call up Vendor A and add Product B. This would give me a new record in the Products table and a new record in the Vendor/Products Query, but would use an existing record in the Vendors Table.

The same should be true in reverse, if I were adding a second vendor to an existing product.

Because I have made the Vendorname unique in the Vendor table and the Productname unique in the Products table (Indexed = Yes - no duplicates), the form refuses to accept an existing vendor paired with a new product ("because they would create duplicate values in the index, primary key, or relationship&quot.

Thanks for your help!

 

[fneily]

First, vendername is connected to vendorID and productname is connected to productID. VendorId and ProductID are primary keys so unless a vendorname can have more then one vendorID, and productname can have more then one productID, there should be no duplicates (ie. they're unique).
You're also saying that a vendor can only sell one product (vendorA sells productA and cannot sell productB). Interesting. Usually vendors sell multiple products.

Your WhoSellsWhat table should have a multi-field primary key, both VendorId AND ProductID. So the WhoSellsWhat table will LOOK like it has duplicate VendorID's. But that's not the KEY. You must "glue" both fields together to get the uniqueness.

Try that. Create a multi-field primary key in the WhoSellsWhat table. Your Vendor table will have unique vendorID's, your product table will have unique productID's and then they combine in the WhoSellsWhat table.

Neil

 

[owrsome]

Thanks, Neil. I changed the WhoSellsWhat table's key to multi-field keys (vendor and product ID's). When I use the vendors/products form to enter a new product for a vendor already in the vendor table, I get the "because they would create duplicate values" error.

I'm not sure I understand the first sentence of your last post. I'd have thought it was possible for a vendorname to have more then one vendorID in the table unless I set the vendorname field property to (Indexed = Yes - no duplicates) as I have done. Should I be doing something different regarding duplicates in the underlying tables?

(I may not have made things clear in my last post. Yes, I do want to list a vendor as selling multiple products and a product as sold by multiple vendors.)

 

[fneily]

Ohhh. I think I see it now. Because we placed everything on one form, when you enter a new product, you are also entering a old vendor. Thus you get a duplicate error on the vendor when you save it.

You may have to do it this way. I used this way at a company around here.

Create three forms: One form for the Product table, one form for the Vendor table and a third form for WhoSellsWhat. This last one will have three fields on it - vendorID, productID, topic.

On the WhoSellsWhat form, next to the three fields, delete the labels and create command buttons (use the wizard) that open the related forms. So, next to the text box for Product, the command button will open the Product form. Next to the text box for the Vendor, it will open the Vendor form.

On the product form, go to design view and open the property sheet for the form. Click on the event tab and click in the box next to OnCurrent. Select code builder and in the VBA window type:

Forms![WhoSellsWhatFormNAme]![ProductID] = Me![ProductID]

On the vendor form, go to design view and open the property sheet for the form. Click on the event tab and click in the box next to OnCurrent. Select code builder and in the VBA window type:

Forms![WhoSellsWhatFormNAme]![VendorID] = Me![VendorID]

On the product and vendor forms create a command button to return to the WhoSellsWhat form.

This way you can now add new products and vendors and bring them together through the WhoSellsWhat form.

If you need to see a file, send me your email address and I'll email an example back to you.

Neil fneily@hotmail.com

 

[owrsome]

I can see how separate forms for separate tables can reduce the problems I'm having, but I would be grateful if you can send me the example you offered. I can't seem to get the thing to work as you specify it, and I need to find out where I'm screwing up. Many thanks.

 

[judgehopkins]

owrsome, Neil did an exceptional amount of work for you.

Don't you think he deserves a star?

Judge Hopkins

http://www.psc.state.mo.us/adjudication.asp

There are only two rules for success: (1) Never tell everything you know.

 

[fneily]

I have a small database file showing you what I mean. Email me at fneily@hotmail.com and I'll send it to you.

Neil