Adding a new record

[Core]

Hello,

Okay scratch that, I going to explain it differently below.

Kind Regards,

 

[Core]

Adding a record to a table (updated)

Okay here is the issue:

I have 3 tables linked as shown in attachment rel1.

Now I have a form to add a new test, the form is linked to a query which has both the test table and client table.

The learner can enter details on the test using the form. There is then a button to add a learner for that test. The button takes the user to a new form with text boxes for entering the users details. If a user is found on the clients table, it will alert the user and give him the option to add that learner... that works fine.

The problem is when the client is not found. I would like to add the client and then use that new entry. To achieve this I created vba code to open a form and enter the new record, store the id in a variable and then close the form and add the id to the add test form. This caused an error stating the ID could not be found, although the ID was valid and the ID was in the table.

Ok so I thought, made its some kind of issue because the table has been updated while the add test form was open. So I made the code close the form and then reopen it on the same record, but that does work either..

Now can any one take a look and my code and help me out.

I know the method I have used for adding a record is probably the wrong way to do it. However I cannot find the code needed to add a client to a table directly.

Here is my code (its a mess lol):

Private Sub bDone_Click()
lstFirst.RowSource = "qAddNewClientForTest"
lstFirst.Requery
If lstFirst.ListCount > 0 Then
    Command20.Visible = True
    count1 = lstFirst.ListCount
    lstFirst.Visible = True
    lblclients.Visible = True
    answer1 = MsgBox("Attention:" & vbNewLine & vbNewLine & "A Learner exists in the database matching these details, select OK to verify if this is the you are looking for." & vbNewLine & vbNewLine & "If you have already verified this and would like add a new client based on these details please select cancel.", vbOKCancel, "Warning")
    If answer1 = vbOK Then Exit Sub
End If

If txtFirst <> "" And txtSurname <> "" And txtDOB <> "" Then
Else
    answer = MsgBox("Please enter the clients first name, surname and date of birth before proceeding.", vbInformation)
    Exit Sub
End If
   
    Dim tempvar
    'code here
    'Forms!AddNewTest.Form.Dirty = False
    DoCmd.OpenForm "Frontend" 'open frontend
    Forms!frontend.Form.SetFocus 'set focus on front end
    
    DoCmd.GoToRecord , frontend, acNewRec 'goto new record
    Forms!frontend.FORENAME = Forms!addnewclientfortest.txtFirst 'auto-populate
    Forms!frontend.SURNAME = Forms!addnewclientfortest.txtSurname 'auto-populate
    Forms!frontend.DOB = Forms!addnewclientfortest.txtDOB 'auto-populate
    tempid = Forms!frontend.ID 'set id
    temprec = Forms!AddNewTest.Form.CurrentRecord
    
    Forms!frontend.Form.SetFocus
    DoCmd.Close
    Forms!addnewclientfortest.Form.SetFocus
    DoCmd.Close
    Forms!AddNewTest.Form.SetFocus 'set focus on new test
    
    DoCmd.Close
    DoCmd.OpenForm "AddNewTest"
    DoCmd.GoToRecord , AddNewTest, acGoTo, temprec
    Forms!AddNewTest.txtID = tempid