How do I find out why? Error 3251

L

.Len B

Here is a snippet of code which fails with error
3251: Operation is not supported for this type of object.

Set rstPay = db.OpenRecordset("Payments") 'open table 1 side
Set rstInv = db.OpenRecordset("Invoices") 'open table many side
rstPay.MoveFirst
rstInv.MoveFirst
With rstPay
Do Until .EOF
...
strRef = !Ref 'PK in Payments (same spelling FK in Invoices)
===> rstInv.FindFirst "Ref = " & strRef 'find first matching invoice
With rstInv
Do Until .EOF
...
Loop
End With
Loop
End With

I have used this construct to find the first match in a recordset many
times before but now I am at a loss to know where to look. Help says
method is usually valid but not in this case.

I have looked at help for OpenRecordset but this particular syntax is
not shown. I have to assume that the recordset has some default
attribute preventing finds but what is it? How do I find out?
(Access 2003)
 
R

Rob Parker

Hi Len,

Assuming that you've defined and set db as a database object (and I'd expect
an error earlier if you hadn't), the obvious thing is that you need to
delimit strRef (I'm assuming from its name that it's declared as a String
variable), thus:
rstInv.FindFirst "Ref = '" & strRef & "'"

If Ref in Payments table is not a string (perhaps an autonumber), then you
need to declare the variable to match the datatype, perhaps:
Dim lngRef as Long
...
lngRef = !Ref 'PK in Payments (same spelling FK in Invoices)
rstInv.FindFirst "Ref = " & lngRef 'find first matching
...

HTH,

Rob
 
L

.Len B

I cannot believe how stupid I am. The field Ref in both tables is Long
and here I am defining a string to refer to it !!! (AutoNum in Payments)
Set db was done earlier as were other Dims and assignments etc. eg
blnDisplayMsg = Me.chkDisplay

I changed to Dim lngRef as Long
and changed all occurrences of strRef to lngRef so that the code is as
you suggested. The code compiles without error.

HOWEVER, the error 3251 still persists.

--
Len
______________________________________________________
remove nothing for valid email address.
| Hi Len,
|
| Assuming that you've defined and set db as a database object (and I'd
expect
| an error earlier if you hadn't), the obvious thing is that you need to
| delimit strRef (I'm assuming from its name that it's declared as a
String
| variable), thus:
| rstInv.FindFirst "Ref = '" & strRef & "'"
|
| If Ref in Payments table is not a string (perhaps an autonumber), then
you
| need to declare the variable to match the datatype, perhaps:
| Dim lngRef as Long
| ...
| lngRef = !Ref 'PK in Payments (same spelling FK in Invoices)
| rstInv.FindFirst "Ref = " & lngRef 'find first matching
| ...
|
| HTH,
|
| Rob
|
|
| .Len B wrote:
| > Here is a snippet of code which fails with error
| > 3251: Operation is not supported for this type of object.
| >
| > Set rstPay = db.OpenRecordset("Payments") 'open table 1 side
| > Set rstInv = db.OpenRecordset("Invoices") 'open table many side
| > rstPay.MoveFirst
| > rstInv.MoveFirst
| > With rstPay
| > Do Until .EOF
| > ...
| > strRef = !Ref 'PK in Payments (same spelling FK in
Invoices)
| > ===> rstInv.FindFirst "Ref = " & strRef 'find first matching
| > invoice With rstInv
| > Do Until .EOF
| > ...
| > Loop
| > End With
| > Loop
| > End With
| >
| > I have used this construct to find the first match in a recordset
many
| > times before but now I am at a loss to know where to look. Help says
| > method is usually valid but not in this case.
| >
| > I have looked at help for OpenRecordset but this particular syntax is
| > not shown. I have to assume that the recordset has some default
| > attribute preventing finds but what is it? How do I find out?
| > (Access 2003)
|
|
 
T

Tokyo Alex

Hi Len,

If Payments and Invoices are tables in your database (not linked tables),
then OpenRecordset will default to opening a table-type recordset, which
doesn't support the Findxxxx methods.

You can either use the Seek method on the Recordset as is, or force Access
to open Dynaset recordsets thus:
Set rstPay = db.OpenRecordset("Payments", dbOpenDynaset) 'open table 1 side
Set rstInv = db.OpenRecordset("Invoices", dbOpenDynaset) 'open table many side

Caveat: Tested in A2007 only, as I don't have A2003.

Hope this helps,
Alex.
 
L

.Len B

I'm sorry Rob. I should have thanked you for your help.
My only excuse is that I was so overcome by the realisation
of my own stupidity that I forgot.

Thanks Rob.
--
Len
______________________________________________________
remove nothing for valid email address.
| Hi Len,
|
| Assuming that you've defined and set db as a database object (and I'd
expect
| an error earlier if you hadn't), the obvious thing is that you need to
| delimit strRef (I'm assuming from its name that it's declared as a
String
| variable), thus:
| rstInv.FindFirst "Ref = '" & strRef & "'"
|
| If Ref in Payments table is not a string (perhaps an autonumber), then
you
| need to declare the variable to match the datatype, perhaps:
| Dim lngRef as Long
| ...
| lngRef = !Ref 'PK in Payments (same spelling FK in Invoices)
| rstInv.FindFirst "Ref = " & lngRef 'find first matching
| ...
|
| HTH,
|
| Rob
|
|
| .Len B wrote:
| > Here is a snippet of code which fails with error
| > 3251: Operation is not supported for this type of object.
| >
| > Set rstPay = db.OpenRecordset("Payments") 'open table 1 side
| > Set rstInv = db.OpenRecordset("Invoices") 'open table many side
| > rstPay.MoveFirst
| > rstInv.MoveFirst
| > With rstPay
| > Do Until .EOF
| > ...
| > strRef = !Ref 'PK in Payments (same spelling FK in
Invoices)
| > ===> rstInv.FindFirst "Ref = " & strRef 'find first matching
| > invoice With rstInv
| > Do Until .EOF
| > ...
| > Loop
| > End With
| > Loop
| > End With
| >
| > I have used this construct to find the first match in a recordset
many
| > times before but now I am at a loss to know where to look. Help says
| > method is usually valid but not in this case.
| >
| > I have looked at help for OpenRecordset but this particular syntax is
| > not shown. I have to assume that the recordset has some default
| > attribute preventing finds but what is it? How do I find out?
| > (Access 2003)
|
|
 
L

.Len B

Thanks Alex,
That appears to have done the trick; at least until my stupidity
sabotages me again.
--
Len
______________________________________________________
remove nothing for valid email address.


| Hi Len,
|
| If Payments and Invoices are tables in your database (not linked
tables),
| then OpenRecordset will default to opening a table-type recordset,
which
| doesn't support the Findxxxx methods.
|
| You can either use the Seek method on the Recordset as is, or force
Access
| to open Dynaset recordsets thus:
| Set rstPay = db.OpenRecordset("Payments", dbOpenDynaset) 'open table 1
side
| Set rstInv = db.OpenRecordset("Invoices", dbOpenDynaset) 'open table
many side
|
| Caveat: Tested in A2007 only, as I don't have A2003.
|
| Hope this helps,
| Alex.
|
|
| ".Len B" wrote:
|
| > Here is a snippet of code which fails with error
| > 3251: Operation is not supported for this type of object.
| >
| > Set rstPay = db.OpenRecordset("Payments") 'open table 1 side
| > Set rstInv = db.OpenRecordset("Invoices") 'open table many side
| > rstPay.MoveFirst
| > rstInv.MoveFirst
| > With rstPay
| > Do Until .EOF
| > ...
| > strRef = !Ref 'PK in Payments (same spelling FK in
Invoices)
| > ===> rstInv.FindFirst "Ref = " & strRef 'find first matching
invoice
| > With rstInv
| > Do Until .EOF
| > ...
| > Loop
| > End With
| > Loop
| > End With
| >
| > I have used this construct to find the first match in a recordset
many
| > times before but now I am at a loss to know where to look. Help says
| > method is usually valid but not in this case.
| >
| > I have looked at help for OpenRecordset but this particular syntax is
| > not shown. I have to assume that the recordset has some default
| > attribute preventing finds but what is it? How do I find out?
| > (Access 2003)
| > --
| > Len
| > ______________________________________________________
| > remove nothing for valid email address.
| >
| >
| > .
| >
 
D

De Jager

.Len B said:
Here is a snippet of code which fails with error
3251: Operation is not supported for this type of object.

Set rstPay = db.OpenRecordset("Payments") 'open table 1 side
Set rstInv = db.OpenRecordset("Invoices") 'open table many side
rstPay.MoveFirst
rstInv.MoveFirst
With rstPay
Do Until .EOF
...
strRef = !Ref 'PK in Payments (same spelling FK in Invoices)
===> rstInv.FindFirst "Ref = " & strRef 'find first matching invoice
With rstInv
Do Until .EOF
...
Loop
End With
Loop
End With

I have used this construct to find the first match in a recordset many
times before but now I am at a loss to know where to look. Help says
method is usually valid but not in this case.

I have looked at help for OpenRecordset but this particular syntax is
not shown. I have to assume that the recordset has some default
attribute preventing finds but what is it? How do I find out?
(Access 2003)
Click to expand...
 

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

Cumulative formula for word table 0
How do I set focus (and select text) on a text box on a modeless userform? 0
How to modify and updte specific styles for specific paragraphs across the document 0
can't find strSQL 1
Email Invoice to Spefic Client 4
Sql to find record in a hierarchical chain 5
query on query 1
next without for error - stumped 3

Top