sportsguy
Finance wiz, Access hack
- Local time
- Today, 00:11
- Joined
- Dec 28, 2004
- Messages
- 363
I have a Text Box, TOTAL, on the form foooter that is linked to a function in a module. There is a datasheet subform in the form, which has three combo boxes to retrieve specific monthly values on each project. Say one project for one district and one type has 12 monthly records with values.
I want the TOTAL control to display the total of the selected records in the subform, in code. Therefore the total will change at Requery on change in any one of the combo boxes. I can do this with another subform, easily, however, I want to do this in code.
When I open the form, or requery the control, i get an undefined error message. Any suggestions, or should i go back to the subform method.
thanks,
sg
Here is what i have in the module:
Public Function SubFormTotal() As Single
Dim anyTotal As Double
Dim cnnLocal As New ADODB.Connection
Dim rstCurr As New ADODB.Recordset
Set cnnLocal = CurrentProject.Connection
anyTotal = 0
rstCurr.Open "SELECT COMMITTED.Goal " _
& "FROM COMMITTED INNER JOIN PROJECTS ON COMMITTED.SequenceID = PROJECTS.SequenceID " _
& "WHERE (((COMMITTED.SequenceID) Like IIf(IsNull([Forms]![ReviewSubmitted].[comboProject]),'*',[Forms]![ReviewSubmitted].[comboProject])) " _
& "AND ((COMMITTED.District) Like IIf(IsNull([Forms]![ReviewSubmitted].[comboDistrict]),'*',[Forms]![ReviewSubmitted].[comboDistrict])) " _
& "AND ((COMMITTED.BenefitType) Like IIf(IsNull([Forms]![ReviewSubmitted].[comboBenefit]),'*',[Forms]![ReviewSubmitted].[comboBenefit]))); ", _
cnnLocal, adOpenStatic, adLockPessimistic
With rstCurr
Do Until .EOF
For Each fldCurr In .Fields
anyTotal = anyTotal + fldCurr.Value
Debug.Print anyTotal
Next
.MoveNext
Loop
End With
rstCurr.Close
Set cnnLocal = Nothing
Set rstCurr = Nothing
SubFormTotal = anyTotal
End Function
I want the TOTAL control to display the total of the selected records in the subform, in code. Therefore the total will change at Requery on change in any one of the combo boxes. I can do this with another subform, easily, however, I want to do this in code.
When I open the form, or requery the control, i get an undefined error message. Any suggestions, or should i go back to the subform method.
thanks,
sg
Here is what i have in the module:
Public Function SubFormTotal() As Single
Dim anyTotal As Double
Dim cnnLocal As New ADODB.Connection
Dim rstCurr As New ADODB.Recordset
Set cnnLocal = CurrentProject.Connection
anyTotal = 0
rstCurr.Open "SELECT COMMITTED.Goal " _
& "FROM COMMITTED INNER JOIN PROJECTS ON COMMITTED.SequenceID = PROJECTS.SequenceID " _
& "WHERE (((COMMITTED.SequenceID) Like IIf(IsNull([Forms]![ReviewSubmitted].[comboProject]),'*',[Forms]![ReviewSubmitted].[comboProject])) " _
& "AND ((COMMITTED.District) Like IIf(IsNull([Forms]![ReviewSubmitted].[comboDistrict]),'*',[Forms]![ReviewSubmitted].[comboDistrict])) " _
& "AND ((COMMITTED.BenefitType) Like IIf(IsNull([Forms]![ReviewSubmitted].[comboBenefit]),'*',[Forms]![ReviewSubmitted].[comboBenefit]))); ", _
cnnLocal, adOpenStatic, adLockPessimistic
With rstCurr
Do Until .EOF
For Each fldCurr In .Fields
anyTotal = anyTotal + fldCurr.Value
Debug.Print anyTotal
Next
.MoveNext
Loop
End With
rstCurr.Close
Set cnnLocal = Nothing
Set rstCurr = Nothing
SubFormTotal = anyTotal
End Function