RecordsetClone.FindFirst causes error

  • Thread starter Thread starter Peter Row
  • Start date Start date
P

Peter Row

Hi,

I have a main form, I have a button that opens an "add" form. A new record
is added, I requery the main form.
I then try to use :

Me.RecordsetClone.FindFirst "PersonID = " & CStr(lngID)
If Not Me.RecordsetClone.NoMatch Then
Me.Recordset.Bookmark = Me.RecordsetClone.Bookmark
End If

However I get a "object does not support method" error on the
RecordsetClone.FindFirst line.
I am using the syntax from the Access XP SP2 help file so why it doesn't
work I don't know, ideas?

Regards,
Peter
 
Since CStr(lngID) is a string you need to include single quotes around it in
your criteria.

Me.RecordsetClone.FindFirst "PersonID = '" & CStr(lngID) & "'"

Kelvin Lu
 
Hi,

For my style of coding I don't let variables get implicitly converted from
one type to another.
The first argument of the FindFirst method is a STRING that indicates what
field to search and for what value.

As the variable name lngID suggests it is actually a Long. Therefore to
avoid implicit conversion from Long to
String I do the conversion explicitly. The database (SQL Server) field is of
type Int (equivalent to VBA Long)
therefore quotes around the ID value I am concatenating are not needed.

The error "object does not support method or property" means that Access
thinks that
Me.RecordsetClone has no FindFirst method. However from searching Google and
looking
in the help it seems to be used all the time therefore the method does
exist, but why Access
can't find the method on the RecordsetClone object in this case I don't
know, which is what I'm asking.

Now, sorry to be harsh, but can I have some help from a real Access
Programmer and not
a hobbyist home user?

NOTE: I don't use Access normal, I prefer a really language like VB/.NET,
but due to time restrictions
I had no choice but to use Access as the frontend, (ADP so thankfully I can
use a SQL Server).

Regards,
Peter
 
Hi,

Just in case any one else is in the same situation and can't see the wood
for the trees. I now know why it doesn't work.

I am writing an ADP therefore it is using ADO and not DAO of which FindFirst
is exclusive to.
I can't simply reference the DAO library and use that because I'm doing an
ADP, therefore the
recordset object represented by RecordsetClone is an ADO recordset.

Damn! Back to the drawing board to try and get around the find problem.

Regards,
Peter
 
Peter, I know I am not an expert but when your use the CStr function the
result is of type String. Whenever you pass a string as a criteria you have
to have quotes. Right? If PersonID is an integer type then you should be
using CInt and not CStr.

Kelvin Lu
 
Hi,

No you are wrong.

The first parameter of the FindFirst method - Criteria - is a string.
The value for "criteria" in this case is being set to:

PersonID = XXX

where XXX is the long variable I convert to string because the Criteria
parameter is suppose to be a string

You are trying to tell me that I should format the Criteria parameter so
that it reads:

PersonID = "XXX"

This criteria says - "look for data where the PersonID field is equal to the
string value XXX" - converted from my variable.
BUT !!! .... in the SQL Server database the PersonID field has the datatype
Int (equivalent to VBA Long)
therefore your way will cause an error.

I thought I explained it pretty well the first time. But just to lay it down
again...

The FindFirst parameter "Criteria" is a STRING but the value of it is passed
to the database like a
WHERE clause so when I have form bound to a table, i.e tPerson and I say

Me.RecordsetClone.FindFirst "PersonID = " & CStr(lngPersonID)

... and lngPersonID's value is 1023 for example

The database is in affect receiving:
SELECT * FROM tPerson WHERE PersonID = 1023

No quotes are needed because PersonID in tPerson is a SQL Server Int.

If you really can't see what I'm saying after the above then you should stop
programming
immediately and go on some sort of programming for beginners course.

What I have described is basic data type stuff that you would probably learn
on a college level
course. VBA is not very strict in that it will implicitly convert variables
from one type to another
, i.e if you say:
Msgbox "This is my integer: " & MyIntegerVariable

The above is saying concatenate this string with this integer variable
Behind the scenes VBA will convert the integer variable to a string.

Although this works it is bad programming practice to allow this sort
of implicit conversion because depending on which data types are being
converted between
could cause data loss or unexpected results.

Regards,
Peter

However you converting the type of the FindFirst "criteria" parameter with
the type
 
Back
Top