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.

Seems like my requery line is giving the compile error (syntax).

Main form name = fSearch

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

 

[pbaldy]

You wouldn't want the parentheses, but you don't need those lines anyway. Setting the record source will requery the form.

 

[Ebweaver]

So taking out the requery line under both the search click and clear click should make it work?

 

[pbaldy]

Did you try it? Can't answer for the rest of the code, but it won't error on those lines.

 

[Ebweaver]

Unfortunately it didn't work. Also, instead of recognizing what I type into the textboxes, when I click the search button a box pops up and asks for each parameter value.

 

[pbaldy]

It would probably be more accurate to say that part worked and has now revealed the next problem.

The parameter prompt is Access telling you it can't find something. Try this to see how the SQL is coming out:

http://www.baldyweb.com/wherecondition.htm

I personally would build the criteria using only the search fields that were filled out, but that's me.

 

[Ebweaver]

By the 1st and 2nd forms, would that mean using the subform to display the record that the main form is finding?

 

[pbaldy]

I couldn't figure out what you were talking about until I realized I grabbed the wrong link. Sorry about that.

Try this one:

http://www.baldyweb.com/ImmediateWindow.htm