Command button to find record

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jojo - 17 Jan 2005 23:11 GMT

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.

Reply to this Message

Dirk Goldgar - 17 Jan 2005 23:22 GMT

> 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
[quoted text clipped - 3 lines]
> 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.

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

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

jojo - 17 Jan 2005 23:35 GMT

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

> > 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
[quoted text clipped - 22 lines]
> Msgbox "Please enter a valid ID#!"
> End If

Reply to this Message

iholder - 02 Apr 2005 16:25 GMT

> This works perfectly! I've been stuck on this for the whole day...Thank you
> so much!!!
[quoted text clipped - 25 lines]
> > Msgbox "Please enter a valid ID#!"
> > End If

Reply to this Message

iholder - 02 Apr 2005 16:35 GMT

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

> This works perfectly! I've been stuck on this for the whole day...Thank you
> so much!!!
[quoted text clipped - 25 lines]
> > Msgbox "Please enter a valid ID#!"
> > End If

Reply to this Message

Mark M - 03 Apr 2005 03:21 GMT

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 code work perfect with one exception. How can I trap if the user
> cancels
[quoted text clipped - 3 lines]
>
> Thanks

Reply to this Message

Gary B - 15 Mar 2007 15:58 GMT

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

> You could try Dim'ing the variable as Variant instead of string so it could
> hold anything.
[quoted text clipped - 31 lines]
> >
> > Thanks

Reply to this Message

Gary B - 19 Mar 2007 13:43 GMT

This is a neat Search function.

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

Reply to this Message

fredg - 18 Jan 2005 00:46 GMT

> 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
[quoted text clipped - 5 lines]
>
> Thanks.

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

Signature

Fred
Please only reply to this newsgroup.
I do not reply to personal email.

Reply to this Message