Ms Access Error:Run-time error ’94’: Invalid use of Null 

Background: In the recordset update form execution the “Ms Access Error:Run-time error ’94’: Invalid use of Null ”

Workaround:

Generally this error shows up when a variable’s or a control’s in an operation has no value at all i.e NULL. Upon further investigation it seemed that the “Me.txtamount” has no value as in some stage the command DoCmd.GoToRecord , , acNewRec resetting the value it contains.

sql007 = "SELECT nz(outs,0) AS OutsAmt, outs, uptime FROM SUPPLIERS WHERE [suppid] = """ & suppidVar & """" 
Set rs007 = CurrentDb.OpenRecordset(sql007, dbOpenDynaset, dbSeeChanges)
rs007.Edit
rs007!outs = CDbl(rs007!OutsAmt) + CDbl(Me.txtamount)

Resolution of Ms Access Error:Run-time error ’94’:

As the value of “Me.txtamount” had inserted into database table by the immediate previous operation in the process, hence the value will be populated in a variable from the database table by DLookup opeartion and will be replaced in the place of “Me.txtamount”. In the below code the variable expenseAmt has been replaced in the place of “Me.txtamount”.

Dim expenseAmt As Long 
expenseAmt = DLookup("amount", "EXPENSES", "[eid] = " & eidVar)

sql007 = "SELECT nz(outs,0) AS OutsAmt, outs, uptime FROM SUPPLIERS WHERE [suppid] = """ & suppidVar & """" 
Set rs007 = CurrentDb.OpenRecordset(sql007, dbOpenDynaset, dbSeeChanges) 
rs007.Edit
rs007!outs = CDbl(rs007!OutsAmt) + CDbl(expenseAmt) 

Leave a Reply

Your email address will not be published. Required fields are marked *