Update Tables (2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

See post 353263 higher up in this thread.

Reply | Quote

See post 353263 higher up in this thread.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote