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.