Search Form VBA

[cbrsix]

I'm sorry, but I don't know if it's just been long day, but where do I put the SQL code in my current code?

Dim mySQL as string


mySQL="Select * FROM Search " & Buildfilter

debug.print mySQL

me.searchsubform.form.recordsource=mySQL

[jzwp11]

You currently have this (based on your earlier post)

Private Sub Search_Click()
'Update the record source
Me.SearchSubform.Form.RecordSource = "SELECT * FROM Search " & BuildFilter
'Requery the subform
Me.SearchSubform.Requery
End Sub


You need to replace it with the code I provided; like this:

Private Sub Search_Click()
'Update the record source
Dim mySQL as string
mySQL="Select * FROM Search " & Buildfilter
debug.print mySQL
me.searchsubform.form.recordsource=mySQL
'Requery the subform

Forms!SearchSubform.form.requery
End Sub

Basically, I'm getting rid of the stuff shown in red above.

[cbrsix]

This is what showed up in the immediate window after I updated the code like you suggested.

Select * FROM Search WHERE[FirstName] LIKE '*Brad*'
Select * FROM Search WHERE[FirstName] LIKE '**'
Select * FROM Search WHERE[FirstName] LIKE '**'
Select * FROM Search WHERE[LastName] Like '**'

[cbrsix]

Then this message came up when I put it in a new query like you suggested..

[jzwp11]

You only tried to run the search once or 4 times? The first query show looks valid. The others will probably just return all records.

[cbrsix]

I tried it 4 times then. That included me clearing it to see what happened. I tried it again this morning and it just returns all values...any idea on why if the first query looks valid?

[cbrsix]

I want to thank you for your help..I figured it out. My field names were not spelled correctly. When I originally created the database, I had put spaces in the field names, I thought that I went through and corrected all of them to have no spaces, but apparently I forgot to update these fields. Dumb mistake.

[jzwp11]

You're welcome. I'm glad you got it figured out.