Struggling with multi value criteria (1 Viewer)

[topdesk123]

Hello all!

I have read too many threads I think and am now totally confused. I have an inventory table - linked to an inventorylocation table. By inventoryID. When it comes time to count, I want users to be able to choose multiple count locations. Which I use a list box filled with locations (text field). Once the locations are chosen there's a button with this code (which I found on one of the forumns, behind it

 = 
varItem

Dim db As DAO.Database
Dim strNewQueryName As String
Dim oQuery As New QueryDef

    Set db = CurrentDb
 Dim strSql As String
Dim varItem As String
Dim txtsql As String
Dim i As Variant
varItem = ""
For Each i In Me!
[List].ItemsSelected
    If varItem <> "" Then
        varItem = varItem & " , "
    End If
    varItem = varItem & Me!
[List].ItemData(i)
Next i


strSql = "SELECT *, Inventory.InventoryID, Inventory.ItemDescription, Inventory.[Reorder Level], Inventory.Units, Inventory.TargetStockUnit, InventoryLocation.Location, Locations.LocationID " & vbCrLf & _
"FROM Inventory INNER JOIN (Locations INNER JOIN InventoryLocation ON Locations.Location = InventoryLocation.Location) ON Inventory.InventoryID = InventoryLocation.InventoryID;"   'Debug.Print txtSQL
  strNewQueryName = "qryIDs"
  
    Set oQuery = db.QueryDefs(strNewQueryName)
    
    If oQuery.Name = "" Then
        ' query doesn't exist
        db.CreateQueryDef strNewQueryName, txtsql
    Else
        ' query exists; replace existing SQL
        oQuery.SQL = txtsql
    End If
    oQuery.Close

    DoCmd.OpenQuery strNewQueryName

Cleanup:
    Set oQuery = Nothing
    Set db = Nothing
    Exit Sub

ErrorHandler:
    If Err.Number = 3265 Then
        ' Item not found in this collection.
        ' query does not exist
        Debug.Print "Caught " & Err.Number & ": " & Err.Description
        Resume Next
    Else
        Debug.Print Err.Number & ": " & Err.Description
        Resume Cleanup
    End If[CODE]

I would like it to produce the SQL string behind the report "Count Inventory". I have tried this 1000 different ways and just don't get it. I thank you in advance for any help you can give me.

 

[pr2-eugin]

Hello topdesk123, Welcome to AWF

You have mentioned the code and where you have obtained it from, but I have to say you have failed to mention what exactly you are after, what part of the code fails. How about showing a few of the 1000 attempts you made?

Please note how to post VBA Code.

 

[topdesk123]

I'm sorry if I wasn't clear. And I did put

 around everything.
Also, I haven't kept all of the other things I tried. I want users to be able to choose multiple values from locations [me.list]
[CODE]
Set db = CurrentDb
 Dim strSql As String
Dim varItem As String
Dim txtsql As String
Dim i As Variant
varItem = ""
For Each i In Me!
[List].ItemsSelected
    If varItem <> "" Then
        varItem = varItem & " , "
    End If
    varItem = varItem & Me!
[List].ItemData(i)
Next i

and print a report of the inventory in those locations.

HTH

 

[topdesk123]

Oh one more thing, I get this error:

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
[CODE]

 

[pr2-eugin]

So how about adding a WHERE part to the SQL query?

    Dim db As DAO.Database
    Dim strNewQueryName As String
    Dim oQuery As New QueryDef

    Set db = CurrentDb
    Dim strSql As String
    Dim varItem As String
    Dim txtsql As String
    Dim i As Variant

    For Each i In Me!
[List].ItemsSelected
        If varItem = vbNullString Then
           [B] [COLOR=Red]varItem = varItem & "('"[/COLOR][/B]
        Else
            varItem = varItem & "', '"
        End If
        varItem = varItem & Me!
[List].ItemData(i)
    Next i
    [B][COLOR=Red]varItem = varItem & "')"[/COLOR][/B]

    strSql = "SELECT *, Inventory.InventoryID, Inventory.ItemDescription, Inventory.[Reorder Level], Inventory.Units, " & _
             "Inventory.TargetStockUnit, InventoryLocation.Location, Locations.LocationID " & vbCrLf & _
             "FROM Inventory INNER JOIN (Locations INNER JOIN InventoryLocation ON Locations.Location = InventoryLocation.Location) " & _
             "ON Inventory.InventoryID = InventoryLocation.InventoryID [COLOR=Red][B]WHERE InventoryLocation.Location In " & varItem & ";" [/B][/COLOR]
    
    [COLOR=Green]'Debug.Print txtSQL[/COLOR]
    strNewQueryName = "qryIDs"

    Set oQuery = db.QueryDefs(strNewQueryName)

    If oQuery.Name = "" Then
       [COLOR=Green] ' query doesn't exist[/COLOR]
        db.CreateQueryDef strNewQueryName, txtsql
    Else
        [COLOR=Green]' query exists; replace existing SQL[/COLOR]
        oQuery.SQL = txtsql
    End If
    oQuery.Close

    DoCmd.OpenQuery strNewQueryName

Cleanup:
    Set oQuery = Nothing
    Set db = Nothing
    Exit Sub

ErrorHandler:
    If Err.Number = 3265 Then
       [COLOR=Green] ' Item not found in this collection.
        ' query does not exist[/COLOR]
        Debug.Print "Caught " & Err.Number & ": " & Err.Description
        Resume Next
    Else
        Debug.Print Err.Number & ": " & Err.Description
        Resume Cleanup
    End If

 

[topdesk123]

Thank you for that, however I'm getting the error:
Invalid SQL statement, expected "DELETE", "INSERT", "PROCEDURE", "SELECT", OR "UPDATE".

 

[pr2-eugin]

Okay try..

    strSql = "SELECT Inventory.InventoryID...........

Instead of

    strSql = "SELECT *, Inventory.InventoryID...........

 

[topdesk123]

I changed txtsql to strsql and now the query comes up blank?

strSql = "SELECT *, Inventory.InventoryID, Inventory.ItemDescription, Inventory.[Reorder Level], Inventory.Units, " & _
             "Inventory.TargetStockUnit, InventoryLocation.Location, Locations.LocationID " & vbCrLf & _
             "FROM Inventory INNER JOIN (Locations INNER JOIN InventoryLocation ON Locations.Location = InventoryLocation.Location) " & _
             "ON Inventory.InventoryID = InventoryLocation.InventoryID WHERE InventoryLocation.Location In " & varItem & ";"
    
    'Debug.Print txtSQL
    strNewQueryName = "qryIDs"

    Set oQuery = db.QueryDefs(strNewQueryName)

    If oQuery.Name = "" Then
        ' query doesn't exist
        db.CreateQueryDef strNewQueryName, [COLOR=DarkRed]strSql[/COLOR]
    Else
        ' query exists; replace existing SQL
        oQuery.SQL = [COLOR=DarkRed]strSql[/COLOR]

 

[pr2-eugin]

Phew.. I never knew there were two variables.. Anyway.. You are narrowing the problem.. Uncomment the Debug.Print, then run the code again, copy the generated SQL from the Immediate window, paste it in a normal SQL view, then run it, if there are any errors you will be able to spot them there and then.. See if you get the desired result if not, play with it until you get a result..

 

[topdesk123]

SHAZAM!!! THANK YOU PAUL!!! I monkeyd with a couple of things and it is working awesomely!!! For future reference:

 Dim db As DAO.Database
    Dim strNewQueryName As String
    Dim oQuery As New QueryDef

    Set db = CurrentDb
    Dim strSql As String
    Dim varItem As String
    Dim txtsql As String
    Dim i As Variant

    For Each i In Me!
[List].ItemsSelected
        If varItem = vbNullString Then
            varItem = varItem & "("
        Else
            varItem = varItem & ", "
        End If
        varItem = varItem & Me!
[List].ItemData(i)
    Next i
    varItem = varItem & ")"

    strSql = "SELECT *, Inventory.InventoryID, Inventory.ItemDescription, Inventory.[Reorder Level], Inventory.Units, " & _
             "Inventory.TargetStockUnit, InventoryLocation.Location, Locations.LocationID " & vbCrLf & _
             "FROM Inventory INNER JOIN (Locations INNER JOIN InventoryLocation ON Locations.Location = InventoryLocation.Location) " & _
             "ON Inventory.InventoryID = InventoryLocation.InventoryID WHERE Locations.LocationID In " & varItem & ";"
    
    Debug.Print txtsql
    strNewQueryName = "qryIDs"

    Set oQuery = db.QueryDefs(strNewQueryName)

    If oQuery.Name = "" Then
        ' query doesn't exist
        db.CreateQueryDef strNewQueryName, strSql
    Else
        ' query exists; replace existing SQL
        oQuery.SQL = strSql
    End If
    oQuery.Close

    DoCmd.OpenQuery strNewQueryName

Cleanup:
    Set oQuery = Nothing
    Set db = Nothing
    Exit Sub

ErrorHandler:
    If Err.Number = 3265 Then
        ' Item not found in this collection.
        ' query does not exist
        Debug.Print "Caught " & Err.Number & ": " & Err.Description
        Resume Next
    Else
        Debug.Print Err.Number & ": " & Err.Description
        Resume Cleanup
    End If
End Sub