Creating a Search Form

forms_are_nightmares

Registered User.
Local time
Today, 05:20
Joined
Apr 5, 2010
Messages
71
Hello All,

I'm trying to create a search within a form and keep receiving a "sytax error in FROM clause" and I can't figure out why. Any help/insight would be appreciated.

From a button labeled "search" on the form, I have in the OnClick property:

Option Compare Database
Option Explicit

Private Sub Button_Search_Click()
Me.frm_search_subform.Form.RecordSource = "SELECT * FROM qry_search " & BuildFilter
Me.frm_search_subform.Requery
End Sub

Private Function BuildFilter() As Variant
Dim varWhere As Variant
varWhere = Null
If Me.Company > "" Then
varWhere = varWhere & "[Company Name on W-9] LIKE """ & Me.Company & "*"" AND "
End If

BuildFilter = varWhere
End Function

Essentially, I want to get it to work for the first box and then integrate others.

Thanx
 
A few things wrong/redundant in your code for the BuildFilter function.

1. Declare the function as String and not Variant. Same goes for varWhere
2. Initialising varWhere as Null is not necessary because a value not initialised would normally be Null. So that means in actually fact you aren't initialising the variable. Hence, in your case there's no point initialising. If however, you were going to initialise it as a string use the empty string ---> "" and in all honesty you don't really need that variable (in that function), just set it to the function directly.
3. Comparing the empty string to your field Me.Company using the greater than operator (>) wouldn't work. You don't use that operator for strings, you use it for type Number.
4. Another big point is don't use spaces or hyphens when naming your fields. That is this field name [Company Name on W-9]
5. When you're enclosing a quote, for readability it's best you use Chr(34) or alternate the single quote and the double quote.
6. In your string you had the AND at the end as well.
7. To be honest, you don't need the BuildFilter function.

To sum up, it should look like this:
Code:
Private Sub Button_Search_Click()
    Dim strWhere as String

    If Len(Me.Company) > 0 Then
         strWhere = " WHERE [Company[COLOR=Blue][B]_[/B][/COLOR]Name[COLOR=Blue][B]_[/B][/COLOR]on[B][COLOR=Blue]_[/COLOR][/B]W9] LIKE [COLOR=Red][B]'[/B][/COLOR]" &  Me.Company & "*[COLOR=Red][B]'[/B][/COLOR];"
    Else
        strWhere = ";"
    End If

    Me.frm_search_subform.Form.RecordSource = "SELECT * FROM qry_search"  & strWhere
    Me.frm_search_subform.Requery
 End Sub
You could also use the Filter and FilterOn properties of the subform which would save you the hassle of having to "reset" the recordsource. Investigate those, lots of examples on this forum.

Here's a link that could help too:
http://www.mvps.org/access/forms/frm0045.htm
 
I'm still having some issues with the from statement. I've modified my code and this is what I now have.

Private Sub Button_Search_Click()
Dim strWhere As String
If Not IsNull(Me.Company) Then
strWhere = "WHERE [Company_Name_on_W9] LIKE " & Me.Company & ""
Else
strWhere = ";"
End If
Me.frm_search_subform.Form.RecordSource = "SELECT * FROM [qry_search] & strWhere"
Me.frm_search_subform.Requery
End Sub


The other question/issue is how would i integrate other fields into the search?

Thanks again.
 
Since I posted the previous reply, I've been playing around with the code...When I run this, I receive a Run-time error '2101': The setting you entered isn't valid for this property.

The code:

Private Sub Button_Search_Click()
Dim strWhere As String
If Me.Company > "" Then
strWhere = "WHERE [Company_Name_on_W9] LIKE *" & Me.Company & "*"
Else
strWhere = ";"
End If
Me.frm_search_subform.Form.RecordSource = "SELECT * FROM qry_search & strWhere"
Me.frm_search_subform.Requery
End Sub


any insight is appreciated...
 

Users who are viewing this thread

Back
Top Bottom