Find Method

[Mike]

I am trying to use the FindFirst to make sure I am not about to add a record
to my data table that will cause the program to crash (due to a repeated
primary key)

I can't get FindFirst to work with anything that isn't an object set as
Me.Recordset.Clone

The reason i need to do this is because i am using FindFirst to find a
record in the table that is Me.Recordset later on in the code so I cant just
change the table that is the record source for this form. Here is the code i
have so far


Dim db As Database
Dim rsData As DAO.Recordset
Dim jk As Object

Set db = CurrentDb
Set rsData = db.OpenRecordset("Data")

rsData.FindFirst "[Type] = '" & Me!Type_Select & "' & [Serial #] = '" &
Me!Serial_Number & "'"
If Not rsData.EOF Then

'The above code doesnt work. Error says: Run Time error '3251' Operation is
not supported for this type of object

Then the code does some stuff that works fine. Then:

Set jk = Me.Recordset.Clone

jk.FindFirst "[Type] = '" & Me!Type_Select & "'"
If Not jk.NoMatch Then

This FindFirst works just Fine.

I have tried DIMing something else as an object and then setting that as
rsData.Clone and then using FindFirst on that and i get the same Error.

Sorry that was such a long description. I hope someone has an answer. Thanks!

 

[Sam Davis]

Hi Mike,

I'm not entiorely sure why this doesn't work as I never use OpenRecordset
with just a table name. Try using an SQL query instead, that is....

Set rsData = db.OpenRecordset("SELECT * FROM Data")

and why use FindFirst at all? You could include your Where clause in the
SQL.

Set rsData = db.OpenRecordset("SELECT * FROM Data WHERE [Type]= ".... and so
on...

and then your check for an empty recordset...

HTH
Sam

 

[John Spencer]

You don't use the & to add more criteria. You use the AND operator.

rsData.FindFirst "[Type] = '" & Me!Type_Select & "' AND [Serial #] = '"
& Me!Serial_Number & "'"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Graham Mandeno]

Hi Mike

In addition to John's advice about the AND operator, if "Data" is a local
table (not a linked table) then OpenRecordset will open a "table-type"
recordset for which FindFirst is not valid.

You should open the recordset as a dynaset or snapshot (in this case, a
forward-only snapshot will have least overhead):

Set rsData = db.OpenRecordset("Data", dbOpenForwardOnly)

 

[Mike]

Thank you guys so much. I'm a mech engineering student doing some access work
for my internship. I'm really glad this is here to help me out because i
usually don't know what I'm doing. Thanks again.

-Mike

Hi Mike

In addition to John's advice about the AND operator, if "Data" is a local
table (not a linked table) then OpenRecordset will open a "table-type"
recordset for which FindFirst is not valid.

You should open the recordset as a dynaset or snapshot (in this case, a
forward-only snapshot will have least overhead):

Set rsData = db.OpenRecordset("Data", dbOpenForwardOnly)

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am trying to use the FindFirst to make sure I am not about to add a
record
to my data table that will cause the program to crash (due to a repeated
primary key)

I can't get FindFirst to work with anything that isn't an object set as
Me.Recordset.Clone

The reason i need to do this is because i am using FindFirst to find a
record in the table that is Me.Recordset later on in the code so I cant
just
change the table that is the record source for this form. Here is the code
i
have so far


Dim db As Database
Dim rsData As DAO.Recordset
Dim jk As Object

Set db = CurrentDb
Set rsData = db.OpenRecordset("Data")

rsData.FindFirst "[Type] = '" & Me!Type_Select & "' & [Serial #] = '" &
Me!Serial_Number & "'"
If Not rsData.EOF Then

'The above code doesnt work. Error says: Run Time error '3251' Operation
is
not supported for this type of object

Then the code does some stuff that works fine. Then:

Set jk = Me.Recordset.Clone

jk.FindFirst "[Type] = '" & Me!Type_Select & "'"
If Not jk.NoMatch Then

This FindFirst works just Fine.

I have tried DIMing something else as an object and then setting that as
rsData.Clone and then using FindFirst on that and i get the same Error.

Sorry that was such a long description. I hope someone has an answer.
Thanks!