Question Help with table deffs query

[josephbupe]

Hi,

I have a listbox with values that query a table deff querry. The listbox is working fine. I want also to add a few text boxes to run queries in a similar manner but I am getting a syntax error. The code is as follows:

Private Sub Command0_Click()
Dim db As DAO.Database
    Dim QD As QueryDef
    Dim varWhere As Variant
    Dim Answer As String
    Dim varItem As Variant
    Set db = CurrentDb()

'   Delete the existing dynamic query; trap the error if the query does
'   not exist.

    On Error Resume Next
    db.QueryDefs.Delete ("Q_ImageNormalSort")
    On Error GoTo 0
[COLOR=SeaGreen]
'   Setup "ObjectType" code block:[/COLOR]

    Answer = ""
    For Each varItem In Me.lstObjectType.ItemsSelected
        Answer = Answer & Me.lstObjectType.ItemData(varItem) & ","
    Next varItem
    
    If Len(Answer & vbNullString) <> 0 Then
        Answer = Left(Answer, Len(Answer) - 1)
    End If
    
    If Len(Answer & vbNullString) <> 0 Then
        varWhere = varWhere & " AND [ObjectID] In(" & Answer & ")"
    End If

[COLOR=SeaGreen]'   Setup "ImageName" code block: [COLOR=Red]?????[/COLOR][/COLOR]
    
    If Me.TxtImageName <> "" Then
        varWhere = varWhere & "[ImageName] Like '*" & Me.TxtImageName
    End If
    
[COLOR=SeaGreen]'   Create table deff querry[/COLOR]

    Set QD = db.CreateQueryDef("Q_ImageNormalSort", _
    "Select * from T_Image " & (" where " + Mid(varWhere, 6) & ";"))

    DoCmd.OpenQuery "Q_ImageNormalSort"

End Sub

The table deff query looks like this in design view when run with any one of the selected items in the listbox:

Field: [ObjectID]
Table: T_Image
Criteria: In (1,2)

The (1,2) being primary key values of the two selected items from the listbox.

I not familiar with the table deff queries yet. I will appreciate your help.

Joseph

 

[Cronk]

On the line before Set QD="...

Insert
Debug.print "Select * from T_Image " & (" where " + Mid(varWhere, 6) & ";")

Run your code, then paste the SQL in the immediate window below your code into a new query. That will show where the problem is.