FindFirst Method

W

Wavequation

I am trying to run the following code:
Dim intCompNum

Dim db as DAO.Database
Dim rsStock As DAO.Recordset

Set db = CurrentDb
Set rsStock = db.OpenRecordset("Tbl_Location")

intCompNum = 546
strCriteria = "[Component] = " & intCompNum ("Component" is a field in
Tbl_Location)

rsStock.MoveFirst
rsStock.FindFirst(strCriteria)

at the last line, VBA returns Run-time error '3251':
Operation is not supported for this type of object.

What am I doing wrong?
 
D

Dirk Goldgar

Wavequation said:
I am trying to run the following code:
Dim intCompNum

Dim db as DAO.Database
Dim rsStock As DAO.Recordset

Set db = CurrentDb
Set rsStock = db.OpenRecordset("Tbl_Location")

intCompNum = 546
strCriteria = "[Component] = " & intCompNum ("Component" is a field in
Tbl_Location)

rsStock.MoveFirst

You don't need the MoveFirst.
rsStock.FindFirst(strCriteria)

at the last line, VBA returns Run-time error '3251':
Operation is not supported for this type of object.

What am I doing wrong?

Because you are opening your recordset directly on a table, by default you
will get a table-type recordset back, and you that type of recordset doesn't
support the FindFirst method. Try specifying a dynaset instead:

Set rsStock = db.OpenRecordset("Tbl_Location", dbOpenDynaset)

intCompNum = 546
strCriteria = "[Component] = " & intCompNum

rsStock.FindFirst strCriteria
 
D

DaveT

I see intCompNum dim'd as variant (by default), does this eliminate the need
to use CStr such as:

strCriteria = "[Component] = " & CStr(intCompNum)

--
Dave Thompson
Allen, TX
US


Dirk Goldgar said:
Wavequation said:
I am trying to run the following code:
Dim intCompNum

Dim db as DAO.Database
Dim rsStock As DAO.Recordset

Set db = CurrentDb
Set rsStock = db.OpenRecordset("Tbl_Location")

intCompNum = 546
strCriteria = "[Component] = " & intCompNum ("Component" is a field in
Tbl_Location)

rsStock.MoveFirst

You don't need the MoveFirst.
rsStock.FindFirst(strCriteria)

at the last line, VBA returns Run-time error '3251':
Operation is not supported for this type of object.

What am I doing wrong?

Because you are opening your recordset directly on a table, by default you
will get a table-type recordset back, and you that type of recordset doesn't
support the FindFirst method. Try specifying a dynaset instead:

Set rsStock = db.OpenRecordset("Tbl_Location", dbOpenDynaset)

intCompNum = 546
strCriteria = "[Component] = " & intCompNum

rsStock.FindFirst strCriteria


--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)
 
D

Dirk Goldgar

DaveT said:
I see intCompNum dim'd as variant (by default), does this eliminate the
need
to use CStr such as:

strCriteria = "[Component] = " & CStr(intCompNum)

Whether intCompNum is declared as a Variant or as an Integer (as the name
implies) is irrelevant to this. There is no real need to use CStr() anyway.
All it does here is make explicit what is going to happen anyway when you
use the concatenation operator: whatever type of value is in intCompNum is
going to be converted to String, if it isn't already a String, in order to
concatenate it to the string literal "[Component] = ".

If VB didn't do implicit type conversions, one would have to make an
explicit conversion such as seen above using CStr(). Some people argue that
all type conversions should be explicitly specified that way, as a matter of
programming style. I don't see the point, since the behavior of VB in
expressions like this is well-defined.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

FindFirst 3
data type mismatch 4
Issue with Linked Table 1
Find Method 4
VSTO C# add in Outlook.Appointment error 0
OpenRecordset not recognising .FindFirst 2
strCriteria declaration 2
Holiday day tracking 2

Top