Use a form to add data to a non-related (non Source Record) table

[Datament]

I have two forms one is called "Form1" with nine unbound Textboxes (txtbox1 - txtBox9) and a command buttom called "cmd_CustINFO". "Form1" is not related to "tbl_Master1" which is the record source for "Form2". When all the unbound textboxes are filled and the cmd_CustINFO is pushed in "Form1" it opens "Form2" with related record(s) matching the values of the unbound textboxes, but if no record matches the values of the unbound textbaoxes in "tbl_Master1" it triggers a Yes/No error message that asks if user would like to manually add the record.

The event I would like to create is if user presses "YES" I need the values of the (txtbox1 - txtBox9) unbound textboxes from "Form1" to be saved to "tbl_Master1" and "Form2" to open with that saved record.
Could somebody help me to achieve this with a code?
I really appreciate your help!

I have the following:
MsgBox "No Record found. Do you want to manually add the record?"

Dim db As Database Dim RS As Recordset Set db = CurrentDb Set RS = db.OpenRecordset("tbl_Master1")
With RS
.AddNew
[txtBox1] = Me.Store.value (This is the mathing record in "tbl_Master1"
[txtBox2] = Me.Account.value


[txtBox3] = Me.Statement.value
"
"
End With

The above has not worked well for me. it did not add the values from the txtboxes in "Form1", so the desired "Form2" opened but with NO record.
I am hoping to get a better recommendation on how to proceed. All assistance would be greatly appreciated.

[Micron]

If you edit or append with a recordset you have to commit the changes.
See https://learn.microsoft.com/en-us/of...-dao-recordset

You should always explicitly 'type' database objects as either DAO or ADO as appropriate. So
Dim db as DAO.Recordset etc.

[orange]

?? Why 2 forms? Have you considered a form explicitly for DataEntry along with some validation code in the Forms BeforeUpdate event to ensure all necessary values are filled and that no duplicates would be created via these values?
Just curious since we don't really know anything about your business, nor the process/rationale for your proposed method.

[Welshgasman]

You are not referring to Form1?

[CJ_London]

With RS
.AddNew
[txtBox1] = Me.Store.value

shouldn’t this be

With RS
.AddNew
!store=[txtBox1]

[moke123]

The process I think your describing is pretty straight forward.

Your message box code would look something like this:

    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

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

[Datament]

Thank you for your suggestion. Question: are fldA, fldB, fldC from form1 and Tbx1, Tbx2, Tbx3 from form2?

[moke123]

Here's an example