By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,730 Members | 1,659 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,730 IT Pros & Developers. It's quick & easy.

Can a Combobox and Keyword be used in the same search

P: 2
I have created a database to collect daily notes from our QA personnel. I have a search form (split form) that I can search all of the notes by entering a keyword and clicking a search button (code below). This works great. I now want to be able to filter the key word search by individual (selecting the person from a combobox) if necessary. Iím new to Access and I canít figure out how to do this in VBA. Can someone please help me.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearchText_Click()
  2. Dim strSearch As String
  3. Dim strText As String
  4.  
  5. Me.Refresh
  6. If IsNull(Me.txtKeywords) Then
  7.     MsgBox "Please enter keyword(s)", vbInformation, "Keyword Required"
  8.     Me.txtMemberName.SetFocus
  9. Else
  10.     strText = Me.txtKeywords.Value
  11.     strSearch = "select * From TblQANotes where (Notes like ""*" & strText & "*"")"
  12.     Me.RecordSource = strSearch
  13.  
  14. End If
  15.  
  16. If Not IsNull(Me.txtKeywords) And IsNull(Me.Notes) Then
  17.     MsgBox "Please try again", vbInformation, "Keyword Not Found"
  18.     Me.txtKeywords.SetFocus
  19.  
  20. Else
  21.  
  22.  
  23. End If
  24.  
  25. End Sub
1 Week Ago #1

✓ answered by twinnyfo

WrdsFrst,

Welcome to Bytes!

Short answer: Yes - you can do this.

Much of what you want to do, you have already accomplished, but only for the Keyword Search. Let's take a look at some proposed changes:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearchText_Click()
  2.     Dim strSQL    As String
  3.     Dim strWhere  As String
  4.     Dim strText   As String
  5.  
  6.     Me.Refresh
  7.     strSQL = "SELECT * FROM tblQANotes "
  8.  
  9.     strWhere = ""
  10.     If IsNull(Me.txtKeywords) Then
  11.         MsgBox "Please enter keyword(s)", _
  12.                vbInformation, _
  13.                "Keyword Required"
  14.         Me.txtMemberName.SetFocus
  15.     Else
  16.         strText = Me.txtKeywords
  17.         strWhere = "WHERE (Notes LIKE '*" & strText & "*')"
  18.     End If
  19.  
  20.     If Not IsNull(Me.cboPerson) Then
  21.         strText = Me.cboPerson
  22.         strWhere = strWhere & _
  23.             IIf(strWhere = "", _
  24.                 "WHERE ", _
  25.                 " AND ") & _
  26.             "(Person = '" & strText & "')"
  27.     End If
  28.  
  29.     strSQL = strSQL & strWhere & ";"
  30.     Me.RecordSource = strSQL
  31.  
  32.  
  33.     If Not IsNull(Me.txtKeywords) And IsNull(Me.Notes) Then
  34.         MsgBox "Please try again", _
  35.                vbInformation, _
  36.                "Keyword Not Found"
  37.         Me.txtKeywords.SetFocus
  38.     Else
  39.  
  40.     End If
  41.  
  42. End Sub
You'll see there are a few minor mods to the approach here. I've decalred a variable for the SQL statement and a separate variable for the WHERE clause--this just allows us to manage things a bit more easilier.

The second If-Then is looking for the value in your combo box (NB: the code above assumes a text value for that combo box--you will have to modify accordingly if it is an indexed value in the combo box (which many of us would would have as a standardized design).

I haven't tested this code, so it may not quite work. However, the concepts should move you in the right direction here.

Standing by for further assistance.

Hope this hepps!

Share this Question
Share on Google+
3 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,328
WrdsFrst,

Welcome to Bytes!

Short answer: Yes - you can do this.

Much of what you want to do, you have already accomplished, but only for the Keyword Search. Let's take a look at some proposed changes:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearchText_Click()
  2.     Dim strSQL    As String
  3.     Dim strWhere  As String
  4.     Dim strText   As String
  5.  
  6.     Me.Refresh
  7.     strSQL = "SELECT * FROM tblQANotes "
  8.  
  9.     strWhere = ""
  10.     If IsNull(Me.txtKeywords) Then
  11.         MsgBox "Please enter keyword(s)", _
  12.                vbInformation, _
  13.                "Keyword Required"
  14.         Me.txtMemberName.SetFocus
  15.     Else
  16.         strText = Me.txtKeywords
  17.         strWhere = "WHERE (Notes LIKE '*" & strText & "*')"
  18.     End If
  19.  
  20.     If Not IsNull(Me.cboPerson) Then
  21.         strText = Me.cboPerson
  22.         strWhere = strWhere & _
  23.             IIf(strWhere = "", _
  24.                 "WHERE ", _
  25.                 " AND ") & _
  26.             "(Person = '" & strText & "')"
  27.     End If
  28.  
  29.     strSQL = strSQL & strWhere & ";"
  30.     Me.RecordSource = strSQL
  31.  
  32.  
  33.     If Not IsNull(Me.txtKeywords) And IsNull(Me.Notes) Then
  34.         MsgBox "Please try again", _
  35.                vbInformation, _
  36.                "Keyword Not Found"
  37.         Me.txtKeywords.SetFocus
  38.     Else
  39.  
  40.     End If
  41.  
  42. End Sub
You'll see there are a few minor mods to the approach here. I've decalred a variable for the SQL statement and a separate variable for the WHERE clause--this just allows us to manage things a bit more easilier.

The second If-Then is looking for the value in your combo box (NB: the code above assumes a text value for that combo box--you will have to modify accordingly if it is an indexed value in the combo box (which many of us would would have as a standardized design).

I haven't tested this code, so it may not quite work. However, the concepts should move you in the right direction here.

Standing by for further assistance.

Hope this hepps!
1 Week Ago #2

NeoPa
Expert Mod 15k+
P: 31,606
As Twinny says, the short answer is Yes.

I won't add to Twinny's suggestion other than to post a link (Cascaded Form Filtering) to an article that covers similar ground. You may find it helpful to have a look at the design and understand what is going on.

In simple terms, finding records in a database is most often arrived at by filtering the records in your Recordset. In a standard SQL SELECT query that's done using the WHERE clause but this same clause is approximated within Access for many of its objects. When I say it's approximated I mean that the same syntax is used for a string that filters a Form or Report as that found within a SQL WHERE clause.

Once you understand the power of that you can do all sorts of things to help your users specify exactly which record (or records even) they may be interested in.
1 Week Ago #3

P: 2
Thank you both for your replies. As I stated, I'm new to access, and I appreciate your patients as I try to teach this old dog new tricks. I updated my code with twinnyfo's suggestions but when I use a keyword that is in my database, I get the "Keyword Not Found" message. Not sure what to do next. Here is the new code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearchText_Click()
  2.     Dim strSQL    As String
  3.     Dim strWhere  As String
  4.     Dim strText   As String
  5.  
  6.     Me.Refresh
  7.     strSQL = "SELECT * FROM QANotes2 "
  8.  
  9.     strWhere = ""
  10.     If IsNull(Me.txtKeywords) Then
  11.         MsgBox "Please enter keyword(s)", _
  12.                vbInformation, _
  13.                "Keyword Required"
  14.         Me.txtKeywords.SetFocus
  15.     Else
  16.         strText = Me.txtKeywords
  17.         strWhere = "WHERE (Notes LIKE '*" & strText & "*')"
  18.     End If
  19.  
  20.     If Not IsNull(Me.CboContributor) Then
  21.         strText = Me.CboContributor
  22.         strWhere = strWhere & _
  23.             IIf(strWhere = "", _
  24.                 "WHERE ", _
  25.                 " AND ") & _
  26.             "(Contributor = '" & strText & "')"
  27.     End If
  28.  
  29.     strSQL = strSQL & strWhere & ";"
  30.     Me.RecordSource = strSQL
  31.  
  32.  
  33.     If Not IsNull(Me.txtKeywords) And IsNull(Me.Notes) Then
  34.         MsgBox "Please try again", _
  35.                vbInformation, _
  36.                "Keyword Not Found"
  37.         Me.txtKeywords.SetFocus
  38.     Else
  39.  
  40.     End If
  41.  
  42. End Sub
1 Week Ago #4

Post your reply

Sign in to post your reply or Sign up for a free account.