Recordset problem

[Guest]

I'm trying to update a field in a table with a sequential number. Another
member of this group suggested the following code:

Public Function CalcCatNo() As String
Dim rs As dao.Recordset
Dim db As dao.recordset
Dim sql As String
Dim loopcounter As Long

Set db = CurrentDb()
sql = "SELECT Catalogue.* FROM Catalogue;"

Set rs = db.OpenRecordset(sql)
With rs
.MoveFirst
Do Until .EOF
loopcounter = loopcounter + 1
.Fields("CatalogueNo").Value = loopcounter
.MoveNext
Loop
End With

rs.Close
db.Close
rs = nothing
db= nothing

Firstly I have had to drop the dao in the declarations ie I have :
Dim db As Recordset
Dim rs As Recordset
(why did I need the DAO part is this a key to my problem?)
But now I am stuck at the line: Set rs = db.Openrecordset(sql) I get the
error:

Method or data member not found (Error 461)

Any ideas? Thanks

 

[George Nicholson]

1) Put DAO back in the Dim statements
2) In the Visual Basic Editor:Tools>References, then find "Microsoft DAO x.x
Object Library" and check it's box.

Try the code again.

 

[Guest]

Thanks George - I thought it was something like that and I had tried
"messing" with the libraries.

(of course now I have the answer I can see it has been answered several
times before - as they say it's easy when you know how!)

 

[Marshall Barton]

I'm trying to update a field in a table with a sequential number. Another
member of this group suggested the following code:

Public Function CalcCatNo() As String
Dim rs As dao.Recordset
Dim db As dao.recordset
Dim sql As String
Dim loopcounter As Long

Set db = CurrentDb()
sql = "SELECT Catalogue.* FROM Catalogue;"

Set rs = db.OpenRecordset(sql)
With rs
.MoveFirst
Do Until .EOF
loopcounter = loopcounter + 1
.Fields("CatalogueNo").Value = loopcounter
.MoveNext
Loop
End With

rs.Close
db.Close
rs = nothing
db= nothing

Firstly I have had to drop the dao in the declarations ie I have :
Dim db As Recordset
Dim rs As Recordset
(why did I need the DAO part is this a key to my problem?)
But now I am stuck at the line: Set rs = db.Openrecordset(sql) I get the
error:

Method or data member not found (Error 461)

In addition to your References problem, your code does not
actually change the value in the field. You need to use:
.Edit
.Fields("CatalogueNo").Value = loopcounter
.Update
to save the modified record.

 

[Guest]

In addition to your References problem, your code does not
actually change the value in the field. You need to use:
.Edit
.Fields("CatalogueNo").Value = loopcounter
.Update
to save the modified record.

Thanks Marsh - I actually managed to get to that by myself and was feeling
really great - until I then tried to run the code using a linked table query
instead of just on the table (the trouble is I need to do a fairly
complicated sort - requiring fields in 2 other tables before I number the
records.)

I think I read somewhere that reordset doesn't work on a linked table query
- is that right or should I persevere?????

 

[Marshall Barton]

Thanks Marsh - I actually managed to get to that by myself and was feeling
really great - until I then tried to run the code using a linked table query
instead of just on the table (the trouble is I need to do a fairly
complicated sort - requiring fields in 2 other tables before I number the
records.)

I think I read somewhere that reordset doesn't work on a linked table query
- is that right or should I persevere?????

Not right. You can not open a linked table using dbTable
type recordset, but you can't do this on a query either.
You can open a linked table or a query using dbDynaset,
which is the normal thing to do anyway.

The other thing you have to worry about is making sure that
the recordset is updatable. This means the recordset's
query can not use things like DISTINCT, GROUP BY, UNION,
etc.

 

[Guest]

In which case I will keep plugging away at it - I may be back here again with
another query.

Helen
(Living and Learning - with a little help )