Delete records module - runtime error94?

[KevinSlater]

Hi,
i have the below code in a Access 2003 module, but when i run the code i get the following error message: runtime error: 94, invalid use of Null, and when i click debug it highlights this line: x = x + rs(fArr(i))

Any suggestions on how to fix this would be great?.

Sub delZ()
Dim rs As DAO.Recordset, j As Integer, x As Long, fArr(), i As Integer
Set rs = CurrentDb.OpenRecordset("DATA-FORECAST_UPDATE")
rs.MoveFirst
i = 0
For j = 0 To rs.Fields.Count - 1
If InStr(rs(j).Name, "_QTY") > 0 Or InStr(rs(j).Name, "_VALUE") > 0 _
Or InStr(rs(j).Name, "PROMO_PRICE") > 0 Or InStr(rs(j).Name, "ACT") > 0 Or InStr(rs(j).Name, "BSALES") > 0 Then
ReDim Preserve fArr(i)
fArr(i) = rs(j).Name
i = i + 1
End If
Next
Do Until rs.EOF
x = 0
For i = 0 To UBound(fArr)
x = x + rs(fArr(i))
Next
If x = 0 Then rs.Delete
rs.MoveNext
Loop
rs.Close
End Sub


want im wanting to do is look for the follwing field names: FORECAST_1_BASE_QTY, FORECAST_1_BASE_VALUE, FORECAST_1_PROMO_QTY, FORECAST_1_PROMO_VALUE, PD1ACTVAL, BSALES1, PD1ACT in a table and should all of these fileds be empty or 0 on a record then that record should be deleted but only if all fields are empty, if one field has data then the record should not be deleted.

 

[ajetrumpet]

Some notes...

runtime error: 94, invalid use of Null, and when i click debug it highlights this line: x = x + rs(fArr(i))

Any suggestions on how to fix this would be great?.

Sub delZ()
Dim rs As DAO.Recordset, j As Integer, x As Long, fArr(), i As Integer
Set rs = CurrentDb.OpenRecordset("DATA-FORECAST_UPDATE")
rs.MoveFirst
i = 0
For j = 0 To rs.Fields.Count - 1
If InStr(rs(j).Name, "_QTY") > 0 Or InStr(rs(j).Name, "_VALUE") > 0 _
Or InStr(rs(j).Name, "PROMO_PRICE") > 0 Or InStr(rs(j).Name, "ACT") > 0 Or InStr(rs(j).Name, "BSALES") > 0 Then
ReDim Preserve fArr(i)
fArr(i) = rs(j).Name <--- we are storing STRINGS in the array elements here, but only if the condition above is TRUE. If it's FALSE, that element is probably NULL (I think!)
i = i + 1
End If
Next
Do Until rs.EOF
x = 0
For i = 0 To UBound(fArr) <--- here we start from 0, which is the LBound, unless the Option Base has been changed to 1, which I assume it hasn't been, looking at the start of the loop counter.
x = x + rs(fArr(i)) <--- what happens here if the Array called "fArr(0)" ended up being skipped in the above conditional LOOP? Result of NULL I wonder?
Next
If x = 0 Then rs.Delete
rs.MoveNext
Loop
rs.Close
End Sub

 

[KevinSlater]

Hi,

Apologies about the late reply,

i changed x = x + rs(fArr(i))

to

x = x + nz(rs(fArr(i)),0) and it works fine

thanks for your comments