"INSERT INTO" AND Update to Two Different Tables

[irunergoiam]

I have unbound fields on a form that I wish to "On Click" save to two different tables (some of the unbound fields being INSERTED INTO one table "tblOSHW_EAN_NewHireInfo" and two of the fields being UPDATED to another table "tblOSHWLabRequest"). I've successfully INSERTED INTO the "tblOSHW_EAN_NewHireInfo" table, but I'm not sure how to UPDATE the "tblOSHWLabRequest" table by using a single "Save" button on the form via the On Click event.

Here's the code for the INSERT INTO action:

DoCmd.SetWarnings False

Dim StrSQL As String
Dim strCriteria As String

StrSQL = ""
StrSQL = StrSQL & " INSERT INTO tblOSHW_EAN_NewHireInfo"
StrSQL = StrSQL & " (ApplicantID, Req, SSNFirstName, SSNMiddleName, SSNLastName, SSN)"
StrSQL = StrSQL & " Values"
StrSQL = StrSQL & " ('" & unbApplicantID & "'," & unbReq & ",'" & unbSSNFirstName & "','" & unbSSNMiddleName & "','" & unbSSNLastName & "','" & unbSSN & "')"
DoCmd.RunSQL StrSQL

Thanks much to anyone kind enough to share with me their expertise on this.

ad

 

[pbaldy]

Not sure what the issue is. You can simply build another SQL string and execute it after the first one:

strSQL = "INSERT..."
CurrentDb.Execute strSQL, dbFailOnError

strSQL = "UPDATE..."
CurrentDb.Execute strSQL, dbFailOnError

Note I used the more efficient Execute method, which also does not throw the warnings.