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.