Go4Expert

Go4Expert (http://www.go4expert.com/)
-   Visual Basic ( VB ) (http://www.go4expert.com/forums/visual-basic/)
-   -   Help with VB code (http://www.go4expert.com/forums/help-vb-code-t17507/)

mentalhealthnyc 13May2009 20:49

Help with VB code
 
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!

nimesh 14May2009 00:40

Re: Help with VB code
 
please check PM

nimesh 14May2009 23:54

Re: Help with VB code
 
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 15May2009 01:09

Re: Help with VB code
 
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.


All times are GMT +5.5. The time now is 12:19.