Text Box Control Source

[GraeagleBill]

I have a form wherein there's a text box whose ControlSource is the function "=Position()". While I understand one can set the text box to NUll, one cannot set the ControlSource property to NUll. The function code is below. As I have it now, I simply trap the invalid use of null and let the text box be blank, as desired. However, I have the idea that there's a more appropriate method that should be put into practice?

Thoughts please.
Thanks,
Bill

Public Function Position() As Variant
Dim WhereExp As String

On Error GoTo PositionErr

If Me.InvstID <> 0 Then
    WhereExp = "InvstID = " & Me.InvstID & " AND TTypeID = 1"
    TotalBasis = DSum("[Basis]", "Ledgers", WhereExp)
    SharesOwned = DSum("[Shares]", "Ledgers", "InvstID = " & Me.InvstID)

    ' Current Position, based on current value provided by Yahoo Finance
    If SharesOwned > 0 Then
        Position = (Me.PriceQuote * SharesOwned) - TotalBasis
    Else
        Position = Null
    End If
 Else
    Position = Null
End If

ExitPosition:
    Exit Function
    
PositionErr:
    If Err.Number <> 94 Then _
        MsgBox "Error number " & Err.Number & ": " & Err.Description
    Resume ExitPosition

End Function

[ranman256]

any 1 of these functions could return null, so I would convert with NZ

WhereExp = "InvstID = " & nz(Me.InvstID) & " AND TTypeID = 1"
TotalBasis = nz(DSum("[Basis]", "Ledgers", WhereExp))

[GraeagleBill]

Point taken. TotalBasis is Dim'd Currency and SharesOwned is Dim'd Long. Neither would be "happy" if the DSum returned Null. While the app would not likely produce null values because of the nature of the app, I've implemented your suggestion as a matter of good practice.
Thanks,
Bill

[Micron]

FYI Position is a Jet reserved name. One can almost never go wrong in this department with a 'proper' naming convention. For example, I usually use 3 character codes for data types at the beginning of a variable, unless the variable is pretty much universally accepted as is. I will admit you might find something I've written where I have not done this, so I'd hope it's because the syntax is widely accepted as is. Position would therefore become varPosition and perhaps save me from a deeply hidden code bug.

[GraeagleBill]

Ah! "Position" got by me long ago. The app is quite old, written long before I adopted a naming convention probably similar to your own. Generally, I don't like to mess with old code for the sake of naming unless it's obviously causing problems. The function "Position", though, has been renamed to "fncPosition".

Who says you can't teach an old dog new tricks!

Thanks,
Bill

[Micron]

Great excuse! I may have to borrow it.
BTW, how does this even compile when there is no End If after Resume ExitPosition ? Does a Resume statement negate the need for an End If? If it does, that's a new one on me. And if the error number IS 94, then what? That one is not handled...

[GraeagleBill]

Great excuse! I may have to borrow it.

Feel free to do so

BTW, how does this even compile when there is no End If after Resume ExitPosition ? Does a Resume statement negate the need for an End If? If it does, that's a new one on me. And if the error number IS 94, then what? That one is not handled...

Easy, just use an underscore character after the "Then" to continue the statement to the next line

And if the error number IS 94, then what? That one is not handled...

The trap is quite specific to the function in that one cannot set the ControlSource of a text box to Null. And, when the function evaluates to Null, I want the text box to be blank as opposed to zero, which would normally be the case with a format property of "Currency". The whole issue is why I posted in the first place, in that I thought there might be a more preferred method with which I wasn't aware.

Bill

[Micron]

Given my font and its size, and that there is a double quote right over it, I missed the underscore. Not that my eyes are getting any worse.
As for the error trap, why it looks strange to me is because you are first directing execution to the line label, which is fine. But if the raised error is 94, your execution translates to "If the error number is not 94, tell the user about the error." So it seems you're not explicitly handling error 94, just suppressing it. If you had asked me if this would not generate an Access error message because your if statements don't tell it what to do in the case of 94, I would have said "I dunno, but why would you not handle the error if it's likely, even if it was just to say Resume ExitPosition?". If I was following in your footsteps, I would conclude it was an oversight. Not that I could fill your shoes...

[GraeagleBill]

So it seems you're not explicitly handling error 94, just suppressing it.

Yes. Within the context of this function, I want the contents of the text box, from where the function is invoked via its ControlSource, to display empty (blank). The whole point of the original post had to do with whether this method would generally be considered good practice, in that I don't know how else to accomplish attaining the blank display when NUll occurs.

[Micron]

whether this method would generally be considered good practice

Other than maybe setting it to an empty string. Sometimes the table field properties drive the decision.

[GraeagleBill]

Thanks for all your thoughts. Seeing as how I'm getting the desired blank text box I'll leave the error 94 trap as is and move on.
Bill

[Micron]

You know what they say: If it ain't broke...