Error "Invalid use of Null" in a Function called from a Form_Current event

[Richard1980]

Hello there,
I am struggling trying to execute a function inside a Form_current event to display some stats.

The Function is this:

Function FlightsByAircraft(Aircraft As Long) As Long
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim str As String
str = "SELECT * FROM tblFlights WHERE AircraftID = " & Aircraft
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(str)
    If rst.RecordCount = 0 Then
        FlightsByAircraft = 0
    Else
    rst.MoveLast
    FlightsByAircraft = rst.RecordCount
    End If
rst.Close
Set dbs = Nothing
Set rst = Nothing
End Function

The code for the Form_Current event is this:

Private Sub Form_Current()
txtStats1 = FlightsByAircraft(Me.AircraftID)

Very simple.
Well, the problem is when I move to a new record, a error message comes up: "Run-time error '94' - Invalid use of Null".
It is because the AircraftID is not populated at that time.
I tried to insert in the function code something like that:

If IsNull(Aircraft) then
exit function
else
.... (the DAO.Recordset code)

but it doesn't work.
Any suggestion?
Thanks, bye.

Riccardo

 

[pr2-eugin]

Re: Error "Invalid use of Null" in a Function called from a Form_Current event

Hello Richard1980, your problem is you need to make sure the Call is not made, the Error will occur when the function call is made, as the Function FlightsByAircraft expects a Long number to be used..

One very simple way to avoid is..

Private Sub Form_Current()
    [COLOR=Red][B]If Not Me.NewRecord Then[/B][/COLOR] txtStats1 = FlightsByAircraft(Me.AircraftID)
End Sub

 

[Richard1980]

Re: Error "Invalid use of Null" in a Function called from a Form_Current event

Hello Richard1980, your problem is you need to make sure the Call is not made, the Error will occur when the function call is made, as the Function FlightsByAircraft expects a Long number to be used..

One very simple way to avoid is..

Private Sub Form_Current()
    [COLOR=red][B]If Not Me.NewRecord Then[/B][/COLOR] txtStats1 = FlightsByAircraft(Me.AircraftID)
End Sub

Sure, very simple. I was working in the function code to avoid calling, completely wrong indeed.
Anyway, now that error is disappeared, but the unbound textbox where the result is displayed show the previous function result, while it should show 0 or blank.
When I open the form on a new record or when I click on "new record" button, the form must be completely blank. With your code it is "dirty" with the previous result.

@ David R.
Ok, I know I can manage that using "Else" sentence, but the problem is that I have several unbound textboxes showing function results and I would like to avoid writing many rows of VBA code to manage all of them either for Not Me.NewRecord and Me.NewRecord cases under if.

 

[David R]

Re: Error "Invalid use of Null" in a Function called from a Form_Current event

If Not Me.NewRecord Then 
       txtStats1 = FlightsByAircraft(Me.AircraftID)
       txtotherUnbound = blah(Me.AircraftID)
       numUnboundSummary = foo(Me.AircraftID)
Else
       txtStats1 = ""
       txtOtherUnbound = ""
       numUboundSummary = 0
End If

etc.

 

[Richard1980]

Re: Error "Invalid use of Null" in a Function called from a Form_Current event

Ok... exactly what I don't want to do, as written before.
Thanks.

 

[David R]

Re: Error "Invalid use of Null" in a Function called from a Form_Current event

You're going to have to use VBA to do it somewhere. Your other option is basically to convert your functions to Variant (I think) and check for the null there, but this way should be cleaner.

What is your objection? If you are planning on adding additional calculated fields later, you can look into looping through a collection of fields via tags.

 

[pbaldy]

Re: Error "Invalid use of Null" in a Function called from a Form_Current event

You get an error because the input variable can't handle a Null. You can change it to Variant:

Function FlightsByAircraft(Aircraft As Variant) As Long

And then handle the potential Null within your function.

 

[Richard1980]

Re: Error "Invalid use of Null" in a Function called from a Form_Current event

You get an error because the input variable can't handle a Null. You can change it to Variant:

Function FlightsByAircraft(Aircraft As Variant) As Long

And then handle the potential Null within your function.

Function FlightsByAircraft(Aircraft As Variant) As Long
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim str As String
If IsNull(Aircraft) Then
    FlightsByAircraft = 0
Else
str = "SELECT * FROM tblFlights WHERE AircraftID = " & Aircraft
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(str)
    If rst.RecordCount = 0 Then
        FlightsByAircraft = 0
    Else
    rst.MoveLast
    FlightsByAircraft = rst.RecordCount
    End If
rst.Close
Set dbs = Nothing
Set rst = Nothing
End If
End Function

no more errors when switching to a new record, but when I update the AircraftID field, unbound textbox is not updated and stands at 0. Only when I return to that record, the field gets updated.

 

[pbaldy]

Re: Error "Invalid use of Null" in a Function called from a Form_Current event

The form's current event only fires when you move to the record. If you want this to fire as soon as you update that field, call it from the after update event of that control too.