Searching

[Cider]

I recently had help creating a search box using a combo. Essentially you type in your number, pick from one of the three drop down opitions to search and then click Find. The form then populates with that record. Two of the three drop downs search fine but my first option always returns an error. It is my primary key and I assume it is having an issue because its an auto-number. I need that field to stay an auto-number so is there a way to get around that and be able to search it?




Thanks in advance!

[Bulzie]

so does it search on multiple combo boxes or just 1 at a time? Can you give a sample of your data and search options and what it is not doing for you.

[Cider]

I use the combo box to pick which field to search. The user types in the number they which to search, then picks from a drop down which field to search in, and then click find. Here is my code:

Private Sub cmdFind_Click()
If IsNull(Me.txtFind) Then
Me.FilterOn = False
Else
Me.Filter = "[" & cboFld & "]='" & txtFind & "'"
Me.FilterOn = True
End If
End Sub


So the user can't search all three fields, they have to pick one. My records can have three unique identifiers and one is an auto generated number from Access (in case I don't have the other two identifiers.) and I need to be able to pull a record up by searching that auto number. The error I'm getting is: "Run-time error '3464': Data type mismatch in criteria expression". My two other plain text fields search just fine.

[Bulzie]

The autonumber is not text but numeric. If they pick that option, you need Me.Filter = "[" & cboFld & "]=" & txtFind

Are all 3 combo boxes visible on form?

[Cider]

I'm not sure what you mean..Sorry - I'm super new to access

It is one combo box with a drop down with 3 options.

[Bulzie]

Ok, so maybe:

If IsNull(Me.txtFind) Then

Me.FilterOn = False

Else

If Me.cboFld = 1 then 'for the autonumber field

Me.Filter = "[" & cboFld & "]=" & txtFind

Else

Me.Filter = "[" & cboFld & "]='" & txtFind & "'"

End If
Me.FilterOn = True

End If

Again if I am understanding it right, if you are searching the autonumber field, that is a numeric type so has different syntax then searching a text field.

[Cider]

I still get the same error

[orange]

Perhaps you could show us a picture of the form --I'm not following your description.

[Bulzie]

Give an example of the process using actual data. So you pull down the combobox list and select the first option which is the number 1? Then you have a text field called txtfind and in this you type in what value?

[Cider]

This is what the form looks like with the search box filled in


Here is the error I'm getting when I search "SBARNumber"


Here is what is supposed to happen (it works with the other two drop down items):




"SBARNumber" is an auto generated number and on this form it shows up with "new" in the box. I have another form that is for new records and need that field to autopopulate that number. That info is then dropped into a table. The form in the pictures is used to search the info in the table in a form view so the user may make edits.

I can try and send my database if that helps?

[Bulzie]

I think this should work. When searching the SBARNumber column, you have to search as numeric I believe.

If IsNull(Me.txtFind) Then

Me.FilterOn = False

Else

If Me.cboFld = "SBARNumber" then

Me.Filter = "[" & cboFld & "]=" & txtFind

Else

Me.Filter = "[" & cboFld & "]='" & txtFind & "'"

End If
Me.FilterOn = True

End If

[Cider]

That worked!!! Thank you so much!

[Bulzie]

Glad to help!!