Command button to find record

J

jojo

Is it possible to code a command button that is like the find/replace button
but with out the "replace"? Basically, I want the user to click on the
button and have it prompt to enter the ID# of the record. I created the
find/replace command button through the wizard, however I just want the user
to do a search on 1 specific field (Id#) and not allow them to search in any
other fields. Also, I do not want to give the user the option to "replace"
anything.

Thanks.
 
D

Dirk Goldgar

jojo said:
Is it possible to code a command button that is like the find/replace
button but with out the "replace"? Basically, I want the user to
click on the button and have it prompt to enter the ID# of the
record. I created the find/replace command button through the
wizard, however I just want the user to do a search on 1 specific
field (Id#) and not allow them to search in any other fields. Also,
I do not want to give the user the option to "replace" anything.
Click to expand...

Here's a very simplistic approach:

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
 
J

jojo

This works perfectly! I've been stuck on this for the whole day...Thank you
so much!!!

Dirk Goldgar said:
jojo said:
Is it possible to code a command button that is like the find/replace
button but with out the "replace"? Basically, I want the user to
click on the button and have it prompt to enter the ID# of the
record. I created the find/replace command button through the
wizard, however I just want the user to do a search on 1 specific
field (Id#) and not allow them to search in any other fields. Also,
I do not want to give the user the option to "replace" anything.
Click to expand...

Here's a very simplistic approach:

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

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
F

fredg

Is it possible to code a command button that is like the find/replace button
but with out the "replace"? Basically, I want the user to click on the
button and have it prompt to enter the ID# of the record. I created the
find/replace command button through the wizard, however I just want the user
to do a search on 1 specific field (Id#) and not allow them to search in any
other fields. Also, I do not want to give the user the option to "replace"
anything.

Thanks.
Click to expand...

Here are two very basic methods to find a particular record.

Me.Filter = "[IDField] = " & InputBox("What #")
Me.FilterOn = True

Or...
Me!ID.SetFocus
DoCmd.FindRecord InputBox(What #"), acEntire, False, acSearchAll, ,
acCurrent

It would be better to use a combo box with all the available ID#'s to
prevent miss-entries and to keep the user from having to remember each
ID#.

Me.Filter = "[IDField] = " & Me!ComboName
Me.FilterOn = True

Or...
Me!ID.SetFocus
DoCmd.FindRecord [ComboName], acEntire, False, acSearchAll, ,
acCurrent

All of the above examples assume the ID# is actually a Number
datatype, not Text.

You'll need to change the Allow Edits property to No along with each
of the above method:
Me.AllowEdits = False

Don't forget to turn AllowEdits on again when done.
Me.AllowEdits = True
 
I

iholder

jojo said:
This works perfectly! I've been stuck on this for the whole day...Thank you
so much!!!

Dirk Goldgar said:
jojo said:
Is it possible to code a command button that is like the find/replace
button but with out the "replace"? Basically, I want the user to
click on the button and have it prompt to enter the ID# of the
record. I created the find/replace command button through the
wizard, however I just want the user to do a search on 1 specific
field (Id#) and not allow them to search in any other fields. Also,
I do not want to give the user the option to "replace" anything.
Click to expand...

Here's a very simplistic approach:

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

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Click to expand...
 
I

iholder

This code work perfect with one exception. How can I trap if the user cancels
or leaves the input box empty.

I get an "mismatch type error"

Thanks

jojo said:
This works perfectly! I've been stuck on this for the whole day...Thank you
so much!!!

Dirk Goldgar said:
jojo said:
Is it possible to code a command button that is like the find/replace
button but with out the "replace"? Basically, I want the user to
click on the button and have it prompt to enter the ID# of the
record. I created the find/replace command button through the
wizard, however I just want the user to do a search on 1 specific
field (Id#) and not allow them to search in any other fields. Also,
I do not want to give the user the option to "replace" anything.
Click to expand...

Here's a very simplistic approach:

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

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Click to expand...
 
M

Mark M

You could try Dim'ing the variable as Variant instead of string so it could
hold anything.

Or you could add a little error-handler to trap the error and just exit
instead of giving you the "type mismatch" error.

Dim strFindID As String
On Error Goto Err_Handler
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

Exit_Here:
Exit Sub
Err_Handler:
Resume Exit_Here
End Sub
 
G

Gary B

This solution is also something I am trying to do. Would it work if the field
was a text field I was trying to Find?

G



Mark M said:
You could try Dim'ing the variable as Variant instead of string so it could
hold anything.

Or you could add a little error-handler to trap the error and just exit
instead of giving you the "type mismatch" error.

Dim strFindID As String
On Error Goto Err_Handler
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

Exit_Here:
Exit Sub
Err_Handler:
Resume Exit_Here
End Sub

iholder said:
This code work perfect with one exception. How can I trap if the user
cancels
or leaves the input box empty.

I get an "mismatch type error"

Thanks
Click to expand...
Click to expand...
 
G

Gary B

This is a neat Search function.

What would I need to change get it to seach over a Text field?



Dirk Goldgar said:
jojo said:
Is it possible to code a command button that is like the find/replace
button but with out the "replace"? Basically, I want the user to
click on the button and have it prompt to enter the ID# of the
record. I created the find/replace command button through the
wizard, however I just want the user to do a search on 1 specific
field (Id#) and not allow them to search in any other fields. Also,
I do not want to give the user the option to "replace" anything.
Click to expand...

Here's a very simplistic approach:

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

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Top