Solved: VBA search code

[RonNCmale]

I have the following code that works well using two search boxes. Wondering if there is a way to add another line of code to be able to use three search boxes?

Private Sub cmdSearch_Click()
If Not (Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True) Then
If Len(cboSearchField1) = 0 Or IsNull(cboSearchField1) = True Then
GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
Form_frmSTG.RecordSource = "select * from STG where " & GCriteria
DoCmd.Close acForm, "frmSTGSearch"
MsgBox "Results have been filtered."
Else
If Not (Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True) Then
GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
GCriteriasec = cboSearchField1.Value & " LIKE '*" & txtSearchString1 & "*'"
Form_frmSTG.RecordSource = "select * from STG where " & GCriteria & " And " & GCriteriasec
DoCmd.Close acForm, "frmSTGSearch"
MsgBox "Results have been filtered."
End If
End If
End If
End Sub

[Tommy]

If I am getting what you want this should do it. This has not been tested!!

Private Sub cmdSearch_Click()
Dim GCriteria As String, GCriteriasec As String, GCriteriathrd As String
'if the len is 0 it = false anything else is true
If Len(cboSearchField) Then
GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
End If
If Len(cboSearchField1) Then
GCriteriasec = cboSearchField1.Value & " LIKE '*" & txtSearchString1 & "*'"
End If
If Len(cboSearchField2) Then
GCriteriathrd = cboSearchField2.Value & " LIKE '*" & txtSearchString2 & "*'"
End If
If Len(GCriteria & GCriteriasec & GCriteriathrd) Then
Form_frmSTG.RecordSource = "select * from STG where " & GCriteria & GCriteriasec & GCriteriathrd
DoCmd.Close acForm, "frmSTGSearch"
MsgBox "Results have been filtered."
End If
End Sub

[Tommy]

I thought of this later. Still untested.

Private Sub cmdSearch_Click()
Dim GCriteria As String, GCriteriasec As String, GCriteriathrd As String
'if the len is 0 it = false anything else is true
If Len(cboSearchField) Then
GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
End If
If Len(cboSearchField1) Then
GCriteriasec = cboSearchField1.Value & " LIKE '*" & txtSearchString1 & "*'"
If GCriteria > "" Then GCriteriasec = " AND " & GCriteriasec
End If
If Len(cboSearchField2) Then
GCriteriathrd = cboSearchField2.Value & " LIKE '*" & txtSearchString2 & "*'"
If GCriteria > "" Or GCriteriasec > "" Then GCriteriathrd = " AND " & GCriteriathrd
End If
If Len(GCriteria & GCriteriasec & GCriteriathrd) Then
Form_frmSTG.RecordSource = "select * from STG where " & GCriteria & GCriteriasec & GCriteriathrd
DoCmd.Close acForm, "frmSTGSearch"
MsgBox "Results have been filtered."
End If
End Sub

[RonNCmale]

Thanks, I didn't try the first code, but the second one works great. Thanks again

[RonNCmale]

Is there some code to add to this Search Code to be able to print only the results of the search. I have a report named STG.

[RonNCmale]

On my original VBA code it had Call rsSearch

in Modules It has Module1 with the following code:


Option Compare Database
'public variables are needed
Public rsSTG As Recordset
Public GCriteria As String
Sub rsSearch()
'Returns a recordset based on STG Forms filter
Set rsSTG = CurrentDb.OpenRecordset("SELECT * FROM STG WHERE " & GCriteria)
DoCmd.OpenReport "STG", acViewPreview
rsSTG.Close
Set rsSTG = Nothing
End Sub

This worked well with only one search box, now that I have three It doesn't work anymore.

[Tommy]

Does your report "STG" have the 2 extra fields?
I think I would set it up for it to call reports based on the number of fields selected.

[RonNCmale]

Not exactly understanding. The STG report has the fields I want to show. If I use only one search field It will show only those results on the STG report. I was wondering if I used your multiple search VBA if the rsSearch VBA could be converted to open up the report STG with the search results. I hope this is clear!

[Tommy]

It looks like this statement is wrong
Set rsSTG = CurrentDb.OpenRecordset("SELECT * FROM STG WHERE " & GCriteria)

it will need to change to
Set rsSTG = CurrentDb.OpenRecordset( "select * from STG where " & GCriteria & GCriteriasec & GCriteriathrd )

[RonNCmale]

I got the following Error:

Run time error '3145'
Syntax error in WHERE clause

[Tommy]

The problem in the search criteria is the global variable "GCriteria" is the only one being used. So when the variables "GCriteria", "GCriteriasec", "GCriteriathrd" are being set in the search it would need to be set as
GCriteria=GCriteria & GCriteriasec & GCriteriathrd
Then the original statement should work.:yes

Otherwise you will need to post the database and let me find out the issue this weekend.:(

[RonNCmale]

Note the search button is the one that gives the error, the search/Print is the one option search that works. Thanks in advance for your time with this.

[Tommy]

In the frmSTGSearch form:

Private Sub cmdSearch_Click()
'I changed this
'Dim GCriteria As String, GCriteriasec As String, GCriteriathrd As String
'to this
Dim GCriteriasec As String, GCriteriathrd As String
'if the len is 0 it = false anything else is true
If Len(cboSearchField) Then
GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
End If
If Len(cboSearchField1) Then
GCriteriasec = cboSearchField1.Value & " LIKE '*" & txtSearchString1 & "*'"
If GCriteria > "" Then GCriteriasec = " AND " & GCriteriasec
End If
If Len(cboSearchField2) Then
GCriteriathrd = cboSearchField2.Value & " LIKE '*" & txtSearchString2 & "*'"
If GCriteria > "" Or GCriteriasec > "" Then GCriteriathrd = " AND " & GCriteriathrd
End If
If Len(GCriteria & GCriteriasec & GCriteriathrd) Then
'added this line
GCriteria = GCriteria & GCriteriasec & GCriteriathrd
'changed this
'Form_frmSTG.RecordSource = "select * from STG where " & GCriteria & GCriteriasec & GCriteriathrd
'to this
Form_frmSTG.RecordSource = "select * from STG where " & GCriteria
DoCmd.Close acForm, "frmSTGSearch"
MsgBox "Results have been filtered."
End If
Call rsSearch
End Sub



The global variable GCriteria was declared as a local variable also.
:hi:

[Tommy]

I thought of a possible problem. :)


Private Sub cmdSearch_Click()
'I changed this
'Dim GCriteria As String, GCriteriasec As String, GCriteriathrd As String
'to this
Dim GCriteriasec As String, GCriteriathrd As String
GCriteria = "" 'this could be a problem if it isn't cleared first
'if the len is 0 it = false anything else is true
If Len(cboSearchField) Then
GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
End If
If Len(cboSearchField1) Then
GCriteriasec = cboSearchField1.Value & " LIKE '*" & txtSearchString1 & "*'"
If GCriteria > "" Then GCriteriasec = " AND " & GCriteriasec
End If
If Len(cboSearchField2) Then
GCriteriathrd = cboSearchField2.Value & " LIKE '*" & txtSearchString2 & "*'"
If GCriteria > "" Or GCriteriasec > "" Then GCriteriathrd = " AND " & GCriteriathrd
End If
If Len(GCriteria & GCriteriasec & GCriteriathrd) Then
'added this line
GCriteria = GCriteria & GCriteriasec & GCriteriathrd
'changed this
'Form_frmSTG.RecordSource = "select * from STG where " & GCriteria & GCriteriasec & GCriteriathrd
'to this
Form_frmSTG.RecordSource = "select * from STG where " & GCriteria
DoCmd.Close acForm, "frmSTGSearch"
MsgBox "Results have been filtered."
End If
Call rsSearch
End Sub