Data Entry Form Problems

[Stan Denman]

I am trying to set my my db to allow a temp worker to input client info in my absence. When I set the DB up, I made the mistake of over-analysing, and have primarily many-to-many relationships. They have been a nightmare. I posted yesterday trying to figure out why in data entry when I turned to the subform I got an error. I think I now know why, but I am still far away from a resolution, and I leave town next week.



Table organization:
Contacts
Clients (a subclass of contacts(
Addresses
CommPts (phones and email)
I have used autonumbers as primary key

Contact relates 1 to 1 one to clients
Contacts relate many to many to Addresses (a contact like a doctor can have more than one office, and two clients could have the same address)
CommPts are many to many with Contacts. Don't ask me why, I think that is probably wrong,but I am stuck with the design.

So I set up a main form based upon a contacts/clients query, with a subform for addresses, with ContID as master/child field. I now see that when I went to the subform to create an address I get an error because we don't have a record in linking table with a contID and AddrId field. The more think about it the more I like this solution:

No link between form and subform (guess that is just 2 forms side by side) and when I create a record in Address table create a record in the linking table. On the form I have the two field values that need to be linked on the open form: contID was created on the data entry for the main form, and on creating the record in the Address table I will have a field with the AddrID autonumber. So I create a command button when data entry is complete on form and subform that would create the new records needed in the linking table from the control values for contID on manin form and AddrID on subform.

I am not having good luck. This is what I have tried. On the subform I have an unbound control with source =[Forms].[frmnewclientdataentry].[contid]. FrmNewclientDataEntry is the main form,and ContID is the name of the control that hold the ContID autonumber. Then I added an upon update event for the main form ConID to refresh the subform control: Forms.frmnewclientdataentry.frmClientAddress.ContI D.Update

It doesn't work. ANY advice would be appreciated.

[June7]

A form can do data entry/edit for only one table. Including multiple tables in a form RecordSource can definitely cause issues, especially if they use INNER JOIN.

What are your junction tables?

A subform with conditional RecordSource based on main form is problematic because subform loads first - I know, weird, but true. That's why subform container Master/Child Links is supposed manage synchronization.

If you want to provide db for analysis, follow instructions at bottom of my post.

[orange]

What are the relationships between your tables?
Can you look at your relationships windows under Database Tools?
Take a screen capture and post in the thread.

You should resolve Many to Many relationships using a Junction table.

As you are finding out through experience, getting tables and relationships designed to support your business/opportunity is a critical first step in building a database.

A great tutorial (about 45-60 minutes) to work through is this one from RogersAccessLibrary.
Good luck.