Update Three Tables w/Form and two subforms

[Barry McCocinner]

I've looked through all the postings I can find on this subject, but can't seem to make it work.


I have three tables Vendor, Prescreen and Contacts. The vendor table has the following fields: ID(PK), VName, VContact, VTitle, VEmail, ContactID (FK). The Prescreen table has the following fields: ID(PK), ProductName, Value, DataSize, Claims, VendorID(FK). The Contacts table is the standard contacts table out of just about any MS canned access db.


I've created a main form that uses the Vendor table as its source, and two subforms for the Contacts and Prescreen tables. The contacts subform uses the Contacts Extended query for its source (another canned MS access piece) so I can use the Contact Name expression and automatically populate the rest of the contact information when I select the contact name from the dropdown.



When I open the form and try to enter data few things happen. The subform containing the prescreen fields look like they are blocked from entering data, but as soon as I enter data on of the vendor fields they accept data entry. The second issue is the subform containing the contact info with the dropdown will not let me change the name. I get the error "Field 'Contact Name' is based on an expression and cant be edited." I pretty sure there's a way around that but I can't figure it out.


Any help would be appreciated. I'm new at this (clearly) and this has made me :banghead: the last few days.

 

[JHB]

Could you post your database, zip it because you haven't post 10 post yet?

 

[June7]

What are table relationships?

Each vendor can have many contacts? Each contact can associate with many vendors?

Same question for Prescreen and Vendors.

 

[theDBguy]

Hi. Calculated columns, like concatenated first and last names, cannot be edited. The only workaround is to use bound controls.

 

[Barry McCocinner]

JHB/June7,


Thanks for offering to look. I've attached the db as a zip.


TheDBGuy,


The field is already bound to a control. To me it's one of those odd things. I'm not "editing" the data as in typing in new data, but it is being changed since I'm changing the data through the dropdown.

 

[June7]

Appears to be a circular relationship which may or may not be valid. Why is Contacts related to both tblVendor and tblPrescreen? And then tblVendor and tblPrescreen are also related.

Neither of these subforms makes sense. With these relationships, Contacts and tblPrescreen should be lookup sources for comboboxes on frmMain.

Cannot link tables/forms on autonumber fields, which is what is done in the Contacts subform container. An autonumber primary key should link to a number foreign key.

Can you answer questions in my previous post?

 

[Barry McCocinner]

June7,


Thanks for the pointers...still trying to muddle through this.

To answer your earlier question: Each vendor can have many contacts? Each vendor will likely have only one contact
Each contact can have many vendors
Prescreen can have many contacts and many vendors, and likewise a vendor may have many prescreens.

I'm not sure at all how the form stuff works. The linkage was what was suggested to me by the wizard.

 

[June7]

Okay, relationship between tblVendors and Contacts is okay. Use a combobox on frmMain to select contact.

However, tblPrescreen and tblVendors appear to have many-to-many relationship. This requires a third 'junction' table.

Suggest you study some tutorials on database relationships. Here is one https://www.lifewire.com/database-relationships-p2-1019758

Yes, the wizards will try to work with what you build. I seldom use them.