Hello!

I am using Access 2007. I had someone compile some VB code for a database I
was creating. However, some of the data recorded in the record doesn't get
saved when browsing through records. This is crucial for creating reports
around these figures.

The code (see below) has totals linked to a "unbound" text boxes. It also
has a button that must be clicked in order to calculate scores. I'd prefer
for the totals to appear automatically as other data is changed.

Ultimately, I would like the totals to remain in each record and get
calculated automatically (w/o having a button to be clicked).

I have a Monday deadline and would greatly appreciate help with this. Due to
some extreme reasons, I cannot get a hold of the original programmer. I could
also email you a sample and explain this better. My email is
mentalhealthnyc@yahoo.com

THANK YOU!

--Frank

VB Code:

***
Code:
Option Compare Database 
Option Explicit 

'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
*******

THANK YOU!

Last edited by shabbir; 13May2009 at 21:49.. Reason: Code blocks