Help! Search using checkboxes and search box for multiple fields

[goodhvnting]

Hello!

This is my first post asking for help in this awesome community so please bare with me as I try to explain my problem.

Problem:
I am hoping someone can help me with part of my code. I have check boxes and a search box that both work. My checkbox filters out results based on what is selected, and my search box can search multiple fields. The problem is when I try to combine both the checkbox and search box I lose the ability to search multiple fields in my search box.

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. Could someone take a look at my code? Currently the checkboxes only apply to field "Title" but I want it to also apply to search fields "Keywords" and "Comments". Any help would be very much appreciated! Cheers

Here is my code and I will also attach my db:

Private Sub Command637_Click()
'Check if search parameters exist
Dim strsearch As String
Dim strtext As String
Dim Task As String
Dim CRM As Integer
Dim RRM As Integer

If IsNull(Me.txtSearch) Or Me.txtSearch = "" Then
MsgBox "Please type in your search keyword", vbOKOnly, "Keyword Needed"
Me.txtSearch.BackColor = vbYellow
Me.txtSearch.SetFocus

Else
Me.Refresh
strsearch = Me.txtSearch.Value

If Me.chkCRM = True Then
CRM = 1
End If
If Me.chkRRM = True Then
RRM = 2
End If

strsearch = Me.txtSearch.Value
Task = "SELECT * FROM tblResourceRoom WHERE ((([LocationID] = " _
& CRM & ") Or ([LocationID] = " _
& RRM & ")) And (Title like ""*" & strsearch & "*"") Or (Keywords like ""*" & strsearch & "*"") Or (Comments like ""*" & strsearch & "*""))"
Me.RecordSource = Task
End If
End Sub

Private Sub Command638_Click()
Dim strsearch As String
strsearch = "SELECT * from tblResourceRoom"
Me.RecordSource = strsearch
End Sub

Private Sub Command657_Click()
Me.chkCRM = False
Me.chkRRM = False
End Sub

Private Sub Command658_Click()
Me.chkCRM = True
Me.chkRRM = True
End Sub

Private Sub txtSearch_AfterUpdate()
Call Command637_Click
End Sub

 

[MarkK]

What's going wrong is that if the user hasn't specified anything for the text search, you abandon the procedure, right . . .

If IsNull(Me.txtSearch) Or Me.txtSearch = "" Then
[COLOR="Green"]   'if true, you message the user and abandon the search[/COLOR]

But no such check is made for the CheckBoxes. If chkCRM is false and chkRRM is false, then your WHERE clause can NEVER be true, and so it will never select records in that case.

You need to conditionally construct your WHERE clause, and if chkCRM is false, you need to exclude if from the WHERE clause entirely, and not test for [LocationID] = 0 (because if chkCRM = False, then your CRM variable = 0, and that is executed in your WHERE clause).

If chkCRM is false, and chkRRM is false, then your SQL should be . . .

Task = _
   "SELECT * FROM tblResourceRoom " & _
   "WHERE (Title like ""*" & strsearch & "*"") " & _
      "Or (Keywords like ""*" & strsearch & "*"") " & _
      "Or (Comments like ""*" & strsearch & "*""))"

See what I mean?

 

[MarkK]

So what I would do in this case is make a few properties that construct the SQL automatically based on the values in the controls, like . . .

Property Get WhereLocation as string
[COLOR="Green"]'  construct a partial where clause based on check box settings[/COLOR]
   dim tmp as string

   If Me.chkCRM Then tmp = " OR LocationID = 1 "
   If Me.chkRRM Then tmp = tmp & " OR LocationID = 2 "
   if tmp <> "" then WhereLocation = Mid(tmp, 5) [COLOR="Green"]'drop the leading " OR "[/COLOR]
End Property

Property Get WhereTextSearch as string
[COLOR="Green"]'  construct a partial where clause based on Me.txtSearch, if present[/COLOR]
   If NOT IsNull(Me.txtSearch) Or Me.txtSearch = "" Then
      WhereTextSearch = _
          "(Title like ""*" & Me.txtSearch & "*"") " & _
          "Or (Keywords like ""*" & Me.txtSearch & "*"") " & _
          "Or (Comments like ""*" & Me.txtSearch & "*""))"
   End If
End Property

Property Get WhereClause as string
[COLOR="Green"]'   Construct a final WHERE clause from the two partials, above[/COLOR]
   dim loc as string
   dim txt as string
   loc = me.WhereLocation
   txt = me.WhereTextSearch
   If len(loc) > 0 AND len(txt) > 0 then
[COLOR="Green"]      'if both exist, we AND them together[/COLOR]
      WhereClause = " WHERE (" & loc & ") AND (" & txt & ") "
   ElseIf len(loc & txt) > 0 then
[COLOR="Green"]      'otherwise we just use the one that exists[/COLOR]
      WhereClause = " WHERE " & loc & txt & " "
   Else
[COLOR="Green"]      'otherwise, there is no where clause, and we return an empty string[/COLOR]
   End If
End Property

And now all the heavy lifting is done by those properties, and your Private Sub Command637_Click() can look like . . .

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

In this way we can completely reconstruct the SQL WHERE clause based on the setting in the form.

 

[goodhvnting]

Thank you MarkK for taking the time to try to help me. I really appreciate it as I am a bit overwhelmed by all of this.

I must admit that I have absolutely not programming experience and have been trying to create this database for my job using internet resources and youtube videos. Therefore, I hope you don't hold it against me when I say I do not quite understand what I am supposed to do next.

Do I paste in your property codes and then replace my Sub Command637_Click entirely with the code you provided at the bottom? I've tried to interpret your instruction but keep ending up with errors. :banghead:

I understand the logic behind having to construct a clause for when my chkCRM/RRM is false I am just not sure where to paste that code into what I already have... should I be taking something out or just adding to it?

Again, apologies for my ignorance. I truly appreciate the help though as I really don't have anywhere else to turn to, to ask these questions.

Thanks again. JL

 

[goodhvnting]

Nevermind!

I figured it out!

I had an extra bracket one of the property clauses.

MarkK... again - so many thanks for responding and taking the time to show me exactly what I was doing wrong.

Many thanks, I would not have been able to figure this out without your help and this community.

 

[MarkK]

Yeah, you bet. I think this is an interesting problem you asked about, and a common one, to build SQL on the fly like this. It seemed like a good opportunity to show a method that I find simplifies the process considerably.
All the best!