New Record Default Value Not Working

[mistera]

I have a form where the control source of several text box fields includes a function that looks up values in a table that is not the form's record source. The default value in the property for these fields is set to 0. Unfortunately, when I migrate to a new record, these fields display the dreaded #Error. It should be noted that these fields are for display only and are not even in the form's underlying table.

Here is an example of the control source for one of the fields:

=GetFYSummary("Budget",[ProjectID],0,0,"PPE","frm_ProjectFinancials")

Here is the start of the code for the function:

Public Function GetFYSummary(strField As String, lngProjectID As Long, lngFYYear As Long, intPeriod As Integer, _
    strWBSType As String, strSource As String) As Long
 
    If Mid(strSource, 1, 3) = "frm" Then
        If Forms.Item(strSource).NewRecord Then
            GetFYSummary = 0
            Exit Function
        End If
    End If
     .
     .
     .
End Function

As you can see, I'm trying to cover my bases to ensure that the displayed value of the field shows 0 rather than #Error.

I even tried including a breakpoint at the first line of code in the function and it is never even triggered when I move to a new record.

One more thing to mention: As soon as I type the first letter/number in a field that is tied to the form's record source, all of these display values do change to 0 appropriately.

What am I missing here? HELP!!

 

[pbaldy]

If I had to guess, I'd guess ProjectID is an autonumber field and thus doesn't have a value until you dirty the record. Try changing

lngProjectID As Long

to be a Variant, which can accept a Null value, and test for Null in the function.

 

[mistera]

Paul,

You were absolutely right! ProjectID is an autonumber field and when I changed it to be variant in the function, everything worked perfectly.

THANKS!!

 

[pbaldy]

Happy to help!