Calling function from a form control doesnt work

[Jesse Aviles]

I have the following Private function within a form module:

Private Function CalculateBalance() As Currency

Dim rst As DAO.Recordset
Dim frm As Form
Dim lngMyPosition As Long
Dim curSumPayments As Currency
Dim curSumDeposits As Currency
On Error GoTo HandleErrors

Set frm = Me
Set rst = frm.RecordsetClone
With rst
.MoveLast
.MoveFirst
End With
rst.Bookmark = frm.Bookmark
lngMyPosition = rst.AbsolutePosition
Debug.Print "lngMyPosition: " & lngMyPosition
curSumPayments = DSum("0 + Nz([curPayment])", "tblTrnxRegister", _
"lulngBankAccountID= " & frm!cboAccountSelector And frm!bolVoid =
False And _
rst.AbsolutePosition <= lngMyPosition And rst.EOF = False)
Debug.Print "curSumPayments: " & curSumPayments
curSumDeposits = DSum("0 + Nz[curDeposit]", "tblTrnxRegister", _
"lulngBankAccountID= " & frm!cboAccountSelector And _
rst.AbsolutePosition <= lngMyPosition And rst.EOF = False)
Debug.Print "curSumDeposits: " & curSumDeposits
CalculateBalance = CCur(curSumDeposits - curSumPayments)
Debug.Print "CalculateBalance: " & CalculateBalance

ExitHandler:
Set rst = Nothing
Exit Function

HandleErrors:
Select Case Err.Number
Case 3021
Resume Next
Case Else
MsgBox "Error number: " & Err.Number & vbCrLf & vbCrLf &
Err.Description, vbMsgBoxHelpButton, "Unexpected error", Err.HelpFile,
Err.HelpContext
GoTo ExitHandler
End Select

End Function

The function gets called from a control with its format property set to
currency and I get an error 13, Type mismatch. Variables, fields and the
function are set as currency. The code appears to stop at the - Debug.Print
"lngMyPosition: " & lngMyPosition - statement since that is the only value I
can see in the Immediate window. I also set the code to ignore the error to
see what values I was getting from the calculations and VBA is not
evaluating the formulas (value received is 0). I've checked the names of
the fields and tables and they are correct. Thanks for any help.

 

[Steve Schapel]

Jesse,

I believe your syntax is incorrect in the line of code beginning
curSumPayments = ...

I think it needs to be something like this:
If rst.AbsolutePosition <= lngMyPosition And rst.EOF = False Then
curSumPayments = DSum("Nz([curPayment],0)", "tblTrnxRegister", _
"lulngBankAccountID= " & frm!cboAccountSelector & " And " & _
frm!bolVoid & "= False)
End If

I don't think you can refer to recordset items or properties within
the Condition clause of a domain aggregate function such as DSum(). I
mean, rst.AbsolutePosition and rst.EOF have got nothing to do with
"tblTrnxRegister". But in any case, I can't work out what you are
trying to get at with 'rst.AbsolutePosition <= lngMyPosition'. You
have already defined 'lngMyPosition = rst.AbsolutePosition' so
'rst.AbsolutePosition <= lngMyPosition' will always be true.

The same applies to the curSumDeposits line as well.

- Steve Schapel, Microsoft Access MVP

I have the following Private function within a form module:

Private Function CalculateBalance() As Currency

Dim rst As DAO.Recordset
Dim frm As Form
Dim lngMyPosition As Long
Dim curSumPayments As Currency
Dim curSumDeposits As Currency
On Error GoTo HandleErrors

Set frm = Me
Set rst = frm.RecordsetClone
With rst
.MoveLast
.MoveFirst
End With
rst.Bookmark = frm.Bookmark
lngMyPosition = rst.AbsolutePosition
Debug.Print "lngMyPosition: " & lngMyPosition
curSumPayments = DSum("0 + Nz([curPayment])", "tblTrnxRegister", _
"lulngBankAccountID= " & frm!cboAccountSelector And frm!bolVoid =
False And _
rst.AbsolutePosition <= lngMyPosition And rst.EOF = False)
Debug.Print "curSumPayments: " & curSumPayments
curSumDeposits = DSum("0 + Nz[curDeposit]", "tblTrnxRegister", _
"lulngBankAccountID= " & frm!cboAccountSelector And _
rst.AbsolutePosition <= lngMyPosition And rst.EOF = False)
Debug.Print "curSumDeposits: " & curSumDeposits
CalculateBalance = CCur(curSumDeposits - curSumPayments)
Debug.Print "CalculateBalance: " & CalculateBalance

ExitHandler:
Set rst = Nothing
Exit Function

HandleErrors:
Select Case Err.Number
Case 3021
Resume Next
Case Else
MsgBox "Error number: " & Err.Number & vbCrLf & vbCrLf &
Err.Description, vbMsgBoxHelpButton, "Unexpected error", Err.HelpFile,
Err.HelpContext
GoTo ExitHandler
End Select

End Function

The function gets called from a control with its format property set to
currency and I get an error 13, Type mismatch. Variables, fields and the
function are set as currency. The code appears to stop at the - Debug.Print
"lngMyPosition: " & lngMyPosition - statement since that is the only value I
can see in the Immediate window. I also set the code to ignore the error to
see what values I was getting from the calculations and VBA is not
evaluating the formulas (value received is 0). I've checked the names of
the fields and tables and they are correct. Thanks for any help.

 

[Jesse Aviles]

Mr. Schapel:

Here is the code with some modifications made in order to work. Thanks!

If rst.AbsolutePosition <= lngMyPosition And rst.EOF = False Then
curSumPayments = DSum("Nz([curPayment],0)", "tblTrnxRegister", _
"lulngBankAccountID= " & frm!cboAccountSelector & " And " &
frm!bolVoid <> True)
End If

What I'm trying to do is to have an unbound text field on the form (and in a
report) that shows our bank balance on a transaction basis. I could use the
Primary ID of the record to perform the calculations without any of this
hassle. However the order entered in the mdb is not necessarily arranged by
date. If I use the PrimID to perform a calculation, the value shown in the
text box may not be accurate (the form is sorted by date, not by the PrimID
value). This is where the absolute position gets in the game. The property
will provide me with the current sort order ordinal position (which no two
records will share) and my idea is to use the AbsolutePosition as the base
of my calculations. However my idea is not that good because the
calculation will be performed but not as I expected, I can only see the
final value (the domain value) and not the calculated value at each record.

--
Jesse Avilés
(e-mail address removed)
http://home.coqui.net/monk
Reply Only To The Newsgroup

Jesse,

I believe your syntax is incorrect in the line of code beginning
curSumPayments = ...

I think it needs to be something like this:
If rst.AbsolutePosition <= lngMyPosition And rst.EOF = False Then
curSumPayments = DSum("Nz([curPayment],0)", "tblTrnxRegister", _
"lulngBankAccountID= " & frm!cboAccountSelector & " And " & _
frm!bolVoid & "= False)
End If

I don't think you can refer to recordset items or properties within
the Condition clause of a domain aggregate function such as DSum(). I
mean, rst.AbsolutePosition and rst.EOF have got nothing to do with
"tblTrnxRegister". But in any case, I can't work out what you are
trying to get at with 'rst.AbsolutePosition <= lngMyPosition'. You
have already defined 'lngMyPosition = rst.AbsolutePosition' so
'rst.AbsolutePosition <= lngMyPosition' will always be true.

The same applies to the curSumDeposits line as well.

- Steve Schapel, Microsoft Access MVP

I have the following Private function within a form module:

Private Function CalculateBalance() As Currency

Dim rst As DAO.Recordset
Dim frm As Form
Dim lngMyPosition As Long
Dim curSumPayments As Currency
Dim curSumDeposits As Currency
On Error GoTo HandleErrors

Set frm = Me
Set rst = frm.RecordsetClone
With rst
.MoveLast
.MoveFirst
End With
rst.Bookmark = frm.Bookmark
lngMyPosition = rst.AbsolutePosition
Debug.Print "lngMyPosition: " & lngMyPosition
curSumPayments = DSum("0 + Nz([curPayment])", "tblTrnxRegister", _
"lulngBankAccountID= " & frm!cboAccountSelector And frm!bolVoid =
False And _
rst.AbsolutePosition <= lngMyPosition And rst.EOF = False)
Debug.Print "curSumPayments: " & curSumPayments
curSumDeposits = DSum("0 + Nz[curDeposit]", "tblTrnxRegister", _
"lulngBankAccountID= " & frm!cboAccountSelector And _
rst.AbsolutePosition <= lngMyPosition And rst.EOF = False)
Debug.Print "curSumDeposits: " & curSumDeposits
CalculateBalance = CCur(curSumDeposits - curSumPayments)
Debug.Print "CalculateBalance: " & CalculateBalance

ExitHandler:
Set rst = Nothing
Exit Function

HandleErrors:
Select Case Err.Number
Case 3021
Resume Next
Case Else
MsgBox "Error number: " & Err.Number & vbCrLf & vbCrLf &
Err.Description, vbMsgBoxHelpButton, "Unexpected error", Err.HelpFile,
Err.HelpContext
GoTo ExitHandler
End Select

End Function

The function gets called from a control with its format property set to
currency and I get an error 13, Type mismatch. Variables, fields and the
function are set as currency. The code appears to stop at the - Debug.Print
"lngMyPosition: " & lngMyPosition - statement since that is the only value I
can see in the Immediate window. I also set the code to ignore the error to
see what values I was getting from the calculations and VBA is not
evaluating the formulas (value received is 0). I've checked the names of
the fields and tables and they are correct. Thanks for any help.