Syntax error(missing operator) in query expression '='"

[dwiniers]

Hello there i need your expertise in VB6 i make some program using the navigation key and add, edit, delete, search command button. The program is almost 99% working. By the way im using adodc and access in my db and combo box using with search fields. My problem now when i run the program if i mistakenly click the search command without selecting the fields in the combo1 box when i click the ok or cancel in the inputbox it says: syntax error (missing operator in query expression '='" then it go to the if statement No record found. And the all data in data grid will disappear. And when you click any of the navigation key it will debug: object variable or with block variable not set. But if select of the fields before you search if working fine.

What i want is if you mistakenly click the search button without selecting fields if cancel nothing will happen to program.

here's my code:

Private Sub cmdadd_Click()
    Adodc1.Recordset.AddNew
End Sub

Private Sub cmddel_Click()
    Adodc1.Recordset.Delete
    If Adodc1.Recordset.EOF Then
    Adodc1.Recordset.MoveFirst
End Sub

Private Sub cmdedit_Click()
    Adodc1.Recordset.Update
End Sub

Private Sub cmdsearch_Click()
    On Error Resume Next
    Dim s As String
    s = InputBox("Enter " & Combo1 & " to search", "Searching")
    Adodc1.Recordset.Close
    Dim f As String
    f = "Select * From FamilyInfo where " & Combo1.List(Combo1.ListIndex) & " = '" & s & "'"
    Adodc1.RecordSource = f
    Adodc1.Refresh
    If Adodc1.Recordset.EOF Then
    MsgBox "No record found!", vbInformation
    End If
End Sub

Private Sub Command1_Click()
    End
End Sub

Private Sub first_Click()
    Adodc1.Recordset.MoveFirst
    Text6.Text = DateDiff("yyyy", Text5.Text, Now)
End Sub

Private Sub Form_Load()
Adodc1.CommandType = adCmdText
Text6.Text = DateDiff("yyyy", Text5.Text, Now)
End Sub

Private Sub last_Click()
    Adodc1.Recordset.MoveLast
    Text6.Text = DateDiff("yyyy", Text5.Text, Now)
End Sub

Private Sub nextt_Click()
    Adodc1.Recordset.MoveNext
    If Adodc1.Recordset.EOF Then
    Adodc1.Recordset.MoveLast
    MsgBox "End of file!", vbInformation
    End If
    Text6.Text = DateDiff("yyyy", Text5.Text, Now)
End Sub
Private Sub previous_Click()
    Adodc1.Recordset.MovePrevious
    If Adodc1.Recordset.BOF Then
    Adodc1.Recordset.MoveFirst
    MsgBox "Beginning of file!", vbInformation
    End If
    Text6.Text = DateDiff("yyyy", Text5.Text, Now)
End Sub

[BitBlt]

Between lines 18 and 19, you might want to test the value of your string variable "s" and the value of your ComboBox to determine if there are valid values in both. If there aren't valid values in both, then raise an appropriate message box and exit the subroutine.

I haven't tested this, by the way...just offering a suggestion. Good luck!

[dwiniers]

@bitbit

So how code it goes in line 18 and 19.

[scudzilla]

Hmmm provided the value in the combobox corresponds to a valid field in the database, no matter the value of s, it should not generate an error (which means you don't need to check the value of s). If however, the combobox doesn't, then it becomes an error. I assume your combobox style is a dropdown combo, such that you could enter any value in it, or that at runtime its original value is -1 and you search before changing it.

Things you could do: a) make it a dropdown list style, and at Form_Load, add combo1.listindex = 0; or b) like BitBlt said, write a code that checks whether the value selected in the combobox corresponds to a field in the database. You can do this by using an if-and-then statement.

[dwiniers]

@ scudzilla

Thanks for the code. Problem solved.

with this code the problem solved.

If Combo1.ListIndex = -1 Then
        MsgBox "No field selected."
        Exit Sub
    End If