473,320 Members | 1,987 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,320 software developers and data experts.

adding search to my form in access

i have more that one button that i am trying to search different things. here is my first button which is my search button for the sku

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearchSku_Click()
  2.  
  3.     Dim bkmk As Variant
  4.     Dim strField As String
  5.  
  6.     Me.RecordsetClone.MoveFirst
  7.  
  8.        Me.RecordsetClone.FindFirst "[SKU] Like " & Chr(34) & Me.txtSearchSku & "*" & Chr(34)
  9.  
  10.  
  11.  
  12.     If Me.RecordsetClone.NoMatch Then
  13.         msgBox "No Match"
  14.     Else
  15.         bkmk = Me.RecordsetClone.Bookmark
  16.         Me.Recordset.Bookmark = bkmk
  17.     End If
  18.  
  19.  
i want to add another search button for the description and another one for code. so three in total. when i add the same code to the search by description button, it just come back no records found, when i type in exactly what i am looking for, and the same for nmfc code. i tried to make the variable a bkmk1 and bkmk2, but then it gives me an error! please help!
Jun 15 '15 #1

✓ answered by Seth Schrock

Please use [CODE/] tags when posting code, per site rules.

You appear to be trying to do what is on the MSDN website: Form.RecordsetClone Property

Their example is like this:
Expand|Select|Wrap|Line Numbers
  1. Sub SupplierID_AfterUpdate() 
  2.     Dim rst As Recordset 
  3.     Dim strSearchName As String 
  4.  
  5.     Set rst = Me.RecordsetClone 
  6.     strSearchName = Str(Me!SupplierID) 
  7.     rst.FindFirst "SupplierID = " & strSearchName 
  8.         If rst.NoMatch Then 
  9.             MsgBox "Record not found" 
  10.         Else 
  11.             Me.Bookmark = rst.Bookmark 
  12.         End If 
  13.     rst.Close 
  14. End Sub
You should only have to change lines 6 and 7 to fit your criteria and then it would work on your form.

9 1519
Seth Schrock
2,965 Expert 2GB
Please use [CODE/] tags when posting code, per site rules.

You appear to be trying to do what is on the MSDN website: Form.RecordsetClone Property

Their example is like this:
Expand|Select|Wrap|Line Numbers
  1. Sub SupplierID_AfterUpdate() 
  2.     Dim rst As Recordset 
  3.     Dim strSearchName As String 
  4.  
  5.     Set rst = Me.RecordsetClone 
  6.     strSearchName = Str(Me!SupplierID) 
  7.     rst.FindFirst "SupplierID = " & strSearchName 
  8.         If rst.NoMatch Then 
  9.             MsgBox "Record not found" 
  10.         Else 
  11.             Me.Bookmark = rst.Bookmark 
  12.         End If 
  13.     rst.Close 
  14. End Sub
You should only have to change lines 6 and 7 to fit your criteria and then it would work on your form.
Jun 15 '15 #2
why is this saying after update?
Expand|Select|Wrap|Line Numbers
  1. Private Sub buttonSearchDesc_Click()
  2. Dim search As Recordset
  3. Dim txtSearchDesc As String
  4.  
  5. Set search = Me.RecordsetClone
  6. txtSearchDesc = Str(Me!description)
  7. search.FindFirst "Description" = " & txtSearchDesc"
  8.  
  9. If search.NoMatch Then
  10.     msgBox "record not found"
  11.     Else
  12.     Me.Bookmark = search.Bookmark
  13.     End If
  14.  
  15.     search.Close
  16.  
the txtsearchdesc is my textbox and description is what i am trying to search for. it is not working. the error is 'type mismatch'.
Jun 15 '15 #3
Seth Schrock
2,965 Expert 2GB
For the code tags to work, remove the / in the first one. You can also use the [CODE/] button to add the tags for you.

Line 7 should be
Expand|Select|Wrap|Line Numbers
  1. search.FindFirst "Description = '" & Me.txtSearchDesc & "'"
Jun 15 '15 #4
it is saying type mismatch and then highlighting this line

Expand|Select|Wrap|Line Numbers
  1. txtSearchDesc = Str(Me!description)
Jun 15 '15 #5
Seth Schrock
2,965 Expert 2GB
Try just removing that line.
Jun 15 '15 #6
okay!! thanks so much!! i got that part to working!!! life savor!!
Jun 15 '15 #7
Seth Schrock
2,965 Expert 2GB
Great! Glad I could help. Good luck on your project.
Jun 15 '15 #8
oh an i do have another question for you. when you pull up the search, it just pulls up the first one it finds with that match, that is when i used the find first field. is there anyway to make it show me all of the records with that code? and separate those out.
Jun 15 '15 #9
NeoPa
32,556 Expert Mod 16PB
Hi Lilly.

It sounds like you're looking for a filtering solution rather than a search/find one. Very similar in concept but coded quite differently.

The good news for you is that filtering is mostly handled automatically by Access so much less complicated to code. Have a look at Cascaded Form Filtering or Example Filtering on a Form for help on that.

NB. Pay special attention to how differently numeric, date and string literal values are used in the filter string. For strings, such as you're likely to find in a [Description] field, you want single-quotes (') around the value.
Jun 15 '15 #10

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

Similar topics

6
by: Jules | last post by:
Hi: I have an Access 97 Search form set up - a couple of combo boxes, a couple of text fields and a command button. I want the command button to run an SQL script and then open the results form....
0
by: _TeCk_ | last post by:
Do anyone know the name of the control : Search in Access. I want to know when the user leave the search form to refresh a subform. Thank's!
9
by: lightning | last post by:
Hi all, I'm not very conversant with the vocabulary of Access, so please ask for clarification if necessary... I am trying to build a search form that allows combinations of search terms. For...
1
by: hottoku | last post by:
Hi All, I'm having quite a bit of trouble designing a search tool to work with my database. I have found lots of examples from Microsoft Templates to Allen Browne's sample search form. The...
1
by: tamoochin | last post by:
I have a form that registers the user with my website, the form is in farsi language and must use utf-8 standard. I can store data in MS Access and also read it back with any problems. the...
2
by: Mark | last post by:
Hi All, I am creating a music database for a friend and have run into a problem. Within the ALBUM table, I wanted to store the ARTIST_ID rather than the ARTIST_NAME. To do this, I intended to have...
2
by: woodey2002 | last post by:
Hi Guys and thanks for your time. I have a search form for my database that allows users to select multiple criteria from multi select list boxes. I successfully integrated a multi select...
9
f430
by: f430 | last post by:
i have been trying to write a search code for a similar database, and i followed all the steps that were provided above, and my code was close to what lightning had but i have added date range in my...
1
by: lorax | last post by:
I am new to this. I am recreating a form from ACCESS 2000 in Access 2010. The problem is that ACCESS no longer allow one to close a form without saving it. Form: a search form that allows people...
9
by: ittechguy | last post by:
I have a unbound form which contains no data. I need to populate the form and also 3 listboxes with data using a search form which contains several search boxes in the header of my form. My idea...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
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: 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...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.