Search Button Help

[Ebweaver]

Hi everyone,

I'm new to MS Access and especially to VBA code. I'm trying to have my search button in a form find contact information based on what I type in the text boxes. However, my code (that a friend tried helping me with) keeps giving me a compile error when I try and run it. Is there something that looks wrong? Any help would be much appreciated.

Edit - Seems like my requery line is giving the compile error.


Option Compare Database
Private Sub cmdSearch_Click()
'Holds the Filtered Query
Dim DataSource As String
'Set Query based on text fields
DataSource = "SELECT * FROM tMediaContacts WHERE " & _
"[Publication] like '*" & Me.txtPublication & "*' AND" & _
"[Publication_Location] like '*" & Me.txtLocation & "*' AND " & _
"[Category_type] like '*" & Me.txtCategory & "*' AND " & _
"[First_Name] like '*" & Me.txtFirst & "*' AND " & _
"[Last_Name] like '*" & Me.txtLast & "*' AND " & _
"[Email] like '*" & Me.txtEmail & "*' AND " & _
"[Title] like '*" & Me.txtTitle & "*' AND " & _
"[City] like '*" & Me.txtCity & "*' AND " & _
"[State] like '*" & Me.txtState & "*'"
'Set Form Record Source to be the Query
Me.tMediaContacts_subform.Form.RecordSource = DataSource
'Bind that query to the form
Me.tMediaContacts_subform.Requery()
Exit Sub
End Sub


Private Sub cmdClear_Click()
'Clear all Text Boxes
txtPublication = txtLocation = txtCategory = txtFirst = txtLast = txtEmail = txtTitle = txtCity = txtState = ""
'Set the Record Source to be all records
Me.tMediaContacts_subform.Form.RecordSource = "SELECT * FROM tMediaContacts "
'Bind datasource to subform
Me.tMediaContacts_subform.Requery()
'Set the focus
txtPublication.SetFocus()
End Sub

[ranman256]

1. you dont need the ME. (but it dont hurt neither)
2. are you sure the form is called tMediaContacts ? (this seems like a TABLE name, forms would start with f )
and if it IS correct then it would be me.forms!tMediaContacts_subform (you call the masterform, then FORMS, then the sub form)

[Ebweaver]

You are correct, tMediaContacts is the table name. The main form name is fSearch.

[jonh]

try
Me.tMediaContacts_subform.form.Requery
instead of
Me.tMediaContacts_subform.Requery()

[Ebweaver]

Hmm that didn't work, now when I click search after typing anything into the text boxes that I'm searching for, it pops up with a box to search for those fields again "Enter Parameter Value".

[jonh]

Well that's a problem with your query.

Easy way to check generated sql; Add a breakpoint to stop the code and type ?datasource into the immediate window and hit return.
Copy the generated SQL into a new query and run it. Find the problem and fix your code.

But scanning your code, I think you missed a space at the end of the line for publication.