Help with VB code

  • Thread starter Thread starter Franklin
  • Start date Start date
F

Franklin

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
(e-mail address removed)

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!
 
Ultimately, I would like the totals to remain in each record and get
calculated automatically (w/o having a button to be clicked).
Click to expand...

I'd calculate the totals in the Query upon which the report is based, rather
than trying to save them anywhere in any table.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.
 
Back
Top