Command button to find record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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...
 
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
 
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...
 
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...
 
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
 
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...
 
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...
 
Back
Top