That should not be the only error:
1. rstCurrentData is not declared anywhere.
2. rstCurrentData2 is not declared anywhere.
Note: If you do not have the line:
in the Declarations section of a module, all variables that are not explicitly declared (i.e. using a Dim
instruction) are of type Variant
, except if you use Deftype
rstCurrentData.FindFirst (RowID = 1)
the criteria should be a litteral (i.e. "RowID = 1"
) but it makes no sense since there is no field named RowID
into the recordset (in a previous post you wrote that the columns of the table were: TStamp
). If you want to move to the first record, use:
4. mtype, sid, vid
are not declared anywhere (see above):
rstNew!MsgType = mtype
rstNew!SenderID = sid
rstNew!WordID = vid
mtype = "None"
sid = "00"
vid = "A"
5. In lines such as:
If rstCurrentData!Collectms <= rstCurrentData2!Collectms Then
where does Collectms
come from? (in a previous post you wrote that the columns of the table were: TStamp
6. Though declared:
Dim rstNew As Recordset
is never initialized before you use it in the line:
At that stage in the code rstNew is nothing (i.e. rstNew Is Nothing = True
) as there is no previous line to open the RecordSet, such as:
rstNew = CurrentDb.OpenRecordset(...
This is the meaning of the error you describe. Moreover the type of RecordSet (ADO or DAO) is not specified: this can be a problem (not always) as the default object type can vary according to the version of Access and/or to the ranking order of the references in your project.
7. I'm not sure of what the lines:
datStart = DFirst("MinOfCollectms", "MJ_Min_Max")
datEnd = DFirst("MaxOfCollectms", "MJ_Min_Max")
actually mean, partly because I don't know what MJ_Min_Max
is (a query, I guess, but with which SQL statement?). However I'm rather sure that using the values datStart
together with datTime
to control the iterations of the loop is not wise nor logical or safe. Not wise because you don't now in advance what values they can have. Not logical because when you have to process a recordset from the first to the last line, the normal (and logical) method consists in using an instruction such as:
Do Until RecordSet.EOF
This at least garantees that the loop will never be executed if the recordset is empty (i.e. both .BOF
are True). Moreover the whole recordset will always be processed. Accessorily the logic behind the code is easier to understand. Not safe because you cannot assert that the loop will not run past the last record of the recordset. This is because you control the iterations of the loop with values extracted from the data set processed inside it, while you never test for .EOF = True
8. An other issue should arise because you use:
then test the value of one of its fields:
If datTime < rstCurrentData2!Collectms Then
will be True (i.e. "No Current Record") before
the end of rstCurrentData
is always one record ahead
IMHO it would be more efficient to use one or possibly two queries to perform this kind of operation. Consider the following SQL statement:
FROM tblMaster_J RIGHT JOIN Tbl_Tally
ON tblMaster_J.TStamp = Tbl_Tally.SysCounter
WHERE (Tbl_Tally.SysCounter <= (SELECT MAX(TStamp) FROM tblMaster_J));
Where Tbl_Tally is a tally table (i.e. a table with a single column (here named SysCounter) containing adjacent increasing integer values: 1, 2, 3... etc. This query yields a result such as the one you can see in the attached screenshot and does most of the work. Using it into a RecordSet loop, or better in a second UPDATE query (if possible) would complete the columns from tblMaster_J where the values are Null. Removing the duplicate values from the data set would not be difficult either.