The process I think your describing is pretty straight forward.
Your message box code would look something like this:
Code:
Select Case MsgBox("No Record found. Do you want to manually add the record?", vbYesNo, "Confirm Addition")
Case vbYes
SaveandOpen
Case vbNo
'do nothing or something else
End Select
The below code saves the record and opens the form
Notice how the primary key of the new record is obtained then used in the criteria to open Form2
Code:
Private Sub SaveandOpen()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim SQL_Select As String
Dim NewID As Long 'a variable to hold the PK of new record.
SQL_Select = "Select * from tblMaster where MasterID = 0" 'open empty recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset(SQL_Select)
With rs
.AddNew
!fldA = Me.Tbx1
!fldB = Me.Tbx2
!fldC = Me.Tbx3
!fldD = Me.Tbx4
!fldE = Me.Tbx5
NewID = rs.Fields("MasterID") 'get the primary key value of the new record
.Update
End With
DoCmd.OpenForm "Form2", , , "MasterID = " & NewID ' open form2 using the new PK.
MyExit:
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub