Form Causing RunTime error 94, Invalid use of null

[vba317]

I have created a form that gives mt this error 94 when I first open it and try and click on the list boxes. I have three listboxes that need to be clicked on to get the values. The error occurs when I click on the last listbox. If I click on debug and run the routine sometimes the procdedure works fine and sometimes I only get 3 updates instead of 4. I have highligthed in yellow where the error is. The last listbox called txtMove4Amt is on an enter event procedure. Can this be fixed? Any help is appreaciated.

 Dim txtPeriodName As String
    Dim frmMovePeriods As String
    Dim txtAmt As Double
    Dim SomeName As String
    Set db = CurrentDb()

    txtClntName = Me!txtClientName
    txtMoveName = Me!lstMove
 [Yellow]   txtAmt = Me!txtMove4Amt [/Yellow]
    If txtMoveName = "Plus" Then txtMove1 = "+"
    If txtMoveName = "Minus" Then txtMove1 = "-"
    txtMove = txtMove1 & txtAmt
    If txtMoveName = "Plus" And txtAmt = 1 Then txtMoveString = "Plus 1 Period"
    If txtMoveName = "Plus" And txtAmt > 1 Then txtMoveString = "Plus " & txtAmt & " Periods"
    If txtMoveName = "Minus" And txtAmt = 1 Then txtMoveString = "Minus 1 Period"
    If txtMoveName = "Minus" And txtAmt > 1 Then txtMoveString = "Minus " & txtAmt & " Periods"
'    If txtMoveName = "Minus" Then txtMoveString = "Plus 1 Period"
'    If txtMoveName = "p2" Then txtMove = "+2"
'    If txtMoveName = "p2" Then txtMoveString = "Plus 2 Periods"
'    If txtMoveName = "p3" Then txtMove = "+3"
'    If txtMoveName = "p3" Then txtMoveString = "Plus 3 Periods"
'    If txtMoveName = "m1" Then txtMove = "-1"
'    If txtMoveName = "m1" Then txtMoveString = "Minus 1 Period"
'    If txtMoveName = "m2" Then txtMove = "-2"
'    If txtMoveName = "m2" Then txtMoveString = "Minus 2 Periods"
'    If txtMoveName = "m3" Then txtMove = "-3"
'    If txtMoveName = "m3" Then txtMoveString = "Minus 3 Periods"

    strSQL = "UPDATE dbo_rpt_FYInfo " & _
             "SET dbo_rpt_FYInfo.rptpddiff = [rptpddiff]" & txtMove & _
             " WHERE dbo_rpt_FYInfo.uci='" & txtClntName & "';"
    Debug.Print strSQL

    db.Execute strSQL, dbFailOnError
    MsgBox "The FyInfo table has been updated " & txtMoveString

    strSQL = "UPDATE dbo_dic_Period_1 SET dbo_dic_Period_1.calpddiff = [calpddiff]" & txtMove & ";"
    db.Execute strSQL, dbFailOnError
    MsgBox "Period 1 table has been updated " & txtMoveString

    strSQL = "UPDATE dbo_dic_Period_2 SET dbo_dic_Period_2.calpddiff = [calpddiff]" & txtMove & ";"
    db.Execute strSQL, dbFailOnError
    MsgBox "Period 2 table has been updated " & txtMoveString

    strSQL = "UPDATE dbo_dic_Client_Period " & _
             "INNER JOIN dbo_rpt_Clients ON dbo_dic_Client_Period.clid = dbo_rpt_Clients.clntid " & _
             "SET dbo_dic_Client_Period.billpddiff = [billpddiff]" & txtMove & _
             " WHERE dbo_rpt_Clients.uci ='" & txtClntName & "';"
    db.Execute strSQL, dbFailOnError
    MsgBox "The Client table has been updated " & txtMoveString
    Set db = Nothing
End Sub

 

[lameid]

It sounds like txtMove4Amt is null when you enter it...

I would use IF and Isnull to test the value or NZ(txtMove4Amt,"") = ""

Also if you are doing something related to data in a control, generally you would use the after update event of that control not enter.

 

[Andrzejek]

You may also want to try:[tt]
txtAmt = Me!txtMove4Amt [blue]& ""[/blue][/tt]

or set txtMove4Amt field in your DB to not allow NULL

BTW - your pre-fixes are confusing:
Dim frmMovePeriods As String - looks like a Form to me, if it is a string, consider strMovePeriods (you don't use it anyway)
Dim txtAmt As Double - looks like text to me, if it is Double, consider dblAmt


Have fun.

---- Andy

 

[PHV]

I'd put something like this at the top of the procedure:
If IsNull(Me!txtMove4Amt) Then Exit Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886