how can i search records with a text box?

J

jjsaw5

Hello,

what im looking to do is have a text box where the user can enter a number (
number is based off a spread sheet, which is already in a table )

then i want the user to click a "search" button and have the record brought
up...

any help would be great



thank you!
Justin
 
K

Klatuu

Why a text box? Combo boxes are much better at this. The actual look up is
the same code, but the combo gives you some advantages in handling a search
and provides the user with "type ahead", so they can see the results as they
type.

In either case, you use the control's After Update event to find the record
and make it the currrent record:

Dim rst As Recordset

Set rst = Me.RecordsetClone
With rst
.FindFirst "[SomeField] = " & Me.txtNumber
If .NoMatch Then
Me.txtNumber.Undo
MsgBox "Not Found"
Else
Me.BookMark = .BookMark
End If
End With

If you use a combo, leave out the two lines for the .NoMatch true branch.
The Not In List event handles this.
 
J

jjsaw5

I agree with you 100% that a combo box is much better, and that is what i
originally used. But after presenting this to the users, they asked for a
text box and a "search" button to execute the search after they entered what
they were searching for.

I'll give this a shot and see how it turns out


thanks so much for all your help!
Why a text box? Combo boxes are much better at this. The actual look up is
the same code, but the combo gives you some advantages in handling a search
and provides the user with "type ahead", so they can see the results as they
type.

In either case, you use the control's After Update event to find the record
and make it the currrent record:

Dim rst As Recordset

Set rst = Me.RecordsetClone
With rst
.FindFirst "[SomeField] = " & Me.txtNumber
If .NoMatch Then
Me.txtNumber.Undo
MsgBox "Not Found"
Else
Me.BookMark = .BookMark
End If
End With

If you use a combo, leave out the two lines for the .NoMatch true branch.
The Not In List event handles this.
Hello,
Click to expand...
[quoted text clipped - 10 lines]
thank you!
Justin
Click to expand...
Click to expand...
 
Top