Combo box reference in search form error 1

[LowBrow]

Here: faq181-5497 FancyPrairie provides what I consider beautiful search code. I have already used parts of this in one db with positive results. Now, I am using parts again in second db, but I cannot get the combo box process to work. The text and date fields process correctly, it is only with the combo box that I get the error.

Here is my info:

Error Code and Text:
Run-time error '3075': Syntax error (missing operator) in query expression '(qryTrain.StaffID In(7,'.

Debug.Print strWhere (with StaffID 7 selected from the list and no other search criteria input):
(qryTrain.StaffID In (7,

Tag for control (cboSStaff):
Where=qryTrain.StaffID,Number,=;

Relevant Code from module:

BuildComboBox:

'*******************************************
'*  Determine Operator (=, >, Like, etc.)  *
'*******************************************
    
    strAndOr = vbNullString
    If (Len(strOperator) > 0) Then
        If (strOperator = "<>") Then strAndOr = " AND " Else strAndOr = " OR "
    End If

    If (Len(strOperator) = 0) Or (strOperator = "=") Then
        strWhere = strWhere & strAnd & " (" & strFieldName & " In ("
        strSuffix = ", "
    Else
        strWhere = strWhere & strAnd & " (" & strFieldName & " " & strOperator & " "
        strSuffix = ") " & strAndOr
    End If
                
    If (Len(strFieldValue) = 0) Then strFieldValue = ctl.Column(ctl.BoundColumn - 1)
    strWhere = strWhere & strFieldType & strFieldValue & strFieldType & strSuffix
Debug.Print strWhere
    strAnd = " AND "
    
    Return

I have come up with three ideas that may have caused this error, but when I implemented them as 'fixes' to the code, the syntax error disappeared but other errors were created.
I would rather not go into them unless asked, as I doubt they would be helpful.
If anyone can help it will be much appreciated! Thanks!

 

[VBslammer]

This line:

strAnd = " AND "

should be at the beginning of the procedure?

VBSlammer

Unemployed in Houston, Texas

 

[LowBrow]

Good guess, no dice. I noticed that in the BuildControl procs it was at the end, too. But I tried it anyway.

Thanks for trying, though.

 

[VBslammer]

I walked through the "GoSub" maze and the BuildComboBox section looks like it will only work with multiple values for some reason, using ([Value] In (Value1, Value2)) but it can't build a complete statement because the combobox only selects 1 value.

I was able to get it to work by replacing the combobox code with the textbox code with a minor change:

BuildComboBox:

'*******************************************
'*  Determine Operator (=, >, Like, etc.)  *
'*******************************************

    If (Len(strFieldValue) = 0) Then strFieldValue = ctl.Column(ctl.BoundColumn - 1)

    strWhere = strWhere & strAnd & " (" & strFieldName & " " & strOperator & " " & strFieldType & strFieldValue & strFieldType & ") "

    strAnd = " AND "
    
    Return

VBSlammer

Unemployed in Houston, Texas

 

[LowBrow]

Thanks A Ton, VBSlammer! I had hit upon a similar vein in one of my earlier attempts. I had noticed that the code for ListBox and ComboBox were very similar, and that needing an AND or OR clause seemed unnecessary, but since my attempt to fix failed, I moved on and would not have revisited that thought had it not been for your solution here. Thanks!