Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update Tables (2000)

    Gradually getting a motor bike race database together. Have a number of different meetings each year with obviously a number of races within each meeting. Entry costs can vary meeting to meeting and there are optional costs per meeting (eg rent garage space or late entry fee). I have a form that is used to determine what costs apply to each rider with Y/N check boxes beside each optional cost. I have the form calculating the total (adding fixed costs to optional costs multiplied by Y/N field multiplied by -1) but am interested in smarter methods. Major problem is that I dont know how to save that value to the costs table as the total costs for this rider for this meeting . Appreciate any advice
    Also I have a form for each meeting where I specify the costs for that particular meeting. I would like to then have these copied to the appropriate section of the Costing form as the default when I am doing the actual costing described above. eg if meeting 3 has a default entry fee of $50, I would like this to be recorded as the default entry cost for riders for that meeting (and same for other cost items) whereas meeting 4 may have a default entry cost of $60
    At the end of this process, I obviously need to be able to record receipts when the riders pay, and to differentiate between receipts for this meeting and any payment of carry forward balances
    TIA
    Steve
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Tables (2000)

    You can either store each optional cost in the costs table, or just the sum, in the Before Update event of the form.

    I assume this form has a combo box from which the user can select the meeting. You can populate the various costs in the After Update event of this combo box. One way to get them is to add the meeting costs table to the record source of the form (with an outer join on the meeting ID field); DLookups are another possibility.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Tables (2000)

    Thanks for the quick response - I must be holding my tongue wrong as when I use the Before or After Update event , there are no changes in the contents of the table - could you be very specific on the useage required (Its Sunday, I'm tired not to mention thick - I said dont mention thick)
    Steve
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Tables (2000)

    Oh yeah, the cost check boxes and so on are unbound, so if you only change those, and nothing in the bound controls, the Before (or After) Update event of the form will not occur. So you'd better use the After Update event of each of the controls that contribute to the total cost.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Tables (2000)

    I am actually getting all the individual costs into the Table and even have a Total calculated on the form - my problem is getting the total from the form into the TotalCosts field in the Table
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Tables (2000)

    I didn't know that you were already storing the individual costs in the table. In that case, you shouldn't store the total too - it is redundant information. Instead, create a query based on the table and calculate the total in the query. That way it will always be up-to-date.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Tables (2000)

    Yeah I know but there are times when my inadequacies in Access make storing of calculated data the right way to go while I learn more - just for the sake of the exercise (redundancies and inefficiencies acknowledged) how would I do it
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Tables (2000)

    See <post#=353263>post 353263</post#> higher up in this thread.

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Tables (2000)

    Sorry if I have said something out of order especially given the massive contribution you make to this group.
    As a one man IT department in a small town, I have to be all things to all people (yes an impossible task) without any outside support except groups like this. This means that I sometimes have to learn the hard way by using inefficient methods while I learn more so I don't get bogged down on one small (to more experienced people) issue. I was hoping to get this to work (albeit inefficiently) so I could keep going with other items and come back later when I knew more to make it more efficient . Thanks for help anyway
    Steve
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Tables (2000)

    What I meant was just that if you really want to store the total in the table, you can do so in the After Update event of the controls bound to the individual cost fields. That should work, but without knowing further details, I can't give more specific advice.

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Tables (2000)

    Hans I understood your suggestion in concept but cannot make it work. My formula is
    [TotalCost]=[EntryCost]+[EventTimingCost]+([NoExtraTickets]*[ExtraTicketCost])+(([C&DGradeCost]*[C&DGradeFeatures(Y/N)]*-1)+([CrossEntryCost]*[CrossEntry(Y/N)]*-1)+([PracticeCost]*[PracticeCost(Y/N)]*-1)+([GarageCost]*[GarageFee(Y/N)]*-1)+([RidersLevySoloCost]*[RidersLevySolo(Y/N)]*-1)+([RidersLevySidecarCost]*[RidersLevySidecar(Y/N)]*-1)+([CreditCardCost]*[CreditCardCost(Y/N)]*-1)+[LateEntryCost]*[LateEntryFee(Y/N)]*-1)+([OtherFee1Cost]*[OtherFee1(Y/N)]*-1)+([OtherFee2Cost]*[OtherFee2(Y/N)]*-1).
    I have tried to add this to the after update event but it has no effect. Is there something fussy about syntax that I am missing
    TIA
    Steve
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Tables (2000)

    Which of the items mentioned in the formula correspond to controls bound to fields in the record source?

  13. #13
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Tables (2000)

    I'll probably get the terminology wrong here so apologise in advance.
    The answer (I think) is all. Basically all but 3 items (EntryCost and EventTimingCost and TicketCosts) are optional. Ticket costs are the number chosen (entered on the form) by the cost per ticket. For each optional cost, I have the cost field and an additional Y/N field for that cost eg Practice Cost and PracticeCost(Y/N). As I knew Access stores Yes as -1 and No as 0, I multiply the cost by the result in the Y/N field then the answer by -1 to make it a positive number (I'm sure there are smarter ways but this works for my level of knowledge
    I have an item on the form called total costs and do have the total successfully being calculated here but it is not stored anywhere. The formula for this calculation is the long one I sent before
    Steve
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Tables (2000)

    If all these items are bound, I don't understand why the Form_BeforeUpdate event doesn't work:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.TotalCostField = Me.TotalCost
    End Sub

    But of course, this will only work with records you change. You will have to run an update query (once only) to update existing records.

Posting Permissions

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