[mentalhealthnyc]

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:

***

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!

[nimesh]

please check PM

[nimesh]

Hi,

Here are my comments, based on the file that you gave me.

1] The code here looks the unmodified while in the file, it has been modified, and the function has been delinked from the button.
2] The totals textbox is not unbound, it's bound to column "Total Score"
3] Will have to update the code to update this field automatically when any of the other responses change
4] The Maxscore that I mentioned is correct. I thought it differently, but after looking on the entry screen it got cleared.
5] The code has the field txtAverage which does not exist on the form and table.
6] Extra Info: The max possible value for each reponse is 4 but it is not forced, which means I can enter any value and it will accept.

- Nimesh.

[nimesh]

Update:

- Removed the button to calculate
- used the same code, but it a little different way to calculate total score whenever other responses get changed
- txtAverage was mitakenly named as txtAvgerage. Fixed
- Updated all the 22 cboQ fields to accept only values between 0 and 4
- You mailed be the original file minus the records, so the size was 8mb.
You can use compact and repair option to reduce the filesize (which automatically grows everytime you open and/or modify the database.)
Now it's 660kb.

Mailing you the updated file.
Let me know If I can help you with any other thing.