How to, automatically relate a new record to another record (1 Viewer)

Hello people

I have two tables "tblUser" and "tblCase" which are connected through a joint table "tblUSerCase" in order to make a many to many relationship. The tables has been build as follows:

tblUser:

ID (Primary key / Autonumber)
First name
Last name
User name
etc...

tblUserCase:
UserID (Primary key / number)
CaseID (Primary key / number)

tblCase:
ID (Primary key / Autonumber)
Casenumber
Casename
etc..

Now I have created a form where I can enter data for a new case which are to be stored in the the "tblCase" as I press the save button. I am doing this with the following code:

----------------------------

CurrentDb.Execute " INSERT INTO tblCase([Casenumber],[Casename],)" & _
"VALUES ('" & Me.txtCasenumber & "','" & Me.txtCasename & "')"

--------------------------

I want this new record to be automatically related to a specific user in table "tblUSer".

As a new record in "tblCase" is being a created, an new autonumber is automatically created. As "tblCase" is related to "tblUSerCase" this number should also show up in a new record in the joint table "tblUserCase".

However, it does not. Does anybody know what is wrong?
It works if I add the record manually to the "tblCase".

It won't magically appear on it's own unfortunately, you will have to do two things - get the ID of the record you have just added, and also determine the value for the UserID.

You can then do an insert into your junction table.

To reliably get the last record ID use the method described by Markk here https://access-programmers.co.uk/forums/showpost.php?p=1292884&postcount=8