Help with Code

[Franklin]

At the moment, I have this code associated with my Database (see below).

'Private Sub cmdCalc_Click()
'
''use a collection to gather relevant controls
'
' Dim collCbos As Collection 'we will use to group controls with
values
'
' Dim ctrl As Control 'needed to select and add controls to
our collection
'
' Dim intTotal As Integer 'accumulate scores
'
' Set collCbos = New Collection 'initialize the collection
'
' For Each ctrl In Me.Controls 'we are going to walk through all
controls on the form
' If InStr(ctrl.Name, "cboQ") <> 0 Then
' If ctrl.Value <> 0 Then
' 'add only cbos that have non-zero to collection
' collCbos.Add ctrl
' intTotal = intTotal + ctrl.Value
' End If
' End If
'
' Next
' Me.txtTotal = intTotal 'assign result to unbound textbox
' Me.txtMax = collCbos.Count * 4 'assign maximum score to unbound textbox
' Me.txtAverage = intTotal / Me.txtMax 'assign average to unbound text
box
'
' Set collCbos = Nothing
'
'End Sub

Private Sub cmdCalc_Click()

'use a counter rather than collection

Dim ctrl As Control 'needed to select and add controls to
our collection

Dim intTotal As Integer 'accumulate scores

Dim intCntr As Integer


For Each ctrl In Me.Controls 'we are going to walk through all
controls on the form
If InStr(ctrl.Name, "cboQ") <> 0 Then
If ctrl.Value <> 0 Then
'add only cbos that have non-zero to collection

intTotal = intTotal + ctrl.Value
intCntr = intCntr + 1
End If
End If

Next
Me.txtTotal = intTotal 'assign result to unbound textbox
Me.txtMax = intCntr * 4 'assign maximum score to unbound textbox
Me.txtAverage = intTotal / Me.txtMax 'assign average to unbound text
box


End Sub
*********
How can I change the "Me.txtTotal = intTotal 'assign result to unbound
textbox
' Me.txtMax = collCbos.Count * 4 'assign maximum score to unbound textbox
' Me.txtAverage = intTotal / Me.txtMax 'assign average to unbound text
box, " in order to run queries/reports on these numbers?

I tried changing their name (unbound text box) after associating the unbound
textboxes with a source but it didn't work.

Also, how do I get the Me.txtTotal, txtMax, & txtAverage to display
automatically, as opposed to an on click. Right now, the last figure obtained
in the Average field stays the same from one record to the next, so the user
must manually hit a button every time they switch records to get an accurate
figure.

Ultimately, I would like to have the totals (txtTotal, Max, Average)
automatically populate on the report, have the totals save with each record,
be able to run reports using those fields.

Thank you in advance.

 

[Arvin Meyer MVP]

1. You either have to bind the txtTotal textbox to a field, or to refer to
its value on the open form from the report like:

= Forms!FormName!txtTotal

2. Move the code in the Click event of a button to the Current event of the
form. If you update the form, you'll still need to keep the button and its
event.

3. You usually never store calculations since they depend upon other fields
(which could change) and the can easily be recalculated at runtime. In
general calculations are only saved when they have historical value (like a
paycheck total if the pay rate is not in a many side table).