Recordset processing question

[mscertified]

Can I do the following or will it cause a problem?

I need to open a recordset on a table, then within the processing loop issue
UPDATE statements to the same table, e.g.

strSQL = "SELECT ID, ArtNumb, EffTarg, EffAct, GRRCHDate" & _
" FROM tblRpt5YrReviewProgress WHERE" & _
" (EffAct IS NOT NULL OR EffTarg IS NOT NULL)" & _
" ORDER BY ID"
rs1.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
strLastArt = vbNullString
While Not rs1.EOF
strArt = rs1.Fields("ArtNumb").Value
If strArt <> strLastArt Then
If strLastArt <> vbNullString Then
strSQL = "UPDATE tblRpt5YrReviewProgress SET GRRCHDAte = #" & _
dteGRRCH & "# WHERE ID = " & lngID
CurrentProject.Connection.Execute
End If
strLastArt = strArt
lngID = rs1.Fields("ID").Value
dteGRRCH = "1/1/1900"
End If
dteTarg = NZ(rs.Fields("EffTarg").Value, 0)
dteAct = NZ(rs.Fields("EffAct").Value, 0)
If dteAct > dteGRRCH Then
dteGRRCH = dteAct
End If
If dteTarg > dteGRRCH Then
dteGRRCH = dteTarg
End If
rs1.MoveNext
Wend
rs1.Close

 

[Dale Fye]

So, what you are trying to do is update the GRRCHDate field of one record
with the larger of the GRRCHDate, EffTarg, or EffAct from the record that
follows it, based on the ID field Order By clause.

I don't know why this technique wouldn't work, but I assume that since you
are asking, it is not working properly.

Are the GRRCHDate, EffTarg, and EffAct fields all date fields? If so, why
are you setting dteGRRCH equal to a string instead of a date? The problem
could be with comparing data of different types (strings and dates).

Since either EffAct or EffTarg could be null (at least your WHERE clause
implies this), your attempt to identify the larger of the values may be
failing and causing your problem, especially since you are comparing a
string, to a date or zero.

Try using the following function to get the maximum of your values.

Replace the If xxx>dteGRRCH statements with:

dteGRRCH = fnMaximum("1/1/1900", dteAct, dteTarg)

Since one of the values dteAct or dteTarg has to be non null, you might even
be able to just use:

dteGRRCH = fnMaximum(dteAct, dteTarg)

Or, get rid of these variables all together and try:

dteGRRCH = fnMaximum(rs.Fields("EffTarg").Value, rs.Fields("EffAct").Value)

HTH
Dale

Public Function fnMaximum(ParamArray MyArray() As Variant) As Variant

Dim myMax As Variant
Dim intLoop As Integer

For intLoop = LBound(MyArray) To UBound(MyArray)
If IsNull(MyArray(intLoop)) Then
'do nothing
ElseIf IsNull(myMax) Then
myMax = MyArray(intLoop)
ElseIf MyArray(intLoop) > myMax Then
myMax = MyArray(intLoop)
End If
Next
fnMaximum = myMax

End Function

 

[mscertified]

Thanks for the suggestions.
My current code (slightly modified from what I posted) is working now.
I was just concerned that updating a table while in a .movenext loop might
throw off the positioning but it seems it does not.

-Dorian

 

[Dale Fye]

Another option would be to use something like:

rs1.movePrevious 'takes you back to the previous record
rs1.edit
rs1("GRRCHDate") = dteGRRCH
rs1.Update
rs1.MoveNext 'gets you back to the record you were on
.... insert some code if you need it here
rs1.MoveNext 'moves to the next record

Dale