How to add data in an Unbound text Box - Access 2003

[SteveStew44]

Hello AW!

For a text box in a Report, I am trying to use the "SumOf([UnboundTextBox])" as the Control Source in the Report Footer to add the unbound text box in the Details section. I know it works with a bound text box, but does it not work with an unbound text box?

Thank you for your help!

Steve

 

[llkhoutx]

Your post doesn't make sense to me. How does a report unbound control get a value? If it's a calculation, you need to sum that calculation via a bound control.

 

[SteveStew44]

Hi Ilkhoutx,

Thanks for the reply! I am sorry if I was not clear.
The unbound text box in the Details section is populated by code as a Currency. I would like the Footer control to Sum the Detail control.

Cheers,
Steve

 

[marlan]

Hi,
Just a guess: when do the Fields get populated by code?
maybe the sum function works on non-populated textBoxes?

In that case, could you somehow make code run earlier?
if code runs in a form event, maybe have it run as a query field, and bound textBox to it?

Just a guess...

 

[llkhoutx]

Marian, I think that the detail section is built before the footer section, so the value is is referenceable.

"SumOf" is not an Access function.

 

[SteveStew44]

HI Marlan and Ilkhoutx,

Sorry, it is Sum() not SumOf. This is a report so the code runs in the format.

I used code to Sum the controls of the Detail section that were populated with code. I am having difficulty with the other controls as described below.
Here's the code I am using to populate the Unbound text box:

Public Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rsPPS As DAO.Recordset
Dim strSQL1 As String
Set db = CurrentDb


'To Calculate Finance Fees based on custom inputs -SP 11-30-14

Dim cTemp As Currency 'Temp variable to calculate Finance Fees
Dim finfee As Currency 'Temp variable to calculate Finance Fees

Set rsPPS = fDAOGenericRst("qryProfitPerSaleByMonth2")
finfee = 0

If rsPPS.EOF Then
finfee = 0
End If

'Calculate Finance Fees
cTemp = 0
strSQL1 = "SELECT * FROM tblProfitPerSaleCust WHERE CatID = 1"
Set rs1 = db.OpenRecordset(strSQL1)
Do While Not rs1.EOF
cTemp = rsPPS(rs1("CustomField")).Value
cTemp = (cTemp * rs1("Multiple"))
If rs1("Add/Subtract") = "-" Then
finfee = finfee - cTemp
Else
finfee = finfee + cTemp
End If
rs1.MoveNext
Loop
rs1.Close

Me.FinanceFees = finfee
finfeeTot = finfeeTot + finfee

db.Close
Set db = Nothing
End Sub


Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)

Me.SumOfFinanceFees = finfeeTot
finfeeTot = 0

End Sub

*****************

So, I have some other text boxes in the same Detail section that use the Me.FinanceFees code data results to add, i.e. "=[FinanceFees]-[OtherCosts]" as the Control Source.
It is these controls that I would like to Sum in the Footer section.
Any suggestions?

Cheers!
Steve

 

[pbaldy]

You can't sum a calculated control. You can have:

=Sum([FinanceFees]-[OtherCosts])

In a group or report footer, which is what I suspect llkhoutx meant by "you need to sum that calculation via a bound control".

 

[Galaxiom]

I know it works with a bound text box, but does it not work with an unbound text box?

Incorrect. Controls are not summed whether they are bound, calculated or otherwise.

The Sum function is applied to Fields in the Recordset.

They may appear to sum the textbox but that is because, by default, the bound textbox has the same name as the field it is bound to when the form or report is created by the wizard.

 

[llkhoutx]

Assuming arguendo that Galaxiom, is correct, yon you footer OnFormat even run a query performing the sum and store it accordingly.

The following Google search might be of additional help for you: "access report footer sum"


Good luck.

 

[SteveStew44]

Thank you to all who responded. Good to know that controls are not allowed to Sum. It makes sense now that the the bound controls appear to be Sum'd because they are tied to the field and an unbound isn't. I guess the only way is through code. Ha ha!

Thank you all again! Good info.

Cheers!
Steve

 

[pbaldy]

I guess the only way is through code. Ha ha!

No, this should work:

=Sum([FinanceFees]-[OtherCosts])