Unanswered: Looping through table to set variable equal to ouput of sql string
Below is the code I am using to 1)create a query with the information I want from a table that is linked to an excel workbook and 2) extract distinct item numbers from the query and insert them into another table. The issue I am having is that when I am defining itemnumber, it is not looping through the table but instead remaining on the first recordset. My goal is to extract only distinct item numbers, so that if an item number exists multiple times in the query, it will only be copied once into the table. Could anyone help me determine why it is not looping through the recordset? If I take out the DCount function and if statement associated with it, it does loop through, but gives me multiples of the same item number which I don't want. Any help would be greatly appreciated! Thanks!
Private Sub cmd_createqry_Click()
On Error GoTo Err_cmd_createqry_Click
Dim dbs As Database
Dim rst As Recordset
Dim rst1 As Recordset
Dim qdf As QueryDef
Dim strSQL As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim itemnumber As String
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("TblWorkload03", dbOpenDynaset)
'Delete previous query
On Error Resume Next
On Error GoTo 0
strSQL = "Select DISTINCT Project_Number, [Item number], [Item description], " & _
"[Exception description], [Planner code] FROM TblWorkload03;"
Set qdf = dbs.CreateQueryDef("RotorCellExceptions", strSQL)
Set rst = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)
'Insert new item numbers into BoM Table
With dbs.OpenRecordset("SELECT DISTINCT [Item Number] FROM RotorCellExceptions;")
Do Until .EOF
strSQL1 = "SELECT RotorCellExceptions.[Item Number] FROM RotorCellExceptions;"
Set rst1 = dbs.OpenRecordset(strSQL1, dbOpenDynaset)
itemnumber = rst1![Item Number]
If DCount("[Item Number]", "BoMTbl", "[Item Number] = '" & itemnumber & "'") <> 0 Then
strSQL2 = "INSERT INTO BoMTbl " & _
"SELECT RotorCellExceptions.[Item Number] FROM RotorCellExceptions;"
dbs.Execute strSQL2, dbFailOnError
MsgBox "Process Complete", vbInformation
I'm not sure I understand the problem... what would the Where clauses be made up of? I am trying to get it to import any item number that is not already in the BoMTbl... but I thought I was accomplishing this by using the Dcount function