Whats Wrong with My Code --- Searching For Text in a Field

[zashaikh]

I have a single form with 300 records. One the form I have a textbox called FISNumber where I record an identification number. I also have a textbox field called txtSearchFISNum and a button called btnSearchFISNum.



I want to be able to type in a number into txtSearchFISNum and press btnSearchFISNum, and then be taken to the exact record on my form where FISNumber = txtSearchFISNum.

This is what I am using:

Public Sub GoToSearchFISNumb(FISNumber As Long)
With Me.RecordsetClone
.FindFirst "[FISNumber] = '" & FISNumber & "'"
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With
End Sub


Private Sub btnSearchFISNum_Click()
Me.GoToSearchFISNum Me.txtSearchFISNum
End Sub

However, the code doesnt work and only brings up the debug menu which highlights the second code.

[Bulzie]

If numeric, maybe:

.FindFirst "[FISNumber] = " & FISNumber

[zashaikh]

I tried that as well. But I still get the same error.

[pbaldy]

You would normally call a sub/function like:

GoToSearchFISNum Me.txtSearchFISNum

[davegri]

Try it this way:

Public Function GoToSearchFISNumb(argFISNumber As Long)
    With Me.RecordsetClone
        .FindFirst "[FISNumber] = '" & argFISNumber & "'"
        If Not .NoMatch Then Me.Bookmark = .Bookmark
    End With
End Function

Private Sub btnSearchFISNum_Click()
    Call GoToSearchFISNumb(FISNumber)
End Sub

[zashaikh]

Thank you Davegri. I tried the code, but the debug menu came up and highlight
.FindFirst "[FISNumber] = '" & argFISNumber & "'"

[June7]

What is the exact error message?

Could put the function code directly in the Click event. Use a separate function if you need to call the same procedure from somewhere else.

Instead of textbox, why not a combobox so users can enter/select only valid data?

[davegri]

What is the recordsource of your form? If the form is not bound to a query or table, there is no RecordsetClone.

[zashaikh]

The Error:

Run-time error '3464' Data mismatch in criteria expression.

And I am basically using the textfield [txtSearchFISNum] and the button, btnSearchFISNum, to search through the 200+ records in my form so that txtSearchFISNum=FISNumber. FISNumber is the form bound to my table. txtSearchFISNum is unbound.

And yes. The form is bound to a table

[June7]

What is actually saved into FISNumber field? Is it a number - perhaps the primary key of lookup table? If that is the case then of course there is a data type conflict because you are entering a text value in the textbox. Using a multi-column combobox instead would eliminate this issue.

[zashaikh]

Ah. Then... that is likely the issue. I was unaware. The FISNumber field is my primary key and an autonumber.

And if I use a combobox, wont I end up with a dropdown list of 200+ numbers?

[June7]

Yes, but 200+ is not very many. Users can still type in the text part of the combobox and as they type will find match in the dropdown if there is one.

[zashaikh]

Is there a way to do it without a combobox?

And additionally, if I do use the combobox, I would need to set its record source to the FISNumber field in my table. Right?

Also, the 200 will quickly evolve into several thousand

[June7]

The search would have to compare to the text field that has the descriptive text.

Combobox has a ControlSource and RowSource, not a RecordSource.

Controls used to input search criteria should be UNBOUND. Review http://allenbrowne.com/ser-62.html


Did you create lookups in table? I NEVER do that. Most experienced developers here also recommend not to.

[zashaikh]

June7,

Sorry about that. I meant RowSource.

And the control for my search criteria is unbound.

I have created lookups in this table. But none of those lookups would apply to what Im trying to accomplish here (I think)