INSERT wtith @@Identity for a For Loop

[tkepongo]

I am trying to insert into three SQL Server tables. The first insert will go into the Audit table so it can generate an Audit_ID. Then using that Audit_ID, I will run a For loop to insert (Audit_ID, Auditor) into the Auditor junction table and (Audit_ID, Auditee) into the Auditee junction table. I believe the For loop is necessary because the Auditors and Auditees values come from a listbox.

I have some VBA code below but I am now stuck. I get a syntax error

So my question is: how can I retrieve the @@Identity (Audit_ID) and use it in a For loop?

Thank you all for your help! If I figure this out, this will become a turning point for my work project and make my boss happy!!

On Error GoTo Err_test
    Dim strSQL As String
    Dim Group As String
    Dim Year As Integer
    Dim Quarter As Integer
    Dim i As Variant
    Dim j As Variant
    Dim lsql As String
    Dim Auditor as String
    Dim Auditee as String
    Group = [Forms]![frmAddAudittoPlan].[cboGroup].[Value]
    Year = Me.txtYear
    Quarter = Me.cboQuarter.Value
    strSQL = "INSERT INTO dbo_tbl_Audit (Group_Name, ScheduledQuarter, ScheduledYear) Values ('" & Group & "', " & Quarter & ", " & Year & ");"
    CurrentDb.Execute strSQL, dbFailOnError
 
    For Each i In Me!lstAuditors.ItemsSelected
        Auditor = Me!lstAuditors.ItemData(i)
        lsql = "INSERT INTO dbo_jntbl_AuditToAuditor (Audit_ID, Auditor_ID) Values ("SELECT @@Identity", '" & Auditor & "')
        CurrentDb.Execute lsql , dbFailOnError
        Next i
 
    For Each j In Me!lstAuditee.ItemsSelected
        Auditor = Me!lstAuditee.ItemData(j)
        lsql = "INSERT INTO dbo_jntbl_AuditToAuditor (Audit_ID, Auditor_ID) Values ("SELECT @@Identity", '" & Auditee & "')
        CurrentDb.Execute lsql , dbFailOnError
        Next j
 
    MsgBox ("Audit Plan Added")
    End If
Exit Sub
Err_test:
        MsgBox "Error: " & Err.Description
End Sub

 

[boblarson]

I am assuming you have a numeric ID

On Error GoTo Err_test
    Dim strSQL As String
    Dim Group As String
    Dim Year As Integer
    Dim Quarter As Integer
    Dim i As Variant
    Dim j As Variant
    Dim lsql As String
    Dim Auditor as String
    Dim Auditee as String
[B][COLOR=red]Dim lngAuditID As Long[/COLOR][/B]
[B][COLOR=red]Dim rst As DAO.Recordset[/COLOR][/B]
 
    Group = [Forms]![frmAddAudittoPlan].[cboGroup].[Value]
    Year = Me.txtYear
    Quarter = Me.cboQuarter.Value
 
    strSQL = "INSERT INTO dbo_tbl_Audit (Group_Name, ScheduledQuarter, ScheduledYear) Values ('" & Group & "', " & Quarter & ", " & Year & ");"
    CurrentDb.Execute strSQL, dbFailOnError
[COLOR=red][B]Set rst = Currentdb.OpenRecordSet("Select @@Identity As LastID")[/B][/COLOR]
[B][COLOR=red]lngAuditID = rst!LastID[/COLOR][/B]
 
    For Each i In Me!lstAuditors.ItemsSelected
        Auditor = Me!lstAuditors.ItemData(i)
        lsql = "INSERT INTO dbo_jntbl_AuditToAuditor (Audit_ID, Auditor_ID) Values ([B][COLOR=red]" & lngAuditID & "[/COLOR][/B], '" & Auditor & "')
        CurrentDb.Execute lsql , dbFailOnError
        Next i
 
    For Each j In Me!lstAuditee.ItemsSelected
        Auditor = Me!lstAuditee.ItemData(j)
        lsql = "INSERT INTO dbo_jntbl_AuditToAuditor (Audit_ID, Auditor_ID) Values ([B][COLOR=red]" & lngAuditID & "[/COLOR][/B], '" & Auditee & "')
        CurrentDb.Execute lsql , dbFailOnError
        Next j
 
    MsgBox ("Audit Plan Added")
    End If
Exit Sub
Err_test:
        MsgBox "Error: " & Err.Description
End Sub

 

[tkepongo]

I am assuming you have a numeric ID

On Error GoTo Err_test
    Dim strSQL As String
    Dim Group As String
    Dim Year As Integer
    Dim Quarter As Integer
    Dim i As Variant
    Dim j As Variant
    Dim lsql As String
    Dim Auditor as String
    Dim Auditee as String
[B][COLOR=red]Dim lngAuditID As Long[/COLOR][/B]
[B][COLOR=red]Dim rst As DAO.Recordset[/COLOR][/B]
 
    Group = [Forms]![frmAddAudittoPlan].[cboGroup].[Value]
    Year = Me.txtYear
    Quarter = Me.cboQuarter.Value
 
    strSQL = "INSERT INTO dbo_tbl_Audit (Group_Name, ScheduledQuarter, ScheduledYear) Values ('" & Group & "', " & Quarter & ", " & Year & ");"
    CurrentDb.Execute strSQL, dbFailOnError
[COLOR=red][B]Set rst = Currentdb.OpenRecordSet("Select @@Identity As LastID")[/B][/COLOR]
[B][COLOR=red]lngAuditID = rst!LastID[/COLOR][/B]
 
    For Each i In Me!lstAuditors.ItemsSelected
        Auditor = Me!lstAuditors.ItemData(i)
        lsql = "INSERT INTO dbo_jntbl_AuditToAuditor (Audit_ID, Auditor_ID) Values ([B][COLOR=red]" & lngAuditID & "[/COLOR][/B], '" & Auditor & "')
        CurrentDb.Execute lsql , dbFailOnError
        Next i
 
    For Each j In Me!lstAuditee.ItemsSelected
        Auditor = Me!lstAuditee.ItemData(j)
        lsql = "INSERT INTO dbo_jntbl_AuditToAuditor (Audit_ID, Auditor_ID) Values ([B][COLOR=red]" & lngAuditID & "[/COLOR][/B], '" & Auditee & "')
        CurrentDb.Execute lsql , dbFailOnError
        Next j
 
    MsgBox ("Audit Plan Added")
    End If
Exit Sub
Err_test:
        MsgBox "Error: " & Err.Description
End Sub

Thank you kind sir! I am out of the office but I will check up on this first thing in the morning and let you know what happens!

 

[tkepongo]

Everything works out great. Thank you!