Code for search form error

[jfn15]

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!

[orange]

see this video re Many to Many resolution
https://www.youtube.com/watch?v=7XstSSyG8fw

If you have tables related 1 to Many, let's say Parent and Children.

If you are working with Parent100 who has children Mary and Tom, you can't add a new child for a different parent (Parent 101).
You have to identify that Parent(Parent101), and that record must exist before you can add a child/children for that Parent.

[jfn15]

So,then, is there a way I can save the current record in the form (if I am entering a new client), without actually closing the form? I've tried adding code to save the record to the command button that opens the second (search) form.

DoCmd.RunCommand acCmdSaveRecord

However, while this rids me of the error message, and does save the selected address to the new client, it also resets the client form back to the first record, rather than having he focus remain on the new client record.

[jfn15]

Just had a look at the video link you provided - it really only refers to the table relationships and I already have them linked. However I don't know how I can add a new client, and then (from the client table), select an address from the search table and pass the values to the junction table, without having to first close the clients table.

My table setup looks like this -

[jfn15]

Got it! The me.dirty property! (I think?!)