Help Adjusting the Update Piece of Access VBA Code
Hello Experts - The code below is working perfectly, however there has been a change in rules and an adjustment to the code is needed.
I need to adjust this With statement so that if there is only 1 record returned in the recordset, the bytPoints value for that record is 5 instead of 10.
I need to adjust this With statement so that if there is only 1 record returned in the recordset, the bytPoints value for that record is 5 instead of 10.
Dim bytPoints As Byte
bytPoints = 10
strSQL = strSQLbr1
With CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
Do Until .EOF
.Edit
If !RCER_TotalTime = 100 Then
!RCER_Points_AG = 0
.Update
End If
If !RCER_TotalTime < 100 Then
!RCER_Points_AG = bytPoints
.Update
End If
If bytPoints > 0 Then bytPoints = bytPoints - 1
.Edit: !RCER_Rank_AG = .AbsolutePosition + 1: .Update
.MoveNext
Loop
End WithASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<Why are you using the Byte datatype, instead of Integer?>
Perhaps because the value may never go over 255 and they don't need negatives...?
Perhaps because the value may never go over 255 and they don't need negatives...?
ASKER
fyed - testing now...
You might also consider:
Dim bytPoints As Byte
strSQL = strSQLbr1
With CurrentDb.OpenRecordset(st
if not .eof then
.movelast
bytPoints = iif(.RecordCount = 1, 5, 10)
.movefirst
endif
Do Until .EOF
.Edit
!RCER_Points_AG = iif(!RCER_TotalTime = 100, 0, bytPoints)
!RCER_Rank_AG = .AbsolutePosition + 1
.Update
If bytPoints > 0 Then bytPoints = bytPoints - 1
.MoveNext
Loop
End With
Dim bytPoints As Byte
strSQL = strSQLbr1
With CurrentDb.OpenRecordset(st
if not .eof then
.movelast
bytPoints = iif(.RecordCount = 1, 5, 10)
.movefirst
endif
Do Until .EOF
.Edit
!RCER_Points_AG = iif(!RCER_TotalTime = 100, 0, bytPoints)
!RCER_Rank_AG = .AbsolutePosition + 1
.Update
If bytPoints > 0 Then bytPoints = bytPoints - 1
.MoveNext
Loop
End With
ASKER
Thank you! This works great!
Personally, I would actually go with the 2nd version.
ASKER
https://www.experts-exchange.com/questions/27115570/Update-Table-Field-Counting-Backwards-from-10.html