Full Version: One form multiple table data entry
UtterAccess Forums > Microsoft® Access > Access Forms
Hi, Help, and thanks
uilding a form to view/update existing data with subforms from different tables is easy. Getting the data Entry to work for all tables is drving me crazy.
Ocan't figure out the best method for Adding new records because of subforms and dependencies on having FK IDs before entering/leaving subform. There is a FK that is in a subform and I can't get into the subform to select a value.
Attached is snapshot of relationship and form.
If I have 3 different tables to update, what is the best practice.
CompanyContact Form
Has its own data
Has Company ID/Name subform
Has Telephone Number Subform
I have tried a gazillion different things. Frist, I tried using frmCompanyContactUpdate (vice the just add new reord). In this form I had companyID linked via Link Child/Master Fields. So, I can't use the frmCompanyContactUpdate form to get Company ID for new record becasue there is not a CompanyID until I enter one.
Next, I tried just having a selection list for company names/ID that is not linked to child/master. I could not get to company name subform from Company contact form without the error "You can't add or change a record because a realted record is required int tblCompanies"
Now I am trying to write a form to just get company name first and pass to companycontact form. But, wanted to see what is the best practice to do DATA ENTRY on multiple tables.
Welcome to UA!-o!
could you copy your db, compact & repair, then zip it so we could look at it?
It is sometimes easier to give advice when we can actually see what you have.
Looking forward to seeing your db.
One way to do this would be to disallow entering a new record into the subforms until the record has been added to the main form. So you could give them a save button on the main form that when clicked stores the new record in the table, at least the key value that you need. Then activate the subform controls.
Thanks! In my case I was not adding a new record in 2nd subform, but instead picking an existing other table record. But, I can still apply your suggestion with different words.Putting all the required selections first in a popup and then allow the new record to be created. This worked.
You're welcome, I am glad I could help.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.