Access Visual Basic Coding

Code:

'Filter frmfrmStaff based on search criteria
Form_frmStaff.RecordSource = " select * from frmStaff where " & GCriteria
Form_frmStaff.Caption = "tblStaff (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"

I keep getting the error message "Run-time error '3075':

l1 in query expression 'l2'.

Anybody know how to fix this? The line is bold is the problem line according to the debugger.

Thank You

Reply 1

laser

10

What is GCriteria? Also, possibly unrelated, but you have a space before your select.

Reply 2

chaotik_mind

OP

10

laser

What is GCriteria? Also, possibly unrelated, but you have a space before your select.

I'm using that code as a search function. The full code is:

Private Sub cmdSearch_Click()

If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
MsgBox "You must select a field to search."

ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
MsgBox "You must enter a search keyword."

Else

'Generate search criteria
GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"

'Filter frmfrmStaff based on search criteria
Form_frmStaff.RecordSource = " select * from frmStaff where " & GCriteria "
Form_frmStaff.Caption = "tblStaff (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"

'Close frmStaffSearch
DoCmd.Close acForm, "frmStaffSearch"

MsgBox "Search Complete."

End If

End Sub

Reply 3

-WhySoSerious?

17

As you're using a variable in your SQL statement, try putting another ampersand on the other side of the variable. So you have:

Form_frmStaff.RecordSource = " select * from frmStaff where " & GCriteria & ";"

Hmm, to be honest, your code doesn't make sense at the moment.

Select All from frmStaff where cboSearchField.Value & " LIKE '*" & txtSearchString & "*'" ?

Where that = what? You need some kind of entity within the table that the search string has to be 'like'

Reply 4

chaotik_mind

OP

10

-WhySoSerious?

As you're using a variable in your SQL statement, try putting another ampersand on the other side of the variable. So you have:

Form_frmStaff.RecordSource = " select * from frmStaff where " & GCriteria & ";"

Hmm, to be honest, your code doesn't make sense at the moment.

Select All from frmStaff where cboSearchField.Value & " LIKE '*" & txtSearchString & "*'" ?

Where that = what? You need some kind of entity within the table that the search string has to be 'like'

Tried adding the extra ampersand, but got the same error.

I have a search form with a combo box and text box named cboSearchField and txtSearchString respectively. In the combo box is a value list of fields in the staff table I want the user to be able to search. The user enters the search data into the text box. I have entered that code as an event procedure on a command button on the search form.

Hope that clears things up.

Access Visual Basic Coding

Related discussions

Latest

Trending

Trending

Articles for you