Opening a Recordset

[Roderick O'Regan]

I have a form open ("ordernew") based on a table ("order"). I now
press a Cancel command button halfway through entering data on the
form.

Behind the Cancel button I have the following code which closes the
form without saving. It is then supposed to open the table called
"Codes" to reduce the order number by one.

Dim db As Database
Dim rs As Recordset
Dim pcodevalue As Integer

Set db = CurrentDb
DoCmd.Close acForm, "orderNew", acSaveNo
pcodevalue = DLookup("[Last_Nbr_Assigned]", "Codes",
"[Last_Nbr_Assigned]>999")
Set rs = db.OpenRecordset("Codes", dbOpenTable)
rs.Fields("Last_Nbr_Assigned") = pcodevalue - 1
rs.Update
rs.Close

However, I get a Type Mismatch on this line of code:
Set rs = db.OpenRecordset("Codes", dbOpenTable)

I don't understand why this is happening.

Can anyone help, please?

Roderick

 

[fgernhardt]

OpenRecordset

Roderick,

Here is a section of code that works for me. I use DAO.Recordset rather than recordset - maybe that is the problem:
'Using DAO objects, read the records
Dim dbs As Database
Dim rsTable As DAO.Recordset
Dim RecordsRead As Integer

RecordsRead = 0
Set dbs = CurrentDb
'Open the table
Set rsTable = dbs.OpenRecordset("Product_csv", dbOpenTable)
'Cycle Through the Table
With rsTable
Do While RecordsRead < NumRecords
ProductNo = !ProductNo
RecordsRead = RecordsRead + 1
.MoveNext
Loop
End With
rsTable.Close

Another thought.
Have you thought about putting your process within a transaction?
See BeginTrans, CommitTrans, RollBackTrans in Access Help.

Have a good day!
Faith