DAO Recordset Programming

[Guest]

Hello Everyone

I'm requesting help on creating a recordset that contains specific records. Once I have the records, I want to look at a specific field in a record and then look at the previous record and grab a value. I then want to go back to the original record and place that value in a field (write it to the record). I have some code I think might work, but I get an error when I run the code. The error tells me that I am missing a parameter-- and for the life of me, I can't figure out what I'm missin

Any help would be greatly appreciated

My code is as follows

Dim dbReadings As DAO.Databas
Dim rstReadings As DAO.Recordse

Dim strSQL As Strin
Dim strSerial As Strin
Dim strEquipment As Strin
Dim PrevNum As Integer, x As Intege
Dim CurNum As Intege
('used just to try different types of OpenRecordset items
strEquipment = Forms![frmEquipmentReadings]![EquipmentID
strSQL = "SELECT * From tblMeterReadings WHERE [EquipmentID] = Forms![frmEquipmentReadings]![EquipmentID]

Set dbReadings = CurrentDb(
Set rstReadings = dbReadings.OpenRecordset("qryTest"

x = DCount("*", "qryTest"

PrevNum =
CurNum =
'strEquipment = Forms![frmEquipmentReadings]![EquipmentID

If x > 0 The

'be sure you're at the last recor
rstReadings.MoveLas
rstReadings.MovePreviou
PrevNum = rstReadings![MeterReadingNew

FindReadingsNew = PrevNu
rstReadings.Edi

rstReadings.Updat
rstReadings.MoveNex


End I
rstReadings.Clos

'get next number & write it to the fiel

FindReadingsNew = PrevNu
Forms![frmEquipmentReadings]![frmMeterReadings SubForm].Form![MeterReadingOld] = FindReadingsNe

 

[TC]

(snip)

strSQL = "SELECT * From tblMeterReadings WHERE [EquipmentID] =

Forms![frmEquipmentReadings]![EquipmentID]"


Apart from anything else, that should be:

strSQL = "SELECT * From tblMeterReadings WHERE [EquipmentID] = """ &

Forms![frmEquipmentReadings]![EquipmentID] & """"

Do a debug.print or msgbox of the two different versions of strSQL, to see
the difference.

HTH,
TC

 

[Larry Linson]

To start with, I don't see an ORDER BY clause on the SQL you create and that
is the _only_ reliable way to assure the order of the records. Without it
"next" and "previous" records would not necessarily be what you expect to
see -- by definition, a relational table is unordered (though obviously, the
database engine must store them in _some_ order, but you should not make an
assumption about the order in which the db engine will keep the records).

And, it is not clear whether we are seeing the whole procedure or not.
Certainly, you need to set the option to break on all (or unhandled) errors
so you can tell us just which statement is indicated for the error you
describe.

Larry Linson
Microsoft Access MVP

Hello Everyone,

I'm requesting help on creating a recordset that contains specific

records. Once I have the records, I want to look at a specific field in a
record and then look at the previous record and grab a value. I then want
to go back to the original record and place that value in a field (write it
to the record). I have some code I think might work, but I get an error
when I run the code. The error tells me that I am missing a parameter-- and
for the life of me, I can't figure out what I'm missing

Any help would be greatly appreciated.

My code is as follows:

Dim dbReadings As DAO.Database
Dim rstReadings As DAO.Recordset

Dim strSQL As String
Dim strSerial As String
Dim strEquipment As String
Dim PrevNum As Integer, x As Integer
Dim CurNum As Integer
('used just to try different types of OpenRecordset items)
strEquipment = Forms![frmEquipmentReadings]![EquipmentID]
strSQL = "SELECT * From tblMeterReadings WHERE [EquipmentID] = Forms![frmEquipmentReadings]![EquipmentID]"

Set dbReadings = CurrentDb()
Set rstReadings = dbReadings.OpenRecordset("qryTest")


x = DCount("*", "qryTest")


PrevNum = 0
CurNum = 0
'strEquipment = Forms![frmEquipmentReadings]![EquipmentID]


If x > 0 Then

'be sure you're at the last record
rstReadings.MoveLast
rstReadings.MovePrevious
PrevNum = rstReadings![MeterReadingNew]

FindReadingsNew = PrevNum
rstReadings.Edit

rstReadings.Update
rstReadings.MoveNext


End If
rstReadings.Close

'get next number & write it to the field

FindReadingsNew = PrevNum
Forms![frmEquipmentReadings]![frmMeterReadings

SubForm].Form![MeterReadingOld] = FindReadingsNew