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

How to search for a specific keyword within a record/ field using a combobox

100+
P: 547
I would like to use a combobox and when i add a specific word, it will search for that "word" throughout every record in that specific field of the table.
ie
1. Paracetamol 500mg tablets
2. Ciprobay 500mg tablets
3. Amoxil 250mg tablets
(in npitems) field in table

If i add 500mg to the combobox (to search within the "npitems" txt field), it should filter and display items no 1 + 2 and exclude no 3, as it has to have "500mg" in the title, added to the combobox

It does not have to have "Matchcase"
Query name: NPItemsQ
This is how far i got


i have tried this -
Expand|Select|Wrap|Line Numbers
  1. Me![NPItems].SetFocus
  2.  DoCmd.FindRecord Me!Combo51, acEntire, , acSearchAll
  3.   and this 
  4.    'DoCmd.SearchForRecord , "", acFirst, "[NPItems] = " & "'" & Screen.ActiveControl & "'"
and tried this
Expand|Select|Wrap|Line Numbers
  1. Dim rs As DAO.Recordset
  2.  
  3.     If Not IsNull(Me.cbomoveto) Then
  4.         'Save before move.
  5.         If Me.Dirty Then
  6.             Me.Dirty = False
  7.         End If
  8.         'Search in the clone set.
  9.         Set rs = Me.RecordsetClone
  10.  
  11.         rs.FindFirst "[npitems] = """ & Me.cbomoveto & """"
  12.  
  13.         If rs.NoMatch Then
  14.             MsgBox "Not found: filtered?"
  15.         Else
  16.             'Display the found record in the form.
  17.             Me.Bookmark = rs.Bookmark
  18.         End If
  19.         Set rs = Nothing
  20.     End If
All the above code work to an extent, but i still can't search/find the criteria (keyword) i need, when a word is part of a number of words in that record.
pls assist
Apr 27 '12 #1
Share this Question
Share on Google+
14 Replies


NeoPa
Expert Mod 15k+
P: 31,494
Assuming you have a control on your form called [cboNPItems] then :
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboNPItems_AfterUpdate()
  2.     With Me
  3.         .Filter = IIf(.cboNPItems > "", "[NPItems] Like '*" & .cboNPItems & "*'", "")
  4.         .FilterOn = (.Filter > "")
  5.     End With
  6. End Sub
Apr 27 '12 #2

100+
P: 547
Hi N
Cant get it to work. I include a copy of that specific issue below.

It does not selectively allow you to search for 1 component of a record
ie bleach 5L then i want to add 5L, and every item that contains 5L in the complete field must show up in combobox pls
thx for assistance
thx
Apr 28 '12 #3

100+
P: 547
I have also added an unbound search box now
Code in unbound searchbox that work well but does not filter requested data as required
Expand|Select|Wrap|Line Numbers
  1. Dim stDocName As String
  2.    stDocName = "AddConsumablesf"
  3.   stLinkCriteria = "[NPItems]=" & "'" & Me![cbonpitems] & "'"
  4.    DoCmd.OpenForm stDocName, , , stLinkCriteria
  5.  
  6.     DoCmd.close acForm, "frmSearchNPitems"
My file will not save as access 2003.


see attachment pls
Apr 28 '12 #4

100+
P: 759
Take a look here .
Apr 28 '12 #5

100+
P: 547
thx MiHail - i tried it and it works for a single field to find an item. I would like it to show all related fields on the form.
Ie if i call up an item, then it must also add the pack size and price of that item to the form.
Apr 28 '12 #6

100+
P: 759
Declare more fields in that query.

Sorry but I am not sure that I fully understand your requirement.
Do you wish to use the same control (combo box) to look in all fields ?
Apr 28 '12 #7

100+
P: 547
It must only look in the NPItems field

Is there not perhaps a way of using
Expand|Select|Wrap|Line Numbers
  1. Like "*" & [forms]![frmsearchnpitems]![cbonpitems] & "*"
in the row source query of the combobox ? I just cant get it to work.
Apr 28 '12 #8

100+
P: 759
Sorry again, but I must disappoint you: I have tried a lot to use your solution until I "discover" my solution. Of course, you can try again and I'll be very happy if you can find a better one.
Apr 28 '12 #9

100+
P: 547
I cant get your solution to call up all related fields of that record. For a single field it works fine
Apr 28 '12 #10

100+
P: 547
After 4 hours i got it working reasonably well now.
Expand|Select|Wrap|Line Numbers
  1. Like "*" & [Enter a Keyword] & "*"
I use the above code in the specific field of the Row Source query, of the Combobox, a popup box appears to add ie 500ml, and then all items that contains 500ml in the title of the different records will show up in the combobox.I then select the appropriate one
To add a new keyword, i created a button with the following code to clear the current fields and the start all over again
Expand|Select|Wrap|Line Numbers
  1. Me.Combo38.Value = ""
  2.     DoCmd.RunCommand acCmdRefresh
  3.     DoCmd.GoToRecord , "", acNewRec
Apr 28 '12 #11

NeoPa
Expert Mod 15k+
P: 31,494
Neels, I'm really not sure what you're after, nor why you posted the question you did if that was not what you wanted. Even now it is entirely unclear what you actually want (one post seems to contradict another - such that they can't both be true and it's hard to know which is in such circumstances). If you can specify the requirement clearly then I'll look again, but at the moment I'm just trying to guess what you want, which is not good use of anyone's time.
Apr 28 '12 #12

100+
P: 547
Hi N
Thx for your efforts. All i needed to do was to have more "flexibility" when trying to find items in my tables as the normal "search" only find the first word that that you add. You put me on the right track with the "like" code in your original posting and i managed to adapt it a bit, to refine my search box. I wanted to use it for a medicine and non medicine tables, where you need to drill down a bit further to find an item, as the names can be different for the same item and this way one can find it easily
This simple code below in the row source query of the combobox did the trick for me, as i now can look up an item in any part of a record and field
I use the "on click" event in the combobox to start the search process in conjunction for normal search code that you get from the Access wizard
Expand|Select|Wrap|Line Numbers
  1. DoCmd.SearchForRecord , "", acFirst, "[npitems] = " & Str(Nz(Screen.ActiveControl, 0)) 
and
Expand|Select|Wrap|Line Numbers
  1. Like "*" & [Enter a Keyword] & "*"
Apr 29 '12 #13

P: 21
Try this code, It can help you
When you type 500 then it will display all names containing 500 only
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboNPItems_AfterUpdate()
  2.     With Me
  3.         .Filter = IIf(.cboNPItems > "", "[NPItems] Like '%" & .cboNPItems & "%'", "")
  4.         .FilterOn = (.Filter > "")
  5.     End With
  6. End Sub
Apr 29 '12 #14

NeoPa
Expert Mod 15k+
P: 31,494
@Roshan.
That code is simply the same code that is suggested in post #2 except it uses ANSI-92 wildcards instead of ANSI-89. ANSI-92 is an option in Access (since version 2003) but the default is still ANSI-89, so your code will only work in such cases that the user has their Access Options set up to use ANSI-92 explicitly.

As it has already been suggested and found wanting though (for reasons which are still not clear to me I must admit), it's hard to see where this might fit in.

@Neels.
I guess you have what you need, though I didn't find anything in your explanation that made it any clearer what you were trying to do. Your first bit of code gives a clue that it is something quite different from what's been explained (as it seems to be dealing with whatever the currently focused control is), but as that's the only clue I cannot say much more. Reading further, and trying to make some sense of it, it seems that this will always refer to the ComboBox control anyway (as you explain it runs from the cboNPItems_Click() event) so I have no idea why you're coding it in such a strange way (Why use Screen.ActiveControl instead of Me.cboNPItems).

One last comment. Are you sure you want to search for a single record? Typically, it makes more sense to filter all the records such that only those records that match the criteria are shown, but all such records are included in the list. That enables further filtering to be carried out as well as simply scrolling through the list in order to find either one, or all the records of interest. In case it is of interest to you, here is a link to an article on Cascaded Form Filtering.
Apr 29 '12 #15

Post your reply

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