Search Button Help (1 Viewer)

Ebweaver

Registered User.
Local time
Today, 15:51
Joined
Jun 11, 2014
Messages
30
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


Code:
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

Wino Moderator
Staff member
Local time
Today, 15:51
Joined
Aug 30, 2003
Messages
36,118
You wouldn't want the parentheses, but you don't need those lines anyway. Setting the record source will requery the form.
 

Ebweaver

Registered User.
Local time
Today, 15:51
Joined
Jun 11, 2014
Messages
30
So taking out the requery line under both the search click and clear click should make it work?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 15:51
Joined
Aug 30, 2003
Messages
36,118
Did you try it? Can't answer for the rest of the code, but it won't error on those lines. ;)
 

Ebweaver

Registered User.
Local time
Today, 15:51
Joined
Jun 11, 2014
Messages
30
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

Wino Moderator
Staff member
Local time
Today, 15:51
Joined
Aug 30, 2003
Messages
36,118
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

Registered User.
Local time
Today, 15:51
Joined
Jun 11, 2014
Messages
30
By the 1st and 2nd forms, would that mean using the subform to display the record that the main form is finding?
 

Users who are viewing this thread

Top Bottom