1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

Help with VB code

Discussion in 'Visual Basic ( VB )' started by mentalhealthnyc, May 13, 2009.

  1. mentalhealthnyc

    mentalhealthnyc New Member

    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 a moderator: May 13, 2009
  2. nimesh

    nimesh New Member

    please check PM
     
  3. nimesh

    nimesh New Member

    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.
     
  4. nimesh

    nimesh New Member

    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.
     

Share This Page