Help! Using checkboxes and search box to search multiple fields with subform

[goodhvnting]

Hello!

This is my second post in this awesome community. My first question was solved and now I am back hoping to learn more.

Problem:
I created a continuous form that has check boxes and a search box. My check box filters out results based on what is selected, and my search box can search multiple fields. Everything was working perfect until it was decided that using a subform/datasheet view is more user friendly than a continuous form. I created a new form with a subform and used the same code but now the search box will not search any fields. My check boxes work fine but the search box is not.

Hopeful Outcome:
I would like my search box to search multiple fields using keywords but at the same time follow the rules of my checkboxes with my search results populating the subform within my form. Could someone take a look at my code? Currently the checkboxes work but the search box does not. Cheers

Here is my code:

Property Get WhereLocation() As String
' construct a partial where clause based on check box settings
Dim tmp As String

If Me.chkCRM2 Then tmp = " OR LocationID = 1 "
If Me.chkRRM2 Then tmp = tmp & " OR LocationID = 2 "
If tmp <> "" Then WhereLocation = Mid(tmp, 5) 'drop the leading " OR "
End Property

Property Get WhereTextSearch() As String
' construct a partial where clause based on Me.txtSearch, if present
If Not IsNull(Me.txtSearch2) Or Me.txtSearch2 = "" Then
WhereTextSearch = _
"(Title like ""*" & Me.txtSearch2 & "*"") " & _
"Or (Keywords like ""*" & Me.txtSearch2 & "*"") " & _
"Or (Comments like ""*" & Me.txtSearch2 & "*"")"
End If
End Property

Property Get WhereClause() As String
' Construct a final WHERE clause from the two partials, above
Dim loc As String
Dim txt As String
loc = Me.WhereLocation
txt = Me.WhereTextSearch
If Len(loc) > 0 And Len(txt) > 0 Then
'if both exist, we AND them together
WhereClause = " WHERE (" & loc & ") AND (" & txt & ") "
ElseIf Len(loc & txt) > 0 Then
'otherwise we just use the one that exists
WhereClause = " WHERE " & loc & txt & " "
Else
'otherwise, there is no where clause, and we return an empty string
End If
End Property

Private Sub Command11_Click()
Me.RecordSource = _
"SELECT * FROM tblResourceRoom " & _
Me.WhereClause
End Sub

Private Sub Command12_Click()
Me.RecordSource = _
"SELECT * FROM tblResourceRoom " & _
Me.WhereClause
End Sub

Private Sub Command18_Click()
Me.chkCRM2 = False
Me.chkRRM2 = False
End Sub

Private Sub Command19_Click()
Me.chkCRM2 = True
Me.chkRRM2 = True
End Sub

 

[MarkK]

Can you print the SQL that results from this code, for instance, at this routine...

Private Sub Command11_Click()
Me.RecordSource = _
"SELECT * FROM tblResourceRoom " & _
Me.WhereClause
End Sub

...you set the recordsource of the form. Maybe print that SQL and see what it is doing...

Private Sub Command11_Click()
Me.RecordSource = _
"SELECT * FROM tblResourceRoom " & _
Me.WhereClause
[COLOR="Blue"]debug.print Me.Recordsource[/COLOR]
End Sub

...and tell us the value of Me.txtSearch2 when that code runs too.

 

[goodhvnting]

Hi MarkK,

Many thanks for taking the time to try to help me out. Again. I really appreciate it as I don't have other resources to turn to.

I am not sure how to go about printing the SQL. I tried using the code you provided but nothing pop ups that allows me to print the SQL. However, there is a good chance I am just interpreting your instructions wrong.

Would it help if posted my db? I will attach it here but I am not entirely sure if it will work because I've split my database. But I will attach it anyways in case it is helpful.

Apologies for my ignorance, and thank you again for taking the time to try to help me out. It definitely brightens my work day.

 

[MarkK]

It looks like you are applying this recordsource to the main form, but there are no data controls on the main form. Do you mean to apply that recordsource to the subform? Also, given the db you posted I don't see how your check box filter can work, since the recordsource is never applied to the subform. Do my observations make sense to you?

 

[goodhvnting]

Hi MarkK,

Yes, your observations make sense and you are correct. I am trying to apply the record source to the subform. I can now see that my checkboxes don't work... it looked like it was working but after more tinkering I could see that it really is not.

Any advice on how I can go about adding data controls to the main form?

Again, much appreciated!

 

[MarkK]

Or do you want to apply the recordsource to the subform? Does this code make sense to you? See what is happening?

Private Sub Command11_Click()
    SetSubformRecordSource
End Sub

Private Sub Command12_Click()
    SetSubformRecordSource
End Sub

Private Sub SetSubformRecordSource()
[COLOR="Green"]'   here we set the recordsource of the form in the subform control (the subform)
[/COLOR]    Me.tblResourceRoom_subform.Form.RecordSource = "SELECT * FROM tblResourceRoom " & Me.WhereClause
End Sub

 

[goodhvnting]

Yes I do see what you are doing! That makes perfect sense... I knew I was missing code that points the recordsource to the subform but I had no idea how that code should be written.

I feel like a broken record but thank you again for solving this for me. This community is the best.

All the best MarkK.

 

[MarkK]

You are welcome, all the best to you too!