473,322 Members | 1,523 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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

547 512MB
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
14 3084
NeoPa
32,556 Expert Mod 16PB
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
neelsfer
547 512MB
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
neelsfer
547 512MB
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
Mihail
759 512MB
Take a look here .
Apr 28 '12 #5
neelsfer
547 512MB
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
Mihail
759 512MB
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
neelsfer
547 512MB
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
Mihail
759 512MB
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
neelsfer
547 512MB
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
neelsfer
547 512MB
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
32,556 Expert Mod 16PB
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
neelsfer
547 512MB
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
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
32,556 Expert Mod 16PB
@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

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

Similar topics

2
by: mahesr | last post by:
Frds, how to crawl a specific word in whole website using php.need which page it has Example: want to crawl a 144 items found,on somepage.html. Pls if u have code pls mail email address...
1
by: Jai | last post by:
Hi, Is it possibe to update a word document template using ASP.NET 2.0 Example: This is a word template. Employee Name: <Name> Employee No: <EmpNo>
6
by: sujana k | last post by:
how do i write a c program to search a word in a file....i know binary search is apt for it..if its a dictionary...how do we take the low value n high value..can somebody please help me out...
15
by: rashmiraj | last post by:
can anyone help me to write the code to get a field from database using combobox and that should display the corresponding record in datagrid using vb6.0
1
by: anilraja | last post by:
how to search within text using vbscript?
1
by: amiparna | last post by:
There is two frame in my form.In d first frame there is a text box,and i give some word in the text box.I want to search the word from a file from the location(http://45.12.09.31...........) and if...
2
by: Francesco Pietra | last post by:
Please, how to adapt the following script (to delete blank lines) to delete lines containing a specific word, or words? f=open("output.pdb", "r") for line in f: line=line.rstrip() if line:...
0
by: Francesco Pietra | last post by:
I forgot to add that the lines to strip are in present case of the type of the following block HETATM 7007 O WAT 446 27.622 34.356 55.205 1.00 0.00 O HETATM 7008 H1 WAT...
1
by: Ragavendran | last post by:
Hi, I am using this method for search: Query =org.apache.lucene.queryParser.QueryParser.parse(String arg0) throws ParseException Hits = org.apache.lucene.search.Searcher.search(Query query,...
5
by: nse111 | last post by:
Hey yaaaaaaa guys n gals! I want to know how I can match a whole word within a string using php. these are the results returned by the mysql query: tree:sweet:house:gate:tent...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.