Search Form VBA

[cbrsix]

I am creating a Search Form and when I am testing it out, this error code keeps coming up (see Capture). Also attached is the code that I have in there as well (see Capture1). What am I doing wrong?

Thanks.

[jzwp11]

You need a space on either side of the * and after the word search

"SELECT * FROM Search " & Buildfilter

[cbrsix]

Thanks for the quick response..I made that change and it gave me this error

[jzwp11]

The error is now pointing to your BuildFilter section

What I typically do when using the LIKE operator with the asterisk wildcard looks like this:

strWhere = strWhere & "tblProjLog.txtEngProjNo LIKE '*" & Me.engprojno & "*' AND "

You have to use single quotes to delimit text fields, so the query will see this: fieldname LIKE '*value*' OR this fieldname LIKE 'value*' (for a value at the beginning of a string)

[cbrsix]

That doesn't work...I don't get an error but it comes back with 0 results

[jzwp11]

If you got zero results that indicates that the query was constructed properly but that no records matched the criteria you put in.

What I typically do is put the query text in a variable and then push it to the immediate window using the debug.print statement so that I can see what the SQL text that was constructed looks like


Dim mySQL as string
mySQL="Select * FROM Search " & Buildfilter

debug.print mySQL

me.searchsubform.form.recordsource=mySQL

You can then copy the SQL text from the immediate window into a new query and test it directly.

[cbrsix]

I know that name is in there. I checked to make sure. If I have it set up to open in the query table and not in the subform format, it returns everything properly, but I don't want to have that extra windown come up if I can have it all in the one form via the subform..

[jzwp11]

You have to requery the subform

You have this:

Forms!SearchSubform.requery

You should have this

Forms!SearchSubform.form.requery

[Luzie]

Hi

you need an or_maker or and_maker Variable in your dyn. query.

Take a look at this sample.

Function krit_to_where() As String

    or_merker = False
    s_where = ""
    If Me!cb_a = True Then
        or_merker = True
        s_where = " RT = 'A' "
    End If
    
    If Me!cb_r1 = True Then
        If or_merker = True Then
            s_where = s_where & " OR "
        End If
        or_merker = True
        s_where = s_where & " RT = 'R1' "
    End If

    If Me!cb_r2 = True Then
        If or_merker = True Then
            s_where = s_where & " OR "
        End If
        or_merker = True
        s_where = s_where & " RT = 'R2' "
    End If
    
    If Me!cb_s = True Then
        If or_merker = True Then
            s_where = s_where & " OR "
        End If
        or_merker = True
        s_where = s_where & " RT = 'S' "
    End If
    
    'MsgBox (s_where)
    krit_to_where = s_where
    
End Function

Best regards
Luzie from Germany

[cbrsix]

I'm sorry, I don't know what that code means or does...

[cbrsix]

@jzwp11 - I made this change and it still didn't work

[jzwp11]

Using the debug.print statement I mentioned, were you able to pull the SQL text from the immediate window? If so, could you please provide that SQL text? Or if you would rather, could you zip and post a copy of your database with any sensitive data removed?

[cbrsix]

How do I do the SQL thing you mentioned above?

[cbrsix]

This is the entire code I have for this form. I noticed I didn't include all of it earlier.

Option Compare Database
Private Sub Clear_Click()
Dim intIndex As Integer
'clear all search items
Me.LastName = ""
Me.FirstName = ""
Me.AccountNumber = ""
Me.Company = ""
Me.SocialSecurityNumber = ""
End Sub
Private Sub Form_Load()
'clear the search form
Clear_Click
End Sub

Private Sub Search_Click()
'Update the record source
Me.SearchSubform.Form.RecordSource = "SELECT * FROM Search " & BuildFilter
'Requery the subform
Me.SearchSubform.Requery
End Sub
Private Function BuildFilter() As Variant
Dim varWhere As Variant
varWhere = Null 'Main Filter
'Check for LIKE Last Name
If Me.FirstName > "" Then
    varWhere = varWhere & "[LastName] Like '*" & Me.LastName & "*' And "
End If
'Check for LIKE First Name
If Me.LastName > "" Then
    varWhere = varWhere & "[FirstName] LIKE '*" & Me.FirstName & "*' And "
End If
'Check for LIKE Company
If Me.AccountNumber > "" Then
    varWhere = varWhere & "[Company] LIKE '*" & Me.Company & "*' And "
End If
'Check for LIKE Account Number
If Me.Company > "" Then
    varWhere = varWhere & "[AccountNumber] LIKE '*" & Me.AccountNumber & "*' And "
End If
'Check for LIKE Social Security Number
If Me.SocialSecurityNumber > "" Then
    varWhere = varWhere & "[SocialSecurityNumber] LIKE '*" & Me.SocialSecurityNumber & "*' And "
End If
'Check if there is a filter to return...
If IsNull(varWhere) Then
    varWhere = ""
Else
    varWhere = "WHERE" & varWhere
    
' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
    varWhere = Left(varWhere, Len(varWhere) - 5)
    End If
End If
BuildFilter = varWhere
End Function

[jzwp11]

You have to make these changes to your code first:

Dim mySQL as string
mySQL="Select * FROM Search " & Buildfilter

debug.print mySQL

me.searchsubform.form.recordsource=mySQL

Forms!SearchSubform.form.requery

While in the code window, go to the toolbar and select View-->Immediate window. Another window should open. Go back to the form and run a search. Then go back to the code window and you should see the SQL text of the query in the immediate window. Create a new query (do not select any tables or queries when prompted); switch from design grid view to SQL view. Now copy the SQL text from the immediate window to the SQL view of the query and then run the query to see if you get any errors. Also post that same text to the forum so we can see what the code generated.