FindFirst Method

[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?

 

[Dirk Goldgar]

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

 

[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

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)

 

[Dirk Goldgar]

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.