All Powder Board and Ski Microsoft Access Workbook

All Powder Board and Ski Microsoft Access Workbook Chapter 7: Integrity and Transactions Jerry Post Copyright © 2007 1

Compute Sales Tax From Figure 6. 17 2

Action Create a new module At the top add: Option Explicit Add the function Compute. Sales. Tax Use Debug/Compile to find errors Close the module 3

Create Access Code Module Visual Basic Editor New Module Application code 4

Action Edit the Sale form in Design view Right click the Sales. Tax box and select the Build Code option Select the Enter event Add the line: Sales. Tax = Compute. Sales. Tax(Subtotal) Run the form Click on the Sales. Tax box to test the calculation 5

Add Event Code to the Sales Form Choose the Enter event Call the new function Right click and Build Code 6

Debugging Use F 8 to step through the code Set a break point Roll the cursor over a variable to see its current value 7

Action Open the Sale. Item subform in Design view Open the form properties and modify the Record Source query to add the Quantity. On. Hand column View the Field List box Drag Quantity. On. Hand onto the form Run the form and improve the layout 8

Adding Quantity. On. Hand to Subform Build query to add Quantity. On. Hand Right click to set properties Drag QOH from Field List box onto form 9

Form Events l Open a Form 1) Open 2) Load 3) Resize 4) Activate 5) Current 6) Enter 12 33 b a d Forms l 4) Deactivate 3) Unload 12 33 b a d Controls Close a Form 5) Close 7) Got. Focus Change rows 2) Lost. Focus 1) Exit 12 33 b a d 10

Main Control Events control event time 32 Enter 32 Got. Focus 131 Change: keystrokes 131 Exit 131 Lost. Focus 131 Before. Update 131 After. Update 11

Action Edit the Sale. Item subform in Design view Right click the QOH box and build code Select the After. Update event Add code to refresh the row and subtract Quantity. Sold from Quantity. On. Hand Test the form 12

First Attempt: QOH Code Private Sub Quantity. Sold_After. Update() Me. Refresh ‘ Save data for new rows Quantity. On. Hand = Quantity. On. Hand - Quantity. Sold End Sub 13

Problems What if the clerk entered the wrong value and should have entered 1 instead of 2 units? l Test it, and the code subtracts 1 from the QOH, leaving 7. l You need to add the original 2 units back. l Quantity. On. Hand = Quantity. On. Hand – Quantity. Sold + Old. Quantity 14

Clerk Changes SKU Subtract Inventory SKU QOH Enter SKU 950049, Quantity 2 950049 10 8 10 950050 10 8 Change SKU to 950050 Add back to 950049 Subtract for 950050 15

Clerk Changes SKU: Events Clerk enters original SKU (950049) and Quantity Code subtracts Quantity. Sold from Quantity. On. Hand (10 - 2 = 8) Clerk changes SKU to new value (950050) Active row becomes set for new value Need to subtract Quantity. Sold from Quantity. On. Hand for 950050 Need to add Quantity. Sold back into Quantity. On. Hand for 950049 Private Sub cbo. SKU_Enter() If Is. Null(cbo. SKU. Value) Then Old. SKU = "-1" Else Old. SKU = cbo. SKU. Value End If End Sub 16

Action Open the Sale. Item subform in Design view Choose View/Code Add and modify the code as specified to handle changes in quantity Test the form 17

SKU Changes: Code Private Sub cbo. SKU_Enter() If Is. Null(cbo. SKU. Value) Then Old. SKU = "-1" Else Old. SKU = cbo. SKU. Value End If End Sub Private Sub cbo. SKU_After. Update() If Old. SKU <> "-1" And Not Is. Null(Quantity. Sold) Then Quantity. On. Hand = Quantity. On. Hand - Quantity. Sold Dim sql As String sql = "UPDATE Inventory SET Quantity. On. Hand = Quantity. On. Hand + " _ & Quantity. Sold & " WHERE SKU='" & Old. SKU & "'" Dim cmd As ADODB. Command Set cmd = Create. Object("ADODB. Command") cmd. Active. Connection = Current. Project. Connection cmd. Command. Text = sql cmd. Execute End If End Sub 18

Delete Code Dim Del. Quantity() As String Dim Del. SKU() As String Dim n. Del As Integer Private Sub Form_Delete(Cancel As Integer) If Is. Null(cbo. SKU. Value) Or Is. Null(Quantity. Sold) Then Exit Sub If (n. Del = 0) Then Re. Dim Del. Quantity(Me. Current. View) Re. Dim Del. SKU(Me. Current. View) End If Del. SKU(n. Del) = cbo. SKU. Value Del. Quantity(n. Del) = Quantity. Sold n. Del = n. Del + 1 End Sub Private Sub Form_After. Del. Confirm(Status As Integer) Dim i As Integer For i = 0 To n. Del - 1 Delete. One. Row Del. SKU(i), Del. Quantity(i) Next i n. Del = 0 End Sub 19

Delete Code 2 Private Sub Delete. One. Row(By. Val SKU As String, By. Val Qty As String) Dim sql As String sql = "UPDATE Inventory SET Quantity. On. Hand = Quantity. On. Hand + " _ & Qty & " WHERE SKU='" & SKU & "'" Dim cmd As ADODB. Command Set cmd = Create. Object("ADODB. Command") cmd. Active. Connection = Current. Project. Connection cmd. Command. Text = sql cmd. Execute End Sub 20

Action Open the Sale. Item subform in Design view Add the old. SKU variable Add the SKU event code as described Place a breakpoint in the code before the Execute line Test the form by changing an SKU number Examine the sql line before it runs Add the delete code as shown Test multiple changes to validate the code 21

Transactions for Discounts New table 22

Rental Form Button to open discount form 23

Action Create the Rental Discount form in Design view Add the text boxes and button Save the form Open the Rental form in Design view Add a button to open the Discount form Modify the open code as indicated Run the form and test the button 24

Rental Discount Form Rent. ID and Amount are filled in by code on the Rental form Date default value is set to =Now() This is an unbound form built from design view with no Record Source 25

Action Open the Rental Discount form in Design view Add the specified code to the button click event Test the forms 26

Rental Form Code: Discount Button Private Sub cmd. Discount_Click() On Error Go. To Err_cmd. Discount_Click Dim st. Doc. Name As String Dim st. Link. Criteria As String st. Doc. Name = "Give. Rent. Discount" Do. Cmd. Open. Form st. Doc. Name, , , st. Link. Criteria Forms!Give. Rent. Discount!txt. Rent. ID = Rent. ID Forms!Give. Rent. Discount!txt. Discount. Amount = Sub. Total. Charges Exit_cmd. Discount_Click: Exit Sub Err_cmd. Discount_Click: Msg. Box Err. Description Resume Exit_cmd. Discount_Click End Sub 27

Discount Code Private Sub cmd. Discount_Click() Dim cmd As ADODB. Command Dim SQL 1 As String, SQL 2 As String Set cmd = Create. Object("ADODB. Command") cmd. Active. Connection = Current. Project. Connection SQL 1 = "UPDATE Rent. Item SET Repair. Charges=0 WHERE Rent. ID=" & txt. Rent. ID SQL 2 = "INSERT INTO Rental. Discount (Rent. ID, Discount. Date, Discount. Amount, Reason)" & _ " VALUES (" & txt. Rent. ID & _ ", #" & txt. Discount. Date & "#" & _ ", " & txt. Discount. Amount & _ ", '" & txt. Reason & "')" 28

Discount Code Continued On Error Go. To Err_Discount. Trans cmd. Active. Connection. Begin. Trans cmd. Command. Text = SQL 1 cmd. Execute cmd. Command. Text = SQL 2 cmd. Execute cmd. Active. Connection. Commit. Trans lbl. Message. Caption = "Changes recorded. " Exit 1: Exit Sub Err_Discount. Trans: cmd. Active. Connection. Rollback. Trans lbl. Message. Caption = Err. Description Resume Exit 1 End Sub 29

Action Create a new query in Design view Tables: Sale and Sale. Item Create column Sale. Week: Val(Format([Sale. Date], ”ww”)) Create column Value: [Quantity. Sold]*[Sale. Price]) Sum the Value column 30

Query for Cursor: Weekly Sales Val(Format([Sale. Date], ”ww”)) 31

Action Create a new form in Design view Add a button Add a text box named txt. Average Add the specified code to the button Run the form and test the code Place a breakpoint and step through the code as it runs 32

Form to Compute Average Increase 33

Code to Compute Average Increase Dim rst As ADODB. Recordset Set rst = Create. Object("ADODB. Recordset") Dim SQL As String SQL = "SELECT Sale. Week, [Value] FROM qry. Weekly. Sales" rst. Open SQL, Current. Project. Connection, ad. Open. Static, ad. Lock. Read. Only Dim avg 1 As Double, n As Integer Dim prior As Currency Open the SQL statement or table prior = -1 Do Until rst. EOF Skip the first week because If (prior > 0) Then there is no prior value avg 1 = avg 1 + (rst("Value") - prior) / prior n=n+1 Compute the percent change End If and keep a running total prior = rst("Value") rst. Move. Next Save the current value Loop and move to the next row rst. Close Me. txt. Average = avg 1 / n 34

Keys: Create Sales and Items (barcode) Customer ID card is scanned Create new sale Get Sale. ID Scan an item Save sale item, update QOH and totals Save Sale. ID, SKU, Quantity Repeat until done (payment key) 35

Action Create a new form in Design view Add boxes for Customer. ID, Employee. ID, SKU, and txt. Sale. ID as the generated key Create a command button and add the indicated code Test the form Place a breakpoint at the top of the code and step through the code 36

Generate Sale Form IDs and SKU would be scanned, but to test code, set default values 37

Generate Sale Code-1 Dim sql. Sale As String, sql. Item As String, sql. Sale. Item As String Dim rst. Sale As ADODB. Recordset, rst. Model As ADODB. Recordset Dim rst. Sale. Item As ADODB. Recordset Set rst. Sale = Create. Object("ADODB. Recordset") Set rst. Model = Create. Object("ADODB. Recordset") Set rst. Sale. Item = Create. Object("ADODB. Recordset") sql. Sale = "SELECT Sale. ID, Customer. ID, Employee. ID, Sale. Date FROM Sale" sql. Item = "SELECT Model. ID, List. Price FROM Inventory INNER JOIN “ & _ “Item. Model ON Inventory. Model. ID = Item. Model. ID “ & _ “WHERE SKU='" & SKU & "'" sql. Sale. Item = "SELECT Sale. ID, SKU, Sale. Price, Quantity. Sold FROM Sale. Item" Dim cnn As ADODB. Connection Set cnn = Current. Project. Connection 38

Generate Sale Code-2 ' Get the List Price for the SKU rst. Model. Open sqlmodel, cnn, ad. Open. Static, ad. Lock. Read. Only Dim List. Price As Currency List. Price = rst. Model("List. Price") rst. Model. Close ' Open the Sale table and create a new sale rst. Sale. Open sql. Sale, cnn, ad. Open. Dynamic, ad. Lock. Optimistic Dim Sale. ID As Long rst. Sale. Add. New rst. Sale("Sale. Date") = Now rst. Sale("Customer. ID") = Customer. ID rst. Sale("Employee. ID") = Employee. ID Sale. ID = rst. Sale("Sale. ID") rst. Sale. Update rst. Sale. Close 39

Generate Sale Code-3 ' Add the SKU to the Sale. Item table using the new Sale. ID rst. Sale. Item. Open sql. Sale. Item, cnn, ad. Open. Dynamic, ad. Lock. Optimistic rst. Sale. Item("Sale. ID") = Sale. ID rst. Sale. Item("SKU") = SKU rst. Sale. Item("Sale. Price") = List. Price rst. Sale. Item("Quantity. Sold") = 1 rst. Sale. Item. Update rst. Sale. Item. Close txt. Sale. ID = Sale. ID 40

Action Create a new form in Design view Add a combo box to select customers Add a text box to enter a new ZIP Code Create a button and add the indicated code for it Test the form 41

Lock Test Form Combo box to select customer 42

Action Create a Customer. Locks form as a datasheet into the Customer table Run the form Edit a ZIP Code but do not leave the cell Switch to the new form and run it for the same Customer. ID Return to the table and press the Tab key 43

Customer. Lock Test Form 44

Optimistic Lock on the Form Select the first customer, enter a ZIP code and change it Change the ZIP but do not leave the cell Switch back here and Tab out of the cell Error message that value was changed 45

Action Switch to design view on the change form and add the specified error-handling code Open the Customer. Locks form in Design view and set the Record Locks property to Edited Record Run both forms Change a ZIP Code in Customer. Locks Enter a new ZIP Code in the change form for the same customer 46

Pessimistic Lock on Forms Select the first customer, enter a ZIP code and change it Change the ZIP but do not leave the cell The change is not made and the error is trapped because the row is locked 47

Handle Optimistic Locks in Code Retry. Update: rst. Open SQL, cnn, ad. Open. Dynamic, ad. Lock. Optimistic rst("ZIP") = New. ZIPCode rst. Update rst. Close Exit_cmd. New. Zip. Code_Click: Exit Sub Err_cmd. New. Zip. Code_Click: If (Msg. Box(Err. Description, vb. Retry. Cancel) = vb. Cancel) Then Resume Exit_cmd. New. Zip. Code_Click End If rst. Cancel rst. Requery Resume Retry. Update 48