Problem carrying out updates based on choices on a report

[Zippy_The_Wonderdog]

I have created a good invoice report for my Gamestop customer database but it requires some additional functionality that I am unsure how to implement.

It currently pulls information about a customers pre-orders from three tables (Customer, Pre-Orders, Games), but I cannot figure out the following:



1. If loyalty points are to be used then these should be deducted from the final bill and their loyalty points set to 0.
2. After the invoice is printed the loyalty points should be updated (Customer Loyalty points += total cost of bill/1000).

My current thinking is:

Display prices with and without loyalty points used.
Add a check box or dropdown to say if the customer has used loyalty points.
Add a Print button that:

1. Prints Out the Invoice
2. If the check box is ticked removes all loyalty points from the customer record
3. Calculates the loyalty points to be added from the bill and update the customers record.

I'm lost as to how to do this though, and so far have not been able to find help online, all suggestions welcome.

[NTC]

as this is a custom application design - rather than an application feature question - - there is no single correct answer. It will depend on your data structure and the type of user experience you wish to create. One must of course apply the points to a transaction and then reduce their reserve due to the transaction. So it is definitely a 2 step process with the second step being triggered by the first.

I will forewarn you that if their points is a single field value, rather than a table, then it can be tricky to reverse should a transaction be cancelled or modified. You are probably best designing 'Points' as one does inventory. In a well designed inventory database there is never a field that holds the stock amount - - rather it is always calculated on-the-fly as the sum of Stock In and Stock Out. Your best approach to points is also a Points In and Points Out approach - as this is more robust which will become evident when changes/reversals/modifications occur to transactions.

[Zippy_The_Wonderdog]

I will forewarn you that if their points is a single field value, rather than a table, then it can be tricky to reverse should a transaction be cancelled or modified. You are probably best designing 'Points' as one does inventory. In a well designed inventory database there is never a field that holds the stock amount - - rather it is always calculated on-the-fly as the sum of Stock In and Stock Out. Your best approach to points is also a Points In and Points Out approach - as this is more robust which will become evident when changes/reversals/modifications occur to transactions.

Thanks for taking the time to reply, fortunately I don't have to worry about reversals on the transaction with this particular project though for a future project I would certainly take this advice to heart.

The loyalty points earned by customers in store are stored in the customer table as a field (part of the requirements).

A simplified version of the tables used to produce the report can be seen below:

Customers(Customer_ID, forename, Surname, Address, loyalty_Points)
Pre-Orders(Order_Id, Customer_Id, Game_ID, Quantity)
Games(Game_Id, Name, Publisher, Release_Date, Cost)

I've came up with a viable solution which I have put here for posterity (it might help someone!)

Private Sub Button_click()
Dim Points_Earned As Integer
Dim Current_Points As Integer

Current_Points = Me.Loyalty_Point_Balance_box.Value

If Me.Loyalty_Points_Check = -1 Then 'this lets you know the radio button is unchecked.
Current_Points = 0 'Sets the customers loyalty points to 0
MsgBox "Customer to pay: " & Format(Me.Total_Cost_With_Loyalty_Deducted, "Currency")
Else
MsgBox "Customer to pay: " & Format(Me.Total_Cost, "Currency")
End If

Points_Earned = Me.Total_Cost / 1000
MsgBox "Loyal Gamestop minion, this visit you have earned: " & Points_Earned


Points_Earned = Current_Points + Points_Earned
MsgBox "Your Gamestop Loyalty Reward Balance is now: " & Points_Earned


DoCmd.RunSQL "UPDATE Customers SET Loyalty_Point_Balance = " & Points_Earned & " WHERE [Card_ID] = " & Me.Card_ID.Value

End Sub