Search Box

[Gary B]

Don't know anything about coding but I found this piece of code which finds a
numeric record in a field. This works great but I would like to use it to
return a text record in a field.

Dim strFindID As String

strFindID = InputBox("Enter the ID# you're looking for:")

If IsNumeric(strFindID) Then
With Me.RecordsetClone
.FindFirst "[ID#]=" & strFindID
If .NoMatch Then
MsgBox "Sorry, couldn't find that ID#."
Else
Me.Bookmark = .Bookmark
End If
End With
Else
Msgbox "Please enter a valid ID#!"
End If

Is it a simple change?
Gary

 

[Allen Browne]

For a Text field, you need extra quotes in this line:
.FindFirst "[ID#]=" & strFindID
i.e.:
.FindFirst "[ID#]=""" & strFindID & """"

Replace [ID#] with the name of your Text field.

If you are not a coder, you might enjoy this:
Find as you type - Filter forms with each keystroke
at:
http://allenbrowne.com/AppFindAsUType.html
It has a combo box where you choose the field to search in, and a text box
where you type the value to find. You can use it on any form by copying the
combo and text box, and setting one property.

 

[Gary B]

Nice one

Thanks

For a Text field, you need extra quotes in this line:
.FindFirst "[ID#]=" & strFindID
i.e.:
.FindFirst "[ID#]=""" & strFindID & """"

Replace [ID#] with the name of your Text field.

If you are not a coder, you might enjoy this:
Find as you type - Filter forms with each keystroke
at:
http://allenbrowne.com/AppFindAsUType.html
It has a combo box where you choose the field to search in, and a text box
where you type the value to find. You can use it on any form by copying the
combo and text box, and setting one property.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Don't know anything about coding but I found this piece of code which
finds a
numeric record in a field. This works great but I would like to use it to
return a text record in a field.

Dim strFindID As String

strFindID = InputBox("Enter the ID# you're looking for:")

If IsNumeric(strFindID) Then
With Me.RecordsetClone
.FindFirst "[ID#]=" & strFindID
If .NoMatch Then
MsgBox "Sorry, couldn't find that ID#."
Else
Me.Bookmark = .Bookmark
End If
End With
Else
Msgbox "Please enter a valid ID#!"
End If

Is it a simple change?
Gary

 

[Gary B]

Allen,

I thought I had this working.

The text field which I was searching over contains letters and numbers e.g.
BA1066
It keeps telling me to enter a valid ID# when I enter BA1066.

The Data Type of the ID# is set to text.

Can you help
Ta

For a Text field, you need extra quotes in this line:
.FindFirst "[ID#]=" & strFindID
i.e.:
.FindFirst "[ID#]=""" & strFindID & """"

Replace [ID#] with the name of your Text field.

If you are not a coder, you might enjoy this:
Find as you type - Filter forms with each keystroke
at:
http://allenbrowne.com/AppFindAsUType.html
It has a combo box where you choose the field to search in, and a text box
where you type the value to find. You can use it on any form by copying the
combo and text box, and setting one property.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Don't know anything about coding but I found this piece of code which
finds a
numeric record in a field. This works great but I would like to use it to
return a text record in a field.

Dim strFindID As String

strFindID = InputBox("Enter the ID# you're looking for:")

If IsNumeric(strFindID) Then
With Me.RecordsetClone
.FindFirst "[ID#]=" & strFindID
If .NoMatch Then
MsgBox "Sorry, couldn't find that ID#."
Else
Me.Bookmark = .Bookmark
End If
End With
Else
Msgbox "Please enter a valid ID#!"
End If

Is it a simple change?
Gary

 

[Allen Browne]

Okay, so it must handle text, not just numbers:

Dim strFindID As String

strFindID = Trim$(InputBox("Enter the ID# you're looking for:"))

If strFindID <> vbNullString Then
With Me.RecordsetClone
.FindFirst "[ID#] = """ & strFindID & """"
If .NoMatch Then
MsgBox "Sorry, couldn't find that ID#."
Else
Me.Bookmark = .Bookmark
End If
End With
Else
Msgbox "Please enter a valid ID#!"
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I thought I had this working.

The text field which I was searching over contains letters and numbers
e.g.
BA1066
It keeps telling me to enter a valid ID# when I enter BA1066.

The Data Type of the ID# is set to text.

Can you help
Ta

For a Text field, you need extra quotes in this line:
.FindFirst "[ID#]=" & strFindID
i.e.:
.FindFirst "[ID#]=""" & strFindID & """"

Replace [ID#] with the name of your Text field.

If you are not a coder, you might enjoy this:
Find as you type - Filter forms with each keystroke
at:
http://allenbrowne.com/AppFindAsUType.html
It has a combo box where you choose the field to search in, and a text
box
where you type the value to find. You can use it on any form by copying
the
combo and text box, and setting one property.

Don't know anything about coding but I found this piece of code which
finds a
numeric record in a field. This works great but I would like to use it
to
return a text record in a field.

Dim strFindID As String

strFindID = InputBox("Enter the ID# you're looking for:")

If IsNumeric(strFindID) Then
With Me.RecordsetClone
.FindFirst "[ID#]=" & strFindID
If .NoMatch Then
MsgBox "Sorry, couldn't find that ID#."
Else
Me.Bookmark = .Bookmark
End If
End With
Else
Msgbox "Please enter a valid ID#!"
End If

Is it a simple change?
Gary

 

[NEWER USER]

I copied your code for the AsYouType function - VERY Impressive

When you view the Value List in Filter the field, the sort order is showing
based on my Tab Control sort. Can I change this to Alpha sort? If the
RecordSource was a Table/Query, I can control the sort order. How or what
code do I modify where your property shows Value list? I first saw this
feature in Access 2007 and wished I had it in 2003 - now I do. Thanks so much.

Okay, so it must handle text, not just numbers:

Dim strFindID As String

strFindID = Trim$(InputBox("Enter the ID# you're looking for:"))

If strFindID <> vbNullString Then
With Me.RecordsetClone
.FindFirst "[ID#] = """ & strFindID & """"
If .NoMatch Then
MsgBox "Sorry, couldn't find that ID#."
Else
Me.Bookmark = .Bookmark
End If
End With
Else
Msgbox "Please enter a valid ID#!"
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I thought I had this working.

The text field which I was searching over contains letters and numbers
e.g.
BA1066
It keeps telling me to enter a valid ID# when I enter BA1066.

The Data Type of the ID# is set to text.

Can you help
Ta

For a Text field, you need extra quotes in this line:
.FindFirst "[ID#]=" & strFindID
i.e.:
.FindFirst "[ID#]=""" & strFindID & """"

Replace [ID#] with the name of your Text field.

If you are not a coder, you might enjoy this:
Find as you type - Filter forms with each keystroke
at:
http://allenbrowne.com/AppFindAsUType.html
It has a combo box where you choose the field to search in, and a text
box
where you type the value to find. You can use it on any form by copying
the
combo and text box, and setting one property.

Don't know anything about coding but I found this piece of code which
finds a
numeric record in a field. This works great but I would like to use it
to
return a text record in a field.

Dim strFindID As String

strFindID = InputBox("Enter the ID# you're looking for:")

If IsNumeric(strFindID) Then
With Me.RecordsetClone
.FindFirst "[ID#]=" & strFindID
If .NoMatch Then
MsgBox "Sorry, couldn't find that ID#."
Else
Me.Bookmark = .Bookmark
End If
End With
Else
Msgbox "Please enter a valid ID#!"
End If

Is it a simple change?
Gary