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 instructions.
3. In:
Code:
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,
MsgType,
UserID and
RcvID). If you want to move to the first record, use:
Code:
rstCurrentData.MoveFirst
4.
mtype, sid, vid are not declared anywhere (see above):
Code:
rstNew!MsgType = mtype
rstNew!SenderID = sid
rstNew!WordID = vid
and:
Code:
mtype = "None"
sid = "00"
vid = "A"
5. In lines such as:
Code:
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,
MsgType,
UserID and
RcvID.)
6. Though declared:
Code:
Dim rstNew As Recordset
rstNew 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:
Code:
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:
Code:
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 and
datEnd 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:
Code:
Do Until RecordSet.EOF
This at least garantees that the loop will never be executed if the recordset is empty (i.e. both .
BOF and .
EOF 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:
Code:
rstCurrentData2.MoveNext
then test the value of one of its fields:
Code:
If datTime < rstCurrentData2!Collectms Then
while
rstCurrentData2.EOF will be True (i.e. "No Current Record")
before the end of
rstCurrentData as
rstCurrentData2 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:
Code:
SELECT Tbl_Tally.SysCounter
, tblMaster_J.TStamp
, tblMaster_J.MsgType
, tblMaster_J.UserID
, tblMaster_J.RcvID
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.
dBforums
>
Compare data in two records
07-26-11, 15:18
Linear Mode
