Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2011
    Posts
    63

    Unanswered: INSERT wtith Identity Scope for a For Loop

    I have a similar thread about Linked Tables but felt that this should be a new thread because it's a whole different topic.

    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 also put a SQL Server transaction (if it could be used at all).

    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 !!

    Code:
     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
    Code:
    BEGIN TRY
    BEGIN TRANSACTION
    DECLARE @ID int
    INSERT INTO tbl_Audit (Group_Name, ScheduledQuarter, ScheduledYear, AuditStatus) Values ('Call Center', '4','2157', 'Pending')
    set @ID = SCOPE_IDENTITY()
    insert into jntbl_AuditToAuditor(Audit_ID,Auditor_ID) values(@ID, '')
    insert into jntbl_AuditToAuditor(Audit_ID,Auditor_ID) values(@ID, '')
    commit
    END TRY
    BEGIN CATCH
    IF @@TRANCOUNT > 0
    ROLLBACK
    Last edited by tkepongo; 07-18-11 at 19:38.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I would use a stored procedure that would create the row (INSERT INTO...) and return the Identity value of the new row.

    Otherwise you can use a query that would return the TOP (or MAX) value from the table but in a multi-user environment there is no garanty that it will be the Identity value of the row you created.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •