OK, non professional programmer requests help!~ Thanks in advance

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
 
sg,

I changed it to DAO. Really didn't have to. The sql string is built based on
the values of the combos.

Code:
Example 1:  comboProject     = 52
            comboDistrict    = District52
            comboBenefitType = BenefitTypeA

SELECT COMMITTED.Goal
FROM   COMMITTED INNER JOIN PROJECTS ON COMMITTED.SequenceID = PROJECTS.SequenceID
WHERE (COMMITTED.SequenceID = 52 Or 52 = 1)                                     And
      (COMMITTED.District = 'District52' Or 'District52' = 'x')                 And 
      (COMMITTED.BenefitType = 'BenefitTypeA' =  "x" Or 'BenefitTypeA' = 'x')

You'll start getting 'x' = 'x' when you leave a combo blank, then that will make
its section of the AND clause True.

Example 2: ComboProject     = 52
           comboDistrict    = EmptyCombo
           comboBenefitType = EmptyCombo

SELECT COMMITTED.Goal
FROM   COMMITTED INNER JOIN PROJECTS ON COMMITTED.SequenceID = PROJECTS.SequenceID
WHERE (COMMITTED.SequenceID = 52 Or 52 = 1)                                     And
      (COMMITTED.District = 'x' Or 'x' = 'x')                 And 
      (COMMITTED.BenefitType = 'x' =  "x" Or 'x' = 'x')


The Project combo maps to Committed.SequenceID which looks like an integer AND
is (maybe?) in .Column(0) of the combo.

The other combos I assumed to be strings.

Code:
Public Function SubFormTotal() As Single

Dim anyTotal As Double
Dim cnnLocal As DAO.Database
Dim rstCurr As DAO.Recordset
Dim sql As String

anyTotal = 0

str = "SELECT COMMITTED.Goal " & _
      "FROM   COMMITTED INNER JOIN PROJECTS ON COMMITTED.SequenceID = PROJECTS.SequenceID " & _
      "WHERE (COMMITTED.SequenceID = " & Nz([Forms]![ReviewSubmitted].[comboProject].Column(0), -1) & " Or " & _
      "      " & Nz([Forms]![ReviewSubmitted].[comboProject].Column(0), 1) & " = 1)                And " & _
      "      (COMMITTED.District = '" & Nz([Forms]![ReviewSubmitted].[comboDistrict], "x") & "' Or " & _
      "      " & Nz([Forms]![ReviewSubmitted].[comboDistrict], 1) & " = 1)                         And " & _
      "      (COMMITTED.BenefitType = '" & Nz([Forms]![ReviewSubmitted].[comboBenefit], "x") & "' Or " & _
      "      " & Nz([Forms]![ReviewSubmitted].[comboBenefit], 1) & " = 1)"

Set dbs = CurrentDb
Set rstCurr = dbs.OpenRecordSet(sql)

While Not rstcurr.EOF
   anyTotal = anyTotal + rstcurr!SomeField
   rstCurr.MoveNext
   Wend

rstCurr.Close
Set rstCurr = Nothing

SubFormTotal = anyTotal

End Function

Wayne
 

Users who are viewing this thread

Back
Top Bottom