Custom Search Box 2

[c2rich]

I am currently using the Find Record control Microsoft provides, however, I have users that find it difficult to use and wanted to know if there is any sample code i could build on. What I want to do is have a unbound text box with a search button next to it, so when the search criteria is entered (i.e. 555-555-5555) it will bring up the record containing it. Also, I am not sure if this is possible, but when you use the find record in the form it brings the record up right away as you are searching and gives the option to "Find Next." This may not be the best solution and maybe i should consider the time training my employees how to search using the function rather than this. By the way this is not an html based form but rather an Access application form. Any help would be appreciated. Thanks

 

[Ed2020]

I've never liked the Find Record box in Access. In my experience what users really want to do is *filter* for records that match a criteria.

Have a look at the Filter property of your form - this will allow you to specify criteria the the same syntax as an SQL WHERE clause. You could add a textbox to allow users to specify a value and a command button to change the filter property of the form:

Private Sub cmdApplyFilter_Click()
    Me.Filter = "FieldName = 'Search critera'"
    Me.FilterOn = True
End Sub

Please do not feed the trolls.....

 

[c2rich]

I can't believe it was that simple, I guess as a programmer sometimes you look to deep into things and it is just an eay fix.

-Kerry

Those that know, do. Those that understand, teach. -Aristotle

 

[c2rich]

I tried this but it is not doing exactly what i want it to, I do like the filter feature but is there a way to have a drop down field that shows the fields names (i.e last name, first name, etc) and then an unbound text box next to it that is blank? and when you select the field to search from the drop down and then enter the criteria in the unbound text box it filters within the same form?

-Kerry

Those that know, do. Those that understand, teach. -Aristotle

 

[Ed2020]

If you create an unbound combobox on your form and set its Row Source Type property to Field List and its Row Source to the name of the table/query you want to search this will provide you with the list of fields you need.

You can then have an Apply Filter command button with code along the following lines:

Private Sub Command7_Click()
    Me.Filter = Me.cboField.Value & " = " & """" & Me.txtCriteria.Value & """"
    Me.FilterOn = True
End Sub

Please do not feed the trolls.....

 

[dhookom]

Good code Ed2020, if there is a chance the field names might include spaces (shudder) then []s should be added. The code is for text fields. Most of the quotes would need to be removed for numeric fields.

Private Sub Command7_Click()
    Me.Filter = "[" & Me.cboField.Value & "] = " & """" & Me.txtCriteria.Value & """"
    Me.FilterOn = True
End Sub

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

Duane, what about something like this ?
Me.Filter = BuildCriteria(Me!cboField.Value, CurrentDb.TableDefs(Me!cboField.RowSource).Fields(Me!cboField.Value).Type, Me!txtCriteria.Value)

 

[dhookom]

I was trying to figure out an easy method for grabbing the field data type. Your suggestion is way cool.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Ed2020]

Is BuildCriteria new to VBA? I've never seen it before!

Ed Metcalfe.

Please do not feed the trolls.....

 

[dhookom]

I hadn't seen it before but it is in VBA Help (at least version 2003). Example from Help:

Sub SetFilter()
    Dim frm As Form, strMsg As String
    Dim strInput As String, strFilter As String

    ' Open Products form in Form view.
    DoCmd.OpenForm "Products"
    ' Return Form object variable pointing to Products form.
    Set frm = Forms!Products
    strMsg = "Enter one or more letters of product name " _
        & "followed by an asterisk."
    ' Prompt user for input.
    strInput = InputBox(strMsg)
    ' Build criteria string.
    strFilter = BuildCriteria("ProductName", dbText, strInput)
    ' Set Filter property to apply filter.
    frm.Filter = strFilter
    ' Set FilterOn property; form now shows filtered records.
    frm.FilterOn = True
End Sub

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Ed2020]

Very handy! Thanks Duane.

Ed Metcalfe.

Please do not feed the trolls.....