I am admittedly VERY inexperienced with any sort of coding and with the command buttons themselves and am trying to do something that (from my perspective) is somewhat complicated, but as I am not the only person dealing with my database and most of the people entering data have never even opened Access before, I am trying to make it as simple as possible for them based on what I know about them and what I know they need. As such, I was attempting to create a very simple search command where they click the "search..." button and it pulls up another form with two fields. The first field is a combo box containing all the field names they can search from the data form; the second field is where they type in a full or partial expression they would like searched for (i.e. a for all last names begining with a, or adams for all records with the last name adams, or ad because (s)he knows the last name is ad.....something). They then click the search button on this form and it brings up the data form, but limits the records to those which fit the search criteria. I got the coding and everything while searching the web for an answer so I didn't spam with a rediculous quesiton and attempted to change the necessary information, but as little as I know about coding, I'm pretty sure I messed something up. My database is Volunteer Database,
The coding I have 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 string."
Else
'Generate search criteria
GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
'Filter VolunteerInformation based on search criteria
Form_VolunteerDatabase.RecordSource = "select * from Customers where " & GCriteria
Form_VolunteerInformation.Caption = "Customers (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"
'Close Search Form
DoCmd.Close acForm, "Search Form"
MsgBox "Results have been filtered."
End If
End Sub
And the error message I get when searching the [Last Name] field for "adams" is as follows:
Run-time error '3075':
Syntax error (missing operator) in query expression 'Last Name LIKE '*adams*".
When I ask it to debug, it highlights the following line of code:
Form_VolunteerDatabase.RecordSource = "select * from Customers where " & GCriteria
(1) What did I do wrong and/or not replace?
(2) What does Customers refer to in the previous line of coding?
The coding I have 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 string."
Else
'Generate search criteria
GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
'Filter VolunteerInformation based on search criteria
Form_VolunteerDatabase.RecordSource = "select * from Customers where " & GCriteria
Form_VolunteerInformation.Caption = "Customers (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"
'Close Search Form
DoCmd.Close acForm, "Search Form"
MsgBox "Results have been filtered."
End If
End Sub
And the error message I get when searching the [Last Name] field for "adams" is as follows:
Run-time error '3075':
Syntax error (missing operator) in query expression 'Last Name LIKE '*adams*".
When I ask it to debug, it highlights the following line of code:
Form_VolunteerDatabase.RecordSource = "select * from Customers where " & GCriteria
(1) What did I do wrong and/or not replace?
(2) What does Customers refer to in the previous line of coding?