Help on recordset only looping through 1 record

[jgier]

Hey guys,

I have create a recordset based upon a table. The table holds group numbers of where the SQL statement is basing its criteria on. However, when I put multiple groups in, it will cycle through to match the number of records but it will not move to the next group number. Any suggestions? What's weird is I have used the exact same process in multiple databases and they work. Just in this database it doesn't. The code is below.

Set rs1 = New ADODB.Recordset
rs1.Open _
"Select GROUP_NBR, AOR_SYS_ASSGN_NBR, WA_SYS_ASSGN_NBR " _
& "FROM tblInput;", _
ActiveConnection:=CurrentProject.Connection

strSQL1 = "INSERT INTO tblHUM_TTMDTAC (RPT_ID, AOR_SYS_ASSGN_NBR, WA_SYS_ASSGN_NBR, PLATFORM_CD, BLK_BUS_CD, GROUP_NBR " _
& "SELECT DISTINCT HUM_TTMDTAC.RPT_ID, HUM_TTMDTAC.AOR_SYS_ASSGN_NBR, HUM_TTMDTAC.WA_SYS_ASSGN_NBR, HUM_TTMDTAC.PLATFORM_CD, HUM_TTMDTAC.BLK_BUS_CD, HUM_TTMDTAC.GROUP_NBR " _
& "FROM HUM_TTMDTAC " _
& "WHERE (((HUM_TTMDTAC.RPT_ID) In (9,16,23,28,85,89,90)) AND (HUM_TTMDTAC.GROUP_NBR = '" & rs1("GROUP_NBR") & "' ));"

Debug.Print strSQL1

DoCmd.RunSQL strSQL1Delete

If rs1.EOF = False Then

If Not IsNull(rs1("GROUP_NBR")) Then
Do

rs1.MoveNext
DoCmd.RunSQL strSQL1

Loop Until rs1.EOF = True

End If

End If

 

[jgier]

If anyone has issues with this ever, please review the following code.

Set rs1 = New ADODB.Recordset
rs1.Open _
"Select GROUP_NBR, AOR_SYS_ASSGN_NBR, WA_SYS_ASSGN_NBR " _
& "FROM tblInput;", _
ActiveConnection:=CurrentProject.Connection

If rs1.EOF = False Then

If Not IsNull(rs1("GROUP_NBR")) Then

rs1.MoveFirst

Do

DoCmd.RunSQL "INSERT INTO tblHUM_TTMDTAC (RPT_ID, AOR_SYS_ASSGN_NBR, WA_SYS_ASSGN_NBR, PLATFORM_CD, BLK_BUS_CD, GROUP_NBR, TRAN_DUE_DT, TXN_TY_CD, US_POSTL_STATE_CD, PR_LN_CD, PR_ID_NBR, YR1_RNWL_CD, LOB_CD, GL_CO_CD, ERR_DESC) " _
& "SELECT DISTINCT HUM_TTMDTAC.RPT_ID, HUM_TTMDTAC.AOR_SYS_ASSGN_NBR, HUM_TTMDTAC.WA_SYS_ASSGN_NBR, HUM_TTMDTAC.PLATFORM_CD, HUM_TTMDTAC.BLK_BUS_CD, HUM_TTMDTAC.GROUP_NBR, HUM_TTMDTAC.TRAN_DUE_DT, " _
& "HUM_TTMDTAC.TXN_TY_CD, HUM_TTMDTAC.US_POSTL_STATE_CD, HUM_TTMDTAC.PR_LN_CD, HUM_TTMDTAC.PR_ID_NBR, HUM_TTMDTAC.YR1_RNWL_CD, HUM_TTMDTAC.LOB_CD, HUM_TTMDTAC.GL_CO_CD, HUM_TTMDTAC.ERR_DESC " _
& "FROM HUM_TTMDTAC " _
& "WHERE (((HUM_TTMDTAC.RPT_ID) In (9,16,23,28,85,89,90)) AND (HUM_TTMDTAC.GROUP_NBR = '" & rs1("GROUP_NBR") & "' ));"

rs1.MoveNext

Loop Until rs1.EOF = True

End If
End If

End Sub