Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2011

    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
    dbs.QueryDefs.Delete "RotorCellExceptions"
    On Error GoTo 0
    'Create query
    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
       End If
    End With
    MsgBox "Process Complete", vbInformation
        Exit Sub
        MsgBox Err.Description
        Resume Exit_cmd_createqry_Click
    End Sub

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    There are no WHERE clauses in strSQL1 and strSQL2. The .MoveNext instructions refer to the first (no variable assigned) recordset and you loop into it for nothing.
    Have a nice day!

  3. #3
    Join Date
    Nov 2011

    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

Posting Permissions

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