Using a variable in Recordset.Find To lookup an ID in a table

[nouveauricheinvestments]

Hi,

I want to call this function to look and see if a record has been
added to a table when the user updates specific areas of the database
with a form to confirm the record has been added. What would be the
correct way to incorporate a variable in the Rs.Find
("RepID=MyVariable") ? Thanks for your input in advance.

Public Sub LookUp(ByVal RPID As Integer)

Dim Cn As ADODB.Connection
Set Cn = New ADODB.Connection
Dim Rs As ADODB.Recordset
Set Rs = New ADODB.Recordset
Dim WhatToFind As String


With Rs
.ActiveConnection = CurrentProject.Connection
.Open "SELECT * FROM [Pending Tickets]"
.Find "('RepID=" & RPID & "')"
End With
On Error GoTo Handle

MsgBox Rs.Fields(4)
Rs.Close
Set Cn = Nothing
Set Rs = Nothing


TimeToLeave:

Exit Sub

Handle:

Select Case Err.Number
Case Is = 3021
VBA.MsgBox "The request you have made to update the database
with a new record was not processed." & _
"Please attempt to enter the record again; if you continue
to get this error, please contact " & _
"your database administrator for assistance.", vbCritical,
"Error Adding Record"
Case Else
VBA.MsgBox "There was an unknown error when trying to process
your request in the database. Please " & _
"report the specific request you were attempting to make
in the database and forward that information" & _
"to your database administrator in conjunction with the
error number. " & vbCrLf & vbCrLf & Err.Number
End Select

GoTo TimeToLeave


End Sub

 

[Tom van Stiphout]

On Sun, 14 Dec 2008 11:25:16 -0800 (PST),
(e-mail address removed) wrote:

You were overthinking it. Here is the line to Find your integer:
..Find "RepID=" & RPID

A few comments about your code:
ByVal RPID As Integer: if RepID is an autonumber it is a long integer
and your code should say ByVal RPID As Long. Otherwise you'll get a
bad error after 2^16-1

MsgBox Rs.Fields(4): Much better to just return what you need, and to
test for the chance it was not found:
..Open "SELECT RepID FROM [Pending Tickets]"
..Find "RepID=" & RPID
if .NoMatch then
MsgBox "Alas, this record was not found
else
Msgbox "Hurray, the RepID was found:" & .Fields(0)
end if

If you actually want to return found or notfound to the caller of your
routine, you need:
Public Function LookUp(ByVal RPID As Integer) as Boolean
dim blnResult as boolean
....
blnResult = not .NoMatch
....
Lookup = blnResult
Exit Function
....

-Tom.
Microsoft Access MVP

 

[nouveauricheinvestments]

On Sun, 14 Dec 2008 11:25:16 -0800 (PST),

(e-mail address removed) wrote:

You were overthinking it. Here is the line to Find your integer:
.Find "RepID=" & RPID

A few comments about your code:
ByVal RPID As Integer: if RepID is an autonumber it is a long integer
and your code should say ByVal RPID As Long. Otherwise you'll get a
bad error after 2^16-1

MsgBox Rs.Fields(4): Much better to just return what you need, and to
test for the chance it was not found:
.Open "SELECT RepID FROM [Pending Tickets]"
.Find "RepID=" & RPID
if .NoMatch then
  MsgBox "Alas, this record was not found
else
  Msgbox "Hurray, the RepID was found:" & .Fields(0)
end if

If you actually want to return found or notfound to the caller of your
routine, you need:
Public Function LookUp(ByVal RPID As Integer) as Boolean
dim blnResult as boolean
...
blnResult = not .NoMatch
...
Lookup = blnResult
Exit Function
...

-Tom.
Microsoft Access MVP

Hi,

I want to call this function to look and see if a record has been
added to a table when the user updates specific areas of the database
with a form to confirm the record has been added.  What would be the
correct way to incorporate a variable in the Rs.Find
("RepID=MyVariable")  ?  Thanks for your input in advance.

Public Sub LookUp(ByVal RPID As Integer)

Dim Cn As ADODB.Connection
Set Cn = New ADODB.Connection
Dim Rs As ADODB.Recordset
Set Rs = New ADODB.Recordset
Dim WhatToFind As String

With Rs
   .ActiveConnection = CurrentProject.Connection
   .Open "SELECT * FROM [Pending Tickets]"
   .Find "('RepID=" & RPID & "')"
End With
On Error GoTo Handle

MsgBox Rs.Fields(4)
Rs.Close
Set Cn = Nothing
Set Rs = Nothing

Exit Sub

Select Case Err.Number
   Case Is = 3021
       VBA.MsgBox "The request you have made to update the database
with a new record was not processed." & _
           "Please attempt to enter the record again; if you continue
to get this error, please contact " & _
           "your database administrator for assistance.", vbCritical,
"Error Adding Record"
   Case Else
       VBA.MsgBox "There was an unknown error when trying to process
your request in the database.  Please " & _
           "report the specific request you were attemptingto make
in the database and forward that information" & _
           "to your database administrator in conjunction with the
error number. " & vbCrLf & vbCrLf & Err.Number
End Select

GoTo TimeToLeave

End Sub

Thanks Tom. I appreciate your help. I was just kind of winging it
with the code I sent you. I wasn't exactly sure how to even make it
work let alone the most efficient way to do it...