Creating a search box in a form (Access 2003) HELP

[eternalearth]

Hello,
I have created a compuer inventory database for my company, and I have several hundred records. I was wondering if there is anyway to create a search box on the form where the user can pick any field given to them from a combo box and they would type in a string and the form would filter all the records that are relevant to that search.

Here is a copy of the database (totally blank but you can fill in your own stuff.)
searchdatabase.zip



Ideally, I would like the search box to be on the right side of the form, under where it says search.

I don't know much VBA code, so please be patient with me. I will do my best to work with you!

[June7]

Review this for one method of filtering form http://datapigtechnologies.com/flash...tomfilter.html

If you want one combobox to serve for search in any field, would require two unbound controls: Combobox to select the field and Textbox to enter the string. VBA could would construct the criteria string and set the form's filter property.

[eternalearth]

Would you be willing to show/tell me a step by step?

[June7]

For what - the one combobox idea?

1. Set the combobox RowSource and RowSourceType properties. If the list is exactly the field names, will be simpler.

2. Create textbox

3. Create a button to run the code, something like (assumes all fields are text type):
Me.FilterOn = False
Me.Filter = "[" & Me.comboboxname & "] = '" & Me.textboxname & "'"
Me.FilterOn = True

[eternalearth]

The video is great, however it's not quite what I am looking for. For example, the user wants to find a specific computer. They would go to the combo box and select "Comp-(Name)" and in the search box they would type in the computer name and then they would click search and it would be filtered/found.

[eternalearth]

And some fields are numeric as well...

[June7]

Does complicate but can be done. Code from my project:

Private Sub FindRecords()
Dim strsql As String
Dim strField1 As String, strField2 As String, strFor1 As String, strFor2 As String
Dim strField As String, strStart As String, strEnd As String
With Me
.grpFilter = Null
.lbxQueries = Null
strFor1 = Nz(.cbxFor1, "")
strFor2 = Nz(.cbxFor2, "")
strField1 = CustomQuerySettings(Nz(.cbxField1, ""))
strField2 = CustomQuerySettings(Nz(.cbxField2, ""))
strField = CustomQuerySettings(Nz(.cbxCategory, ""))
If strField = "LabNum" Then
    strStart = "'" & Nz(.tbxStart, "") & "'"
    strEnd = "'" & Nz(.tbxEnd, "") & "'"
Else
    strStart = "#" & Nz(.tbxStart, "") & "#"
    strEnd = "#" & Nz(.tbxEnd, "") & "#"
End If
strsql = IIf(IsNull(.cbxField1) Or (Not IsNull(.cbxField1) And IsNull(.cbxFor1)), "", strField1 & "='" & strFor1 & "'")
strsql = strsql & IIf(IsNull(.cbxField2) Or (Not IsNull(.cbxField2) And IsNull(.cbxFor2)), "", IIf(strsql = "", "", " AND ") & strField2 & "='" & strFor2 & "'")
strsql = strsql & IIf(IsNull(.cbxCategory) Or (Not IsNull(.cbxCategory) And (IsNull(.tbxStart) Or IsNull(.tbxEnd))), "", IIf(strsql = "", "", " AND ") & strField & " BETWEEN " & strStart & " AND " & strEnd)
strsql = strsql & IIf(IsNull(.cbxTest), "", IIf(strsql = "", "", " AND ") & "TestNum='" & .cbxTest & "'")
If strsql = "" Then
    MsgBox "Must select criteria!", vbOKOnly, "EntryError"
ElseIf Not IsNull(.cbxTest) Then
    .ctrSampleList.Form.RecordSource = "SELECT Submit.*, TestNum, StateNum, ProjectName, ProgCode, LedgerCode, Colo, FedNum, Metric, Remark1, Remark2, Remark3, Remark4, Remark5, Remark6 " & _
    "FROM ((Submit LEFT JOIN Projects ON Submit.ProjRecID = Projects.ProjRecID) LEFT JOIN Remarks ON Submit.LabNum = Remarks.LabNum) " & _
    "LEFT JOIN Tests ON Submit.LabNum = Tests.LabNum " & _
    "ORDER BY Submit.LabNum DESC;"
End If
.ctrSampleList.Form.Filter = strsql
.ctrSampleList.Form.FilterOn = True
.tbxLABNUM.SetFocus
End With
End Sub

My setup is to search for a specific text string and/or a range (lab number or date). If you want search on number fields as an option, the code will have to be structured to know 'the selected field is numeric so do search as number'. This could mean If Then Else or Select Case structures. I use combobox for selecting the search field and combobox for selecting the specific criteria. The criteria combobox RowSource is set by code in the field list combobox AfterUpdate event.

[eternalearth]

Does all this code apply strictly to the command button? AND how do I set up the combo box to only contain field names that will work with this code?

[June7]

I type a list in the combobox RowSource and set the RowSourceType property to ValueList.

The code I show is a sub that is called by the button Click event. Your code could be placed in the Click event.

[eternalearth]

But what list do I put in the combo box? Is it simply the field names I wish to have in my form?

[June7]

As stated in item 1 of post 4, that would be the simplest.

[eternalearth]

I will try this later tonight and get back to you! Thank you so far!

[eternalearth]

If I have multiple sources (mostly tables), for the Row Source, should I turn them into one Query so that it has all the options available?

[ssanfu]

Here is an example of a search form. You import the form into you project.
You could modify it to look at a table of only the tables/fields you want to make available to search.

Attached is the zip. I found it at "The Access Web" link >> http://access.mvps.org/access/resources/downloads.htm

[eternalearth]

Here is an example of a search form. You import the form into you project.
You could modify it to look at a table of only the tables/fields you want to make available to search.

Attached is the zip. I found it at "The Access Web" link >> http://access.mvps.org/access/resources/downloads.htm

This worked amazingly well! All I had to do was drag and drop it into my database and it started working instantaneously!

I am curious about one thing though, is there any way to get the box that has the list of tables and queries to only have the tables and queries I want to have show up?