search form

[ConfusedMay]

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.

[jyoung2]

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.

[ConfusedMay]

How can I do that? should I put that code on the search button?

[jyoung2]

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

[jaxjagfan]

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"

[ConfusedMay]

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?

[jyoung2]

In your query

Part one

Field    yourfildDesc
Criteria    LIKE *  & [forms]![YourFormName]![YourFieldName] & "*"
Or    LIKE *  & [forms]![YourFormName]![YourFieldName] & "*"

Part 2

yourtxtpart
LIKE *  & [forms]![YourFormName]![YourFieldName2] & "*"
 
LIKE *  & [forms]![YourFormName]![YourFieldName2] & "*"

Prt 3

[forms]![YourFormName]![YourFieldName]
not is null
NOT is null
 is null

prt4

[forms]![YourFormName]![YourFieldName2]
not is null
is null
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.

[NeoPa]

Check out Using "&" and "+" in WHERE Clause.

[ConfusedMay]

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:

Like "*" & [forms]![frmsearch]![txtid] & "*" or  Like "*" & [forms]![frmsearch]![txtid] & "*" 
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"

[NeoPa]

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.

[jaxjagfan]

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:

Like "*" & [forms]![frmsearch]![txtid] & "*" or  Like "*" & [forms]![frmsearch]![txtid] & "*" 
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

SELECT * FROM parts

The WHERE portion should look similar to below.

WHERE partid Like "*" & [forms]![frmsearch]![txtid] & "*"
 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:

Dim strSQL as String
Dim strCrit as String
Dim iNull as integer
 
strSQL = "Select * From tblParts"
 
If Me.opt1 > 1 Then
If isnull(Me.txtSearch)
Msgbox "You must enter search ctriteria!"
Me.txtSearch.Setfocus
Exit Sub
End If
End If
 
Select Case Me.opt1
Case 1
strCrit = ";"
Case 2
strCrit = " WHERE partid Like "*" & [forms]![frmsearch]![txtSearch] & "*" & ";"
Case 3
strCrit = " WHERE  description Like "*" & [forms]![frmsearch]![txtSearch]  & "*" & ";"
End Select
 
Me.RecordSource = strSQL & strCrit
 
 

Don't let us make this seem impossible with all of the code and SQL geek speak. :D