Can't find source of errors

[GraeagleBill]

Below is a screen-shot of the problematic controls, which includes the function "=ProfitLoss(x)" as their Control Source, i.e., for each of the two controls. The second screen-shot simply shows the resulting errors when the app is run. Below those two screen-shots is the function code AND a screen-shot of the resulting immediate window when the app is run.

Obvious????

Public Function ProfitLoss(Target As Integer)
Dim TotalBasis As Currency
Dim TotalSale As Currency
Dim SharesOwned As Long


If Nz(Me.InvstID) Then
    TotalBasis = DSum("[Basis]", "Ledgers", "InvstID = " & Me.InvstID)
    TotalSale = DSum("[NetSales]", "Ledgers", "InvstID = " & Me.InvstID)
    SharesOwned = DSum("[Shares]", "Ledgers", "InvstID = " & Me.InvstID)
    
    Select Case Target
        
    Case 1    ' Current Position, based on current value provided by Yahoo Finance
        If SharesOwned > 0 Then
            ProfitLoss = (Me.SharePrice * SharesOwned) - TotalBasis
            Debug.Print ProfitLoss
            Me.tbCurGainLoss = Null
        Else
            ProfitLoss = Null
        End If
            
    Case 2   ' Current Gain or Loss, based on recorded sale
        If SharesOwned = 0 Then
            ProfitLoss = TotalSale - TotalBasis
            Debug.Print ProfitLoss
            Me.tbCurPosition = Null
        Else
            ProfitLoss = Null
        End If

    End Select
Else
    ProfitLoss = Null
End If

End Function

And, the immediate window from the Debug.Print showing that the desired values are in fact properly obtained.

[pbaldy]

My first suspect is that you're trying to set the value of the other control from the function. You can't, because it has a control source using the function. Take those lines out and see what happens. The function should take care of returning the appropriate value.

[pbaldy]

Wow, somebody didn't like their answer.

[GraeagleBill]

I made the 2nd of the two controls Unbound and the 1st of the two formatted without error. I'll redo the function arrangement making the two controls more independent and the problems should go away. I'll post back when I'm done with that.

Paul, I didn't understand your post: Wow, somebody didn't like their answer.

June, changing the Dim's from Currency to either of Long or Double had no effect.

[pbaldy]

Paul, I didn't understand your post: Wow, somebody didn't like their answer.

The other post you responded to was deleted.

[June7]

Yeah, I just dealt with another thread that involved Currency type fields and formatting that was causing issues. A second look at this OP after seeing Paul's response and I knew I was on wrong track. Was hoping I removed post before you read so you would not get side-tracked.

[GraeagleBill]

This is getting stranger by the minute. Check this out: (Original function broken into two separate functions)

1) Each control gets its own function Control Source. Same errors in both columns when app runs.


2) GainLoss control made to be Unbound. Current Position formats appropriately.


3) Situation reversed, Position control made to be Unbound and GainLoss column
formats properly with its Control Source set to its function.


4) Original function broken into two independent functions.
Common Dim's global to form's code sheet.

Public Function Position()

If Nz(Me.InvstID) Then
    TotalBasis = DSum("[Basis]", "Ledgers", "InvstID = " & Me.InvstID)
    SharesOwned = DSum("[Shares]", "Ledgers", "InvstID = " & Me.InvstID)

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


End Function

Public Function GainLoss()
Dim TotalSale As Currency

If Nz(Me.InvstID) Then
    TotalBasis = DSum("[Basis]", "Ledgers", "InvstID = " & Me.InvstID)
    TotalSale = DSum("[NetSales]", "Ledgers", "InvstID = " & Me.InvstID)
    SharesOwned = DSum("[Shares]", "Ledgers", "InvstID = " & Me.InvstID)
            
    ' Current Gain or Loss, based on recorded sale
    If SharesOwned = 0 Then
        GainLoss = TotalSale - TotalBasis
        Me.tbCurPosition = Null
    Else
        GainLoss = Null
    End If

Else
    GainLoss = Null
End If

End Function

[GraeagleBill]

I found the problem. Access did not "like" my setting of the adjacent control. See the two statements shifted off to the right.

Public Function Position()

If Nz(Me.InvstID) Then
    TotalBasis = DSum("[Basis]", "Ledgers", "InvstID = " & Me.InvstID)
    SharesOwned = DSum("[Shares]", "Ledgers", "InvstID = " & Me.InvstID)

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


End Function

Public Function GainLoss()
Dim TotalSale As Currency

If Nz(Me.InvstID) Then
    TotalBasis = DSum("[Basis]", "Ledgers", "InvstID = " & Me.InvstID)
    TotalSale = DSum("[NetSales]", "Ledgers", "InvstID = " & Me.InvstID)
    SharesOwned = DSum("[Shares]", "Ledgers", "InvstID = " & Me.InvstID)
            
    ' Current Gain or Loss, based on recorded sale
    If SharesOwned = 0 Then
        GainLoss = TotalSale - TotalBasis
                                                                                                                               'Me.tbCurPosition = Null
    Else
        GainLoss = Null
    End If

Else
    GainLoss = Null
End If

End Function

[GraeagleBill]

I didn't really find problem per'se, just what to do to get the controls to format with the intended values.

[pbaldy]

I found the problem. Access did not "like" my setting of the adjacent control.

That's what I meant by "My first suspect is that you're trying to set the value of the other control from the function".

I'm a little surprised that referring to "Me.InvstID" inside the function works to return a different value for each record. I would have passed it as an input to the function.

[GraeagleBill]

I guess I didn't completely understand your original point about "set the value of the other control". I'm sorry now that I didn't bench-mark the module before I split the code into two independent functions, or I would have tried to run the single function with those two statements suppressed. I'll leave things as they are, as I have other "fish to fry".

Thanks,
Bill

[pbaldy]

No problem Bill. We need some snow! We played golf over the weekend and it was beautiful. We should be shoveling snow.

[ssanfu]

41F deg in Anchorage (last week) ....... I'm going to have to put the snow machines away and get out the lawn mower!!

Dropped back down to a high today of 21F.... low of 10F.... but still no snow.

[June7]

But there's sunshine! Hooray!

Paul, with function behind the form/report should be no reason could not reference fields and controls on the form/report as inputs.

[pbaldy]

I'm not surprised you can reference the form from within the function, but I would have expected that Me.Whatever would return the value of the record with focus, not each record in the continuous form (in other words each record would have used the same InvestID and shown the same result). It appears that the function is aware of what record it was called from and uses the appropriate value. Not the first new thing I've learned today, probably not the last. I would have explicitly passed it:

Public Function GainLoss(lngInvestID as Long)

and called it

=GainLoss(InvestID)

probably still will, as I'd be uncomfortable with assuming Access will use the correct value.