Searching For Text in a Field - Combobox vs Textbox (1 Viewer)

[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.

I believe this is because the FISNumber field is my primary key and an autonumber and the txtSearchFISNum is a text field. I would prefer not to use a combo box because I will likely have several thousand FISNumbers in the future, and I do not want a dropdown of that many items.

 

[isladogs]

If it's a number, you need to change this line:

.FindFirst "[FISNumber] = '" & FISNumber & "'"

to

.FindFirst "[FISNumber] = " & FISNumber

BTW - you could just use code in the After_Update event of the textbox and scrap the button

 

[zashaikh]

Thank you for the assistance. But I tried that method as well, but still no luck

 

[MarkK]

I tried that method as well, but still no luck

Sorry, you tried what, and what happened?

 

[missinglinq]

It pretty much has to be a Number, since it's declared as a Long

[B]Public Sub GoToSearchFISNumb(FISNumber As Long)[/B]

If not, that may be the problem.

Since it's declared as a Public Sub, it would usually reside in a Standard Module, rather than in the Form's Module. Is that the case?

Linq ;0)>

 

[arnelgp]

On your second code you need to call the first code:


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

 

[zashaikh]

Thank you for the help everyone. This is the new code:

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()
Call GoToSearchFISNum(Me.txtSearchFISNum)
End Sub

I still recieve an error every time I press btnSearchFISNum. I am not being told "Compile Error: Sub or function not defined

And FISNumber is an autonumber and my primary key. txtSearchFISNum is a textbox where I enter the number I want to search (this is the FISNumber). btnSearchFISNum is the button that runs the code.

 

[isladogs]

Have you tried my suggestion from post #2?

you could just use code in the After_Update event of the textbox and scrap the button

 

[Minty]

There is a typo;

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


Private Sub btnSearchFISNum_Click()
    Call [COLOR="Red"]GoToSearchFISNum[/COLOR][COLOR="RoyalBlue"]b[/COLOR](Me.txtSearchFISNum)
End Sub

Missing the B at the end of the call

 

[zashaikh]

Ridders, I would prefer a button.

And Minty, THANK YOU! That typo fixed it!

 

[isladogs]

Fair enough.
Glad you (Minty really) got it working