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

search form

P: 57
Hi,

I'm kinda stuck right now with one of my forms and wondering if someone could help me with this. Basically what I need to create is a search form for users to be able to search using part Id or part Description. for example if user type in 10* in the part ID text box, then it should show all parts with part ID started with 10 in a data grid on the same form
I have a form with txtpartID, txtDescription and search button. My question is how can I display the results? do I need to create subform for it? I'm using access 2003. Your help in this is very much appreciated.
Jan 21 '08 #1
Share this Question
Share on Google+
10 Replies

P: 32
Yes you should use a subform
If you are creating a seperate search form with "txtpartID, txtDescription and search button" then you can have your query look to txtpartID and txtDescription and have the button call "me.subformname.requery" and it will display the results.
Jan 21 '08 #2

P: 57
How can I do that? should I put that code on the search button?
Jan 21 '08 #3

P: 32
Create a query in design mode,
you place [forms]![YourFormName]![YourFieldName] into the criteria fo your query

requery the subform with your button on click event using this code.

me.subformname.requery
Jan 21 '08 #4

jaxjagfan
Expert 100+
P: 254
In the criteria of your query put something like this

Like "*" & [Forms]![frmSearch]![txtVIN] & "*"

This is a quote from something I do here - modify to suit your needs.

You no longer have to add the *. It is assumed. And this criteria would return all with a 10 anywhere in the partnumber whether beginning, middle, or end.

You can do this via an unbound textbox and a command button. If the form is based on the query with the criteria above then all the command button needs for code is

Me.Requery

You would enter a value and click the button.

Wildcards like you are using have to use the word "Like"
Jan 21 '08 #5

P: 57
In the criteria of your query put something like this

Like "*" & [Forms]![frmSearch]![txtVIN] & "*"

This is a quote from something I do here - modify to suit your needs.

You no longer have to add the *. It is assumed. And this criteria would return all with a 10 anywhere in the partnumber whether beginning, middle, or end.

You can do this via an unbound textbox and a command button. If the form is based on the query with the criteria above then all the command button needs for code is

Me.Requery

You would enter a value and click the button.

Wildcards like you are using have to use the word "Like"
Thank you for the reply and I'm almost there. The only problem I have right now is if there's a part ID start with 10 and there's no description for that part, it won't show up on the search. I need for users to be able to search by part id or description. Is there a way for access to show all data with part id 10 no matter if the description is null or show all part id if the description is null?
Jan 22 '08 #6

P: 32
In your query

Part one
Expand|Select|Wrap|Line Numbers
  1. Field    yourfildDesc
  2. Criteria    LIKE *  & [forms]![YourFormName]![YourFieldName] & "*"
  3. Or    LIKE *  & [forms]![YourFormName]![YourFieldName] & "*"
Part 2
Expand|Select|Wrap|Line Numbers
  1. yourtxtpart
  2. LIKE *  & [forms]![YourFormName]![YourFieldName2] & "*"
  3.  
  4. LIKE *  & [forms]![YourFormName]![YourFieldName2] & "*"
Prt 3
Expand|Select|Wrap|Line Numbers
  1. [forms]![YourFormName]![YourFieldName]
  2. not is null
  3. NOT is null
  4.  is null
prt4
Expand|Select|Wrap|Line Numbers
  1. [forms]![YourFormName]![YourFieldName2]
  2. not is null
  3. is null
  4. Not is null
take each part an put them into the query
The logic is if a field is null it ignores it for the query if both fields have data both feilds are used if only 1 has data it ignores the other

Place them side by side and it makes more sense if this doesn't help I can try to explain it better or I could maybe send a screen shot but it would not paste.
Jan 22 '08 #7

NeoPa
Expert Mod 15k+
P: 31,770

P: 57
Check out Using "&" and "+" in WHERE Clause.
Thank you for all your help. but the code that you gave me is still not working (probably I'm missing something) Here is what I did: On the query (that showed all the data on the subform) on the partID criteria:
Expand|Select|Wrap|Line Numbers
  1. Like "*" & [forms]![frmsearch]![txtid] & "*" or  Like "*" & [forms]![frmsearch]![txtid] & "*" 
  2. and on the description criteria, I put:  Like "*" & [forms]![frmsearch]![txtdesc] & "*" or  Like "*" & [forms]![frmsearch]![txtdesc] & "*"
but I don't really know where I should put the code (part 3 and part 4). Is it on the search button? because my search button code is DoCmd.Requery "search subform"
Jan 22 '08 #9

NeoPa
Expert Mod 15k+
P: 31,770
You've quoted my post, but I suspect from what you've posted yourself that you haven't checked it out yet.

I'm afraid jyoung2's suggestion is not quite right (although they're trying to be helpful so I have no complaint with that). Any strings in SQL should be enclosed in quotes ('). See Quotes (') and Double-Quotes (") - Where and When to use them.

NB. If you're creating a string to pass to SQL you need to remember that the VBA will understand some of the quotes (") and pass on the others to SQL. For a string value in SQL you need to make sure that the SQL gets the string WITH the SQL quotes in.
Jan 22 '08 #10

jaxjagfan
Expert 100+
P: 254
Thank you for all your help. but the code that you gave me is still not working (probably I'm missing something) Here is what I did: On the query (that showed all the data on the subform) on the partID criteria:
Expand|Select|Wrap|Line Numbers
  1. Like "*" & [forms]![frmsearch]![txtid] & "*" or  Like "*" & [forms]![frmsearch]![txtid] & "*" 
  2. and on the description criteria, I put:  Like "*" & [forms]![frmsearch]![txtdesc] & "*" or  Like "*" & [forms]![frmsearch]![txtdesc] & "*"
but I don't really know where I should put the code (part 3 and part 4). Is it on the search button? because my search button code is DoCmd.Requery "search subform"
Make sure the the criteria is entered on seperate lines (if on the same line as each other AND is implied. if on different rows then OR is implied)

Switch to SQL View, copy and paste your query for us to see pls.

The SELECT portion should be similar to
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM parts
The WHERE portion should look similar to below.
Expand|Select|Wrap|Line Numbers
  1. WHERE partid Like "*" & [forms]![frmsearch]![txtid] & "*"
  2.  OR description Like "*" & [forms]![frmsearch]![txtdesc] & "*" 
As previously posted you will have to learn to handle Null or no entries.

This gets a little more complicated if your users have multiple fields to choose from. 99% of the time a user knows a partial partnbr or partial part description and would want to search by one or the other.

The complication is on you, the developer. You may need to set the datasource of the form via code using SQL statements now. You need to decide if you want to give the user the option to search by multiple fields or only one at a time.

Insert an "Option Group" in your search form. Option 1 could be show all number. Option 2 could be part number. Option 3 could be part description, etc. You could have a lot of options. Give the "Option Group" control a good name - opt1 - Access calls it Frame0 if the first frame or option group.

You only need one Textbox for search criteria - i called it txtSearch.

Within the Code for the search button:

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL as String
  2. Dim strCrit as String
  3. Dim iNull as integer
  4.  
  5. strSQL = "Select * From tblParts"
  6.  
  7. If Me.opt1 > 1 Then
  8. If isnull(Me.txtSearch)
  9. Msgbox "You must enter search ctriteria!"
  10. Me.txtSearch.Setfocus
  11. Exit Sub
  12. End If
  13. End If
  14.  
  15. Select Case Me.opt1
  16. Case 1
  17. strCrit = ";"
  18. Case 2
  19. strCrit = " WHERE partid Like "*" & [forms]![frmsearch]![txtSearch] & "*" & ";"
  20. Case 3
  21. strCrit = " WHERE  description Like "*" & [forms]![frmsearch]![txtSearch]  & "*" & ";"
  22. End Select
  23.  
  24. Me.RecordSource = strSQL & strCrit
  25.  
  26.  
Don't let us make this seem impossible with all of the code and SQL geek speak. :D
Jan 22 '08 #11

Post your reply

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