Two diferent VBA Code under the same Event Procedure

[Takiman]

Hi I have a used Event Procedure "After Insert" in my MainForm to create records in SubForm1 (I have tested it and it works well). I would now like to use the 'After Insert" event procedure again but instead create records in my Subform2. I have created the code for Subform2, however, i don't know how the code needs to be written/or combined to sit under the same event procedure -- any help would be great?

Here is my code for both subforms:

Private Sub Form_AfterInsert()
Dim strSQL As String

' insert rows into Tbl_tblAuditMsg table
strSQL = "INSERT INTO tblAuditMsg(AuditID,MsgID) " & _
"SELECT " & Me.AuditID & ", MsgID " & _
"FROM tblMsg"

CurrentDb.Execute strSQL, dbFailOnError

' requery subform to show new rows
Me.subform1.Requery

End Sub

Private Sub Form_AfterInsert()


Dim strSQL As String
' insert rows into Tbl_tblAuditFactor table
strSQL = "INSERT INTO tblAuditFactor(AuditID,FactorID) " & _
"SELECT " & Me.AuditID & ", FactorID " & _
"FROM tblFactor"

CurrentDb.Execute strSQL, dbFailOnError

' requery subform to show new rows
Me.subform2.Requery

End Sub

[John_G]

Just combine the two blocks of code into one, so that the same code updates the two tables and subforms (the merged bit is shown in red):

Private Sub Form_AfterInsert()
Dim strSQL As String

' insert rows into Tbl_tblAuditMsg table
strSQL = "INSERT INTO tblAuditMsg(AuditID,MsgID) " & _
"SELECT " & Me.AuditID & ", MsgID " & _
"FROM tblMsg"

CurrentDb.Execute strSQL, dbFailOnError

' requery subform to show new rows
Me.subform1.Requery

' insert rows into Tbl_tblAuditFactor table
strSQL = "INSERT INTO tblAuditFactor(AuditID,FactorID) " & _
    "SELECT " & Me.AuditID & ", FactorID " & _
    "FROM tblAuditFactor"
 
CurrentDb.Execute strSQL, dbFailOnError
 
' requery subform to show new rows
Me.subform2.Requery

End Sub

[Takiman]

Hi thanks for that John I have tested the code... it isn't coming up with errors, however, it is not actually doing anything? my subform1 is still working as it should (i.e. records are being inserted into tblAuditMsg), -- however, for subform2 - there are no records being inserted into tblAuditFactor? any ideas thanks.

[Takiman]

Ok think maybe because of the mechanics of my subform not the code I will check and let you know.

[Takiman]

Ok I have checked the mechanics of both my subforms and they are fine. I tested this by only using one of the VBA codes in the After Insert event procedure at a time. They both produced what i wanted.. However, when they are combined in the script provided it does not work : ( can anyone let me know how I can get this script to work? thanks

Private Sub Form_AfterInsert()
Dim strSQL As String

' insert rows into Tbl_tblAuditMsg table
strSQL = "INSERT INTO tblAuditMsg(AuditID,MsgID) " & _
"SELECT " & Me.AuditID & ", MsgID " & _
"FROM tblMsg"

CurrentDb.Execute strSQL, dbFailOnError

' requery subform to show new rows
Me.subform1.Requery

' insert rows into Tbl_tblAuditFactor table
strSQL = "INSERT INTO tblAuditFactor(AuditID,FactorID) " & _
"SELECT " & Me.AuditID & ", FactorID " & _
"FROM tblFactor"

CurrentDb.Execute strSQL, dbFailOnError

' requery subform to show new rows
Me.subform2.Requery

End Sub

[John_G]

The second part (red) won't work if there are no records in tblFactor to begin with. Other than that I can't see anything wrong.

Actually, I wonder about this statement: Me.subform1.Requery

In A2010 it probably would generate an error. If subform1 is the name of the control in the main form (not the name of the form), it should be
Me.subform1.form.Requery

A2016 may be more forgiving - I haven't used it yet.

[Takiman]

Ok All good I got it to work! code is as per below.. I think this aligns with what you suggested in the first place (with the new changes of subform names) John_G so thanks.. not sure why it didnt work earlier i.e:

Private Sub Form_AfterInsert()
Dim strSQL As String


' insert rows into Tbl_tblAuditMsg table
strSQL = "INSERT INTO tblAuditMsg(AuditID,MsgID) " & _
"SELECT " & Me.AuditID & ", MsgID " & _
"FROM tblMsg"


CurrentDb.Execute strSQL, dbFailOnError


' requery subform to show new rows
Me.subAuditMsg.Requery


' insert rows into Tbl_tblAuditFactor table
strSQL = "INSERT INTO tblAuditFactor(AuditID,FactorID) " & _
"SELECT " & Me.AuditID & ", DRGFactorID " & _
"FROM tblFactor"

CurrentDb.Execute strSQL, dbFailOnError

' requery subform to show new rows
Me.subAuditFactor.Requery


End Sub