Search Form to Pull All Forms Fitting Peramiters

[JewliaB]

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?

 

[pbaldy]

In your SQL string, Last Name is not bracketed as it would need to be, due to the inadvisable space.

 

[JewliaB]

So if I remove the spaces from my field names, it will run correctly? I don't normally put spaces in and didn't even realize I had.

 

[pbaldy]

It looks okay other than that. The alternative is to add brackets, but getting rid of the spaces would be my preference.

 

[JewliaB]

Well, I fixed the spaces problem and also the fact that I had refered to the database instead of the form in the "Form_VolunteerDatabase.RecordSource = "select * from Customers where " & GCriteria" line of the code, but I am now getting the error message as follows:

Run-time error '2580':
The record source 'select* from Customers where LastName LIKE'*adams*" specified on this form or report does not exist.


What is the customers refering to? I thought it might cause an issue, but I wasn't sure what it was? Is this the issue or something else?

Thanks!

 

[pbaldy]

If that's a copy/paste, you are missing some important spaces after select and like. Customers is the table you've specified the SQL look in. Is that not your table name?

 

[JewliaB]

I don't understand. Where am I missing the space. Also, fixed the customers thing and changed it to VolunteerDatabase so that it's finding the correct database at least, but the error is still refering to something else.

 

[JewliaB]

Two things:

1. The error message is as follows:

Run-time error '2580':
The record source 'select * from VolunteerDatabase where LastName LIKE '*adams*" specified on this form or report does not exist.

2. My current coding is as follows:

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_VolunteerInformation.RecordSource = "select * from VolunteerDatabase where " & GCriteria
Form_VolunteerInformation.Caption = "VolunteerDatabase (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"
'Close SearchForm
DoCmd.Close acForm, "SearchForm"
MsgBox "Results have been filtered."
End If
End Sub

What is wrong with the code? and is this the correct place to asking about this as it turned into more of a coding issue than a forms issue as I was originally thinking?

 

[pbaldy]

The SQL in post 5 was missing spaces after the words "select" and "like", but as noted might have been a typo. Is the other form open? Is it a subform or a stand-alone form? If a subform:

http://www.mvps.org/access/forms/frm0031.htm

If that's not it, can you post the db?

 

[JewliaB]

My database is the one called Volunteer Database. The database I am using to model my search after is attached as well, search2. My form is not a subform, but maybe it's supposed to be?

 

[pbaldy]

The error is because the table name is VolunteerDatebase, not Database. You'll probably want to open the form first, or you won't see the results.

 

[JewliaB]

Wow...I feel quite ridiculous for not seeing that. Thank you so much for all your help.