I have a form (frmContacts) from which I want to open a second form (frmSearchAddress) and enable the user to either select an existing address and assign it to the contact, or add a new address and assign that to the contact. The 2 tables are linked using a junction table, as a person can have more than address assisgned (as we ask for both postal and residential), and similarly, an address can be linked to more than one of our client records. So, we have:
tblClients - ClientID, etc
tblAddress - AddressID etc
tblClientAddress - ClientID, AddressID, AddressType
The code I've used on a command button to open the address form is as follows:
DoCmd.OpenForm "frmAddressSearch", , , , , acDialog, Me!ClientID
On the search form, a user can type in some data and the results will be listed in a subform. They can select the correct one and click on a 'select address' button - the code behind this button is:
CurrentDb.Execute "INSERT INTO tblClientAddress ( ClientID, AddressID ) " _
& "VALUES ( " & Me.OpenArgs & ", " & Me!frmAddressSearch.Form!AddressID & " );", dbFailOnError
I hasten to add that much of this code has been obtained from different websites - I have no programming experience.
My problem is that this code seems to work quite well for existing records in tblContacts, but when I add a new person to the clients table, I have to close and then reopen it to avoid the dreaded error message, telling me that I cannot add or edit a record in the address search form, without first having a corresponding client id in the first form. How do I pass a new client ID to the second form, without closing the first form?
Can anyone point me in the right direction? I'm finding this many-to-many stuff very frustrating!