Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2005
    Posts
    67

    Unanswered: Variable not reinitializing to zero so totals check accumulating everything

    Hi,

    I need help pls. I have 3 forms. 1-many-many relationship.
    The main form has a total figure in one of the text fields.
    In the 3rd form, a number of figures are added in, but they should total the main one in the main form.
    The problem I am having is that the variable I'm using doesn't seem to be reinitializing to zero.
    When I did set it to zero, it started picking up only the last entry so of course the check totals value comparison is always wrong. On the other hand, when I take it out, it just keeps accumulating.
    The 2nd prob is based on the first and is that when an amendment is made based on the check totals throwing up an error message, and the user corrects, it simply adds on to the previous vals.

    Pls help:
    My code is as follows:
    Code:
     '
                '------------------------------------------------------
                'Incrementally add stroke values into check box total
                '------------------------------------------------------
                
                StrokeVal = Me.txtStrokeVal
                
                acc = acc + txtStrokeVal
                store = acc
                Forms!frmRHForm!txtChckTotal = store
    This is within a click() event

    I declared both acc and store as global variables (I thought), by declaring them in the same section as the 'option explicit section'.

    When I initialize acc i.e. acc = 0, within the event, it ends up only taking the last entry. Can anyone pls, pls pls point me in the right direction?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    The "right direction" is to avoid storing derived values in a table in the first place...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Dec 2005
    Posts
    67
    I guess so, but its one of the requirements I've been given.
    Do u know a way round it?

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What was the requirement, exactly? To be able to produce those totals on demand, or to store them at the table level? That's a pretty big difference. If it's the latter, who made that requirement, and why?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Dec 2005
    Posts
    67
    Well they want to be able to validate the totals in the main form against the breakdown of the totals in another form and its stored in a table and output in a text file if correct

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Business logic. What is the business logic. Pretend I know dick-all about databases. What are you trying to do here...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Dec 2005
    Posts
    67
    To make sure that the details entered in the 3rd form sum up to the details on the 1st form. The 1st one is like an invoice with the grand total. the 3rd one is basically itemised. The item totals should add up to the grand total.
    I designed it so that as the user is entering the values in the itemized form, a text box is being populated on the 1st form. When entry is complete, the two field should be the same in val. ie the grand total that was manually entered and the sum of the item values that were manually entered.

  8. #8
    Join Date
    Dec 2005
    Posts
    67

    Find and Replace using VBA

    Hi - I would like to go down a column in a table and remove all spaces. When I use the ordinary find and replace after a while it crashes. Does anyone know how I could write this in VBA?

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    UPDATE yourTable
    SET yourField = Replace(yourField, " ", "")
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Dec 2005
    Posts
    67
    Thanks. I'll let you know how I got on.

  11. #11
    Join Date
    Feb 2006
    Posts
    56
    Can I make a quick design suggestion to you? Why not set the the value on the first form as a running value from the third form. I don't understand why you would want to have the grand total manually entered. That sounds like the opportunity to generate mistakes.

    You could always create a command button to check the form values like this:

    1. for the third form, make sure that the underlying table set has an order id field.

    2. in your command button on your first form, have the following:

    dim db as database, strSQL as String, rs as recordset
    dim a,b
    a = 0
    b = 0

    strSQL= "SELECT * from table_a WHERE table_a.orderID = 1;"
    set db = currentdb()
    set rs = db.openrecordset(strSQL)

    rs.movefirst
    a = rs!itemprice
    rs.movenext
    do until rs.eof
    a = a +rs!itemprice
    loop
    rs.close
    db.close

    if a <> me.textboxtotal then
    msg "itemized totals don't match grand total"
    end if

    I'm sure that there are far better ways to do this, but that is my suggestion. Plus, I typed this up on a boring conference call (that I shouldn't have to attend!!!!)

    Thanks,

    John

  12. #12
    Join Date
    Feb 2006
    Posts
    56
    minor point- put the rs.movenext as the last line inside the loop....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •