1. Start Microsoft Access
2. Click Blank Desktop Database
3. In the File Name text box, type CollegeParkAutoRepair1
4. Click Create
5. Close the default table without saving it
6. On the Ribbon, click CREATE and, in the Forms section, click Form Design
7. Right-click the Form1 tab and click Save
8. Type NewRepairOrder as the name of the form and click OK
9. While the NewRepairOrder form is still in design view, design the form as follows:
    
   

   Control Name Caption Other Properties Label lblMainTitle College Park Auto Repair Fore Color: #FFFFFF Label lblMainTitleShadow College Park Auto Repair Fore Color: Text 1 Option Group   Order Identification   Text Box txtCustomerName Customer Name:   Text Box txtAddress Address:   Text Box txtCity City:   Text Box txtState State:   Text Box txtZIPCode ZIP Code:   Text Box txtMake Make/Model:   Text Box txtModel     Text Box txtCarYear Year:   Text Box txtProblemDescription Problem Description:   Tab Control       Page   pgePartsUsed Parts Used   Label   Part Name   Label   Unit Price   Label   Qty   Label   Sub-Total   Text Box txtPartName1     Text Box txtUnitPrice1   Format: Fixed Text Box txtQuantity1     Text Box txtSubTotal1   Format: Fixed Text Box txtPartName2     Text Box txtUnitPrice2   Format: Fixed Text Box txtQuantity2     Text Box txtSubTotal2   Format: Fixed Text Box txtPartName3     Text Box txtUnitPrice3   Format: Fixed Text Box txtQuantity3     Text Box txtSubTotal3   Format: Fixed Text Box txtPartName4     Text Box txtUnitPrice4   Format: Fixed Text Box txtQuantity4     Text Box txtSubTotal4   Format: Fixed Text Box txtPartName5     Text Box txtUnitPrice5   Format: Fixed Text Box txtQuantity5     Text Box txtSubTotal5   Format: Fixed Page   pgeJobsPerformed Jobs Performed   Label   Job Name   Label   Cost   Text Box txtJobName1     Text Box txtJobCost1   Format: Fixed Text Box txtJobName2     Text Box txtJobCost2   Format: Fixed Text Box txtJobName3     Text Box txtJobCost3   Format: Fixed Text Box txtJobName4     Text Box txtJobCost4   Format: Fixed Text Box txtJobName5     Text Box txtJobCost5   Format: Fixed Text Box txtTotalParts Total Parts:   Text Box txtTotalLabor Total Labor:   Text Box txtTotalRepair Total Repair:   Text Box txtCarYear Year:   Text Box txtRecommendations Recommendations:   Button cmdSaveRepairOrder Save Repair Order   Button cmdResetForm Reset Form   Button cmdClose Close

10. Save the form
11. Right-click the Reset Form button and click Build Event
12. In the Choose Builder dialog box, click Code Builder and click OK
13. Implement the event as follows:

    Private Sub cmdResetForm_Click()
        txtCustomerName = ""
        txtAddress = ""
        txtCity = ""
        txtState = ""
        txtZIPCode = ""
        txtMake = ""
        txtModel = ""
        txtCarYear = ""
        txtProblemDescription = ""
        
        txtPartName1 = "": txtUnitPrice1 = ""
        txtQuantity1 = "": txtSubTotal1 = ""
        txtPartName2 = "": txtUnitPrice2 = ""
        txtQuantity2 = "": txtSubTotal2 = ""
        txtPartName3 = "": txtUnitPrice3 = ""
        txtQuantity3 = "": txtSubTotal3 = ""
        txtPartName4 = "": txtUnitPrice4 = ""
        txtQuantity4 = "": txtSubTotal4 = ""
        txtPartName5 = "": txtUnitPrice5 = ""
        txtQuantity5 = "": txtSubTotal5 = ""
        
        txtJobName1 = "": txtJobCost1 = ""
        txtJobName2 = "": txtJobCost2 = ""
        txtJobName3 = "": txtJobCost3 = ""
        txtJobName4 = "": txtJobCost4 = ""
        txtJobName5 = "": txtJobCost5 = ""
        
        txtTotalParts = "0.00"
        txtTotalLabor = "0.00"
        txtTotalRepair = "0.00"
        txtRecommendations = ""
    End Sub
    
    Private Sub CalculateOrder()
        Dim UnitPrice1 As Double, UnitPrice2 As Double, _
            UnitPrice3 As Double, UnitPrice4 As Double, _
            UnitPrice5 As Double
        Dim Quantity1 As Integer, Quantity2 As Integer, _
            Quantity3 As Integer, Quantity4 As Integer, _
            Quantity5 As Integer
        Dim SubTotal1 As Double, SubTotal2 As Double, _
            SubTotal3 As Double, SubTotal4 As Double, _
            SubTotal5 As Double
        Dim TotalParts As Double, TotalLabor As Double
        Dim JobCost1, JobCost2, JobCost3, JobCost4, JobCost5 As Double
                     
        If txtPartName1 = "" Then
            SubTotal1 = 0
        Else
            SubTotal1 = CDbl(Nz(txtUnitPrice1)) * CInt(Nz(txtQuantity1))
        End If
        
        If txtPartName2 = "" Then
            SubTotal2 = 0
        Else
            SubTotal2 = CDbl(Nz(txtUnitPrice2)) * CInt(Nz(txtQuantity2))
        End If
        
        If txtPartName3 = "" Then
            SubTotal3 = 0
        Else
            SubTotal3 = CDbl(Nz(txtUnitPrice3)) * CInt(Nz(txtQuantity3))
        End If
        
        If txtPartName4 = "" Then
            SubTotal4 = 0
        Else
            SubTotal4 = CDbl(Nz(txtUnitPrice4)) * CInt(Nz(txtQuantity4))
        End If
        
        If txtPartName5 = "" Then
            SubTotal5 = 0
        Else
            SubTotal5 = CDbl(Nz(txtUnitPrice5)) * CInt(Nz(txtQuantity5))
        End If
        
        If txtJobCost1 = "" Then
            JobCost1 = 0
        Else
            JobCost1 = CDbl(Nz(txtJobCost1))
        End If
        If txtJobCost2 = "" Then
            JobCost2 = 0
        Else
            JobCost2 = CDbl(Nz(txtJobCost2))
        End If
        If txtJobCost3 = "" Then
            JobCost3 = 0
        Else
            JobCost3 = CDbl(Nz(txtJobCost3))
        End If
        If txtJobCost4 = "" Then
            JobCost4 = 0
        Else
            JobCost4 = CDbl(Nz(txtJobCost4))
        End If
        If txtJobCost5 = "" Then
            JobCost5 = 0
        Else
            JobCost5 = CDbl(Nz(txtJobCost5))
        End If
        
        TotalParts = SubTotal1 + SubTotal2 + SubTotal3 + _
                     SubTotal4 + SubTotal5
        TotalLabor = JobCost1 + JobCost2 + JobCost3 + JobCost4 + JobCost5
        
        txtTotalParts = TotalParts
        txtTotalLabor = TotalLabor
        txtTotalRepair = FormatNumber(TotalParts + TotalLabor)
    End Sub

14. In the Object combo box, select txtUnitPrice1
15. In the Procedure combo box, select LostFocus
16. Implement the event as follows:

    Private Sub txtUnitPrice1_LostFocus()
        If (txtPartName1 <> "") And (txtUnitPrice1 <> "") Then
            txtQuantity1 = "1"
            txtSubTotal1 = txtUnitPrice1
            CalculateOrder
        End If
    End Sub

17. In the Object combo box, select txtQuantity1
18. In the Procedure combo box, select LostFocus
19. Implement the event as follows:

    Private Sub txtQuantity1_LostFocus()
        If txtPartName1 <> "" Then
            txtSubTotal1 = CDbl(Nz(txtUnitPrice1)) * CInt(Nz(txtQuantity1))
            CalculateOrder
        End If
    End Sub

20. In the Object combo box, select txtUnitPrice2
21. In the Procedure combo box, select LostFocus
22. Implement the event as follows:

    Private Sub txtUnitPrice2_LostFocus()
        If (txtPartName2 <> "") And (txtUnitPrice2 <> "") Then
            txtQuantity2 = "1"
            txtSubTotal2 = txtUnitPrice2
            CalculateOrder
        End If
    End Sub

23. In the Object combo box, select txtQuantity2
24. In the Procedure combo box, select LostFocus
25. Implement the event as follows:

    Private Sub txtQuantity2_LostFocus()
        If txtPartName2 <> "" Then
            txtSubTotal2 = CDbl(Nz(txtUnitPrice2)) * CInt(Nz(txtQuantity2))
            CalculateOrder
        End If
    End Sub

26. In the Object combo box, select txtUnitPrice3
27. In the Procedure combo box, select On Lost Focus
28. Implement the event as follows:

    Private Sub txtUnitPrice3_LostFocus()
        If (txtPartName3 <> "") And (txtUnitPrice3 <> "") Then
            txtQuantity3 = "1"
            txtSubTotal3 = txtUnitPrice3
            CalculateOrder
        End If
    End Sub

29. In the Object combo box, select txtQuantity3
30. In the Procedure combo box, select On Lost Focus
31. Implement the event as follows:

    Private Sub txtQuantity3_LostFocus()
        If txtPartName3 <> "" Then
            txtSubTotal3 = CDbl(Nz(txtUnitPrice3)) * CInt(Nz(txtQuantity3))
            CalculateOrder
        End If
    End Sub

32. In the Object combo box, select txtUnitPrice4
33. In the Procedure combo box, select On Lost Focus
34. Implement the event as follows:

    Private Sub txtUnitPrice4_LostFocus()
        If (txtPartName4 <> "") And (txtUnitPrice4 <> "") Then
            txtQuantity4 = "1"
            txtSubTotal4 = txtUnitPrice4
            CalculateOrder
        End If
    End Sub

35. In the Object combo box, select txtQuantity4
36. In the Procedure combo box, select On Lost Focus
37. Implement the event as follows:

    Private Sub txtQuantity4_LostFocus()
        If txtPartName4 <> "" Then
            txtSubTotal4 = CDbl(Nz(txtUnitPrice4)) * CInt(Nz(txtQuantity4))
            CalculateOrder
        End If
    End Sub

38. In the Object combo box, select txtUnitPrice5
39. In the Procedure combo box, select On Lost Focus
40. Implement the event as follows:

    Private Sub txtUnitPrice5_LostFocus()
        If (txtPartName5 <> "") And (txtUnitPrice5 <> "") Then
            txtQuantity5 = "1"
            txtSubTotal5 = txtUnitPrice5
            CalculateOrder
        End If
    End Sub

41. In the Object combo box, select txtQuantity5
42. In the Procedure combo box, select On Lost Focus
43. Implement the event as follows:

    Private Sub txtQuantity5_LostFocus()
        If txtPartName5 <> "" Then
            txtSubTotal5 = CDbl(txtUnitPrice5) * CInt(txtQuantity5)
            CalculateOrder
        End If
    End Sub

44. In the Object combo box, select txtJobPrice1
45. In the Procedure combo box, select LostFocus
46. Implement the event as follows:

    Private Sub txtJobCost1_LostFocus()
        If (Not IsNull(txtJobName1)) And (Not IsNull(txtJobName1)) Then
            CalculateOrder
        End If
    End Sub

47. In the Object combo box, select txtJobPrice2
48. In the Procedure combo box, select LostFocus
49. Implement the event as follows:

    Private Sub txtJobCost2_LostFocus()
        CalculateOrder
    End Sub

50. In the Object combo box, select txtJobPrice3
51. In the Procedure combo box, select LostFocus
52. Implement the event as follows:

    Private Sub txtJobCost3_LostFocus()
        CalculateOrder
    End Sub

53. In the Object combo box, select txtJobPrice4
54. In the Procedure combo box, select LostFocus
55. Implement the event as follows:

    Private Sub txtJobCost4_LostFocus()
        CalculateOrder
    End Sub

56. In the Object combo box, select txtJobPrice5
57. In the Procedure combo box, select LostFocus
58. Implement the event as follows:

    Private Sub txtJobCost5_LostFocus()
        CalculateOrder
    End Sub

Based on the way you write you code, you can save a file directly or use a dialog box. In Microsoft Windows, the Save File dialog box allows a user to select a folder and give a custom name to a file. The Save File dialog box can be programmatically created as follows:

Private Sub cmdFileDialog_Click()
    Dim dlgSaveFile As FileDialog
    
    Set dlgSaveFile = Application.FileDialog(msoFileDialogSaveAs)
End Sub

1. On the main menu of Microsoft Visual Basic, click Tools -> References
2. Click the check box of Microsoft Office X.x Object Library:
    

   

3. Click OK
4. In the Object combo box, select cmdSaveRepairOrder
5. Implement the event as follows:

   Private Sub cmdSaveRepairOder_Click()
       Dim Action As Integer
       Dim dlgSaveFile As FileDialog
       
       Set dlgSaveFile = Application.FileDialog(msoFileDialogSaveAs)
   
       Action = dlgSaveFile.Show
       
       If Action = 0 Then
           MsgBox "The action was canceled. The file will not be saved"
       Else
           MsgBox "The file will be saved."
       End If
   End Sub

6. Switch to Microsoft Access and switch the form to Form View
7. Click the Save Repair Order button
8. Close or dismiss the dialog box and return to Microsoft Access
9. Switch the form back to Design View
10. Return to Microsoft Visual Basic

In Microsoft Office, file processing, is primarily done using the following syntax:

Open pathname For Output [Access access] [lock] As [#]FileNumber [Len=reclength]

The Open (the name of the procedure) word, the For Output expression, and the As # clause are required.

The first argument, pathname, is a string that can be the name of the file. The file can have an extension or not. The mode specifies the actual action you want to perform, such as creating a new file or only opening an existing one. It can be one of the following keywords:

• Output: The file will be created and ready to receive regular values
• Binary: The file will be created and ready to receive values in binary format (as combinations of 1s and 0s)
• Append: If the file exists already, it will be opened and new values can be added to the end

Here is an example of creating a file:

Private Sub cmdSave_Click()
    Open "example.dat" For Output ...
End Sub

The access argument specifies the types of actions that will be performed on the file, such as writing values to it or only reading existing values. It can have one of the following values:

• Write: After a new file has been created, new values will be written to it
• Read Write: When a new file has been created or an existing file has been opened, values can be read from it or written to it

The lock argument is optional. It indicates how the processor should behave while the file is being used. Its possible values are:

• Shared: Other applications (actually called processes) can access this file while the current application is accessing it
• Lock Write: Do not allow other applications (processes) to access this file while the current application (process) is writing to it
• Lock Read Write: Do not allow other applications (processes) to access this file while the current application (process) is using it

On the right side of #, type a number, for the FileNumber parameter, between 1 and 511.

When you create a file and start using it, or after opening a file and while you are using it, it uses memory and consumes (or can be consuming) memory (which could be significant). When you have finished using the file, you should free the memory it was using and release the resources it was consuming. To assist you with this, the VBA language provides a procedure named Close. Its syntax is:

Close [FileNumberList]

The FileNumberList argument is the FileNumber you would have previously used to create or open the file. Here is an example of closing a file:

Private Sub cmdSave_Click()
    Open "example.dat" For Output As #1
    
    Close #1
End Sub

After creating a file, you may want to write values to it. To support this, the VBA language provides two procedures. One of them is called Print and its syntax is:

Print #FileNumber, [OutputList]

The Print statement takes two arguments but only the first is required.

The FileNumber argument is the FileNumber parameter you would have used to create the file. The FileNumber parameter is followed by a comma.

The OutputList argument can be made of 0, 1 or more parts. Because it is optional, if you don't want to write a value to the file, leave this part empty.

Here is an example of writing some values:

Private Sub cmdSave_Click()
    Open "Employee.txt" For Output As #1
    
    Print #1, "James"
    Print #1, "Larenz"
    Print #1, True
    Print #1, #12/08/2008#
    
    Close #1
End Sub

Instead of writing one value per line, you can write more than one value with one statement. To do this, separate the values with either a semi-colon or an empty space. Here are examples:

Private Sub cmdSave_Click()
    Open "Employee.txt" For Output As #1
    
    REM The values are separated by a semi-colon
    Print #1, "James"; "Larenz"
    REM The values are separated by an empty space
    Print #1, True #12/08/2008#
    
    Close #1
End Sub

Besides the Print procedure, the VBA language also provides a procedure named Write that can be used to write one or more values to a file. The syntax of the Write statement is the same as that of Print:

Write #FileNumber, [OutputList]

The FileNumber argument is required. It must be the FileNumber specified when creating the file.

The OutputList argument is optional. If you want to skip it, type a comma after the FileNumber and end the Write statement. In this case, an empty line would be written to the file. To write the values to the file, follow these rules:

• To start the value with empty spaces, call the Spc() function as mentioned for the Print statement
• The Tab() function is used as it was described for the Print statement
• To write a string, include it in double-quotes
• To write a number, include it normally
• To write a Boolean value, type it as #TRUE# or #FALSE#
• To write a null value, type #NULL#
• To write a date or time value, type it between # and #

Here is an example of writing some values:

Private Sub cmdSave_Click()
    Open "Employee.txt" For Output As #1
    
    Write #1, "James"
    Write #1, "M"
    Write #1, "Larenz"
    Write #1, #12/08/2008#
    Write #1, 24.50
    Write #1, True
    
    Close #1
End Sub

You can also write values on the same line. To do this, separate them with an empty space, a comma, or a semi-colon. Here are examples:

Private Sub cmdSave_Click()
    Open "Employee.txt" For Output As #1

    REM The values are separated by a semi-colon
    Write #1, "James"; "M"; "Larenz"
    REM The values are separated by a comma
    Write #1, #12/08/2008#, 24.50
    Write #1, True

    Close #1
End Sub

1. Change the code of the Click event of the Save Repair Order as follows:

   Private Sub cmdSaveRepairOder_Click()
   On Error GoTo cmdSaveRepairOder_Error
       Dim Action As Integer
       Dim dlgSaveFile As FileDialog
       
       Set dlgSaveFile = Application.FileDialog(msoFileDialogSaveAs)
   
       Action = dlgSaveFile.Show
       
       If Action <> 0 Then
           Open dlgSaveFile.SelectedItems(1) & ".cpr" For Output As #1
       
           Write #1, txtCustomerName
           Write #1, txtAddress
           Write #1, txtCity
           Write #1, txtState
           Write #1, txtZIPCode
           Write #1, txtMake
           Write #1, txtModel
           Write #1, txtCarYear
           Write #1, txtProblemDescription
       
           Write #1, txtPartName1
           Write #1, txtUnitPrice1
           Write #1, txtQuantity1
           Write #1, txtSubTotal1
           Write #1, txtPartName2
           Write #1, txtUnitPrice2
           Write #1, txtQuantity2
           Write #1, txtSubTotal2
           Write #1, txtPartName3
           Write #1, txtUnitPrice3
           Write #1, txtQuantity3
           Write #1, txtSubTotal3
           Write #1, txtPartName4
           Write #1, txtUnitPrice4
           Write #1, txtQuantity4
           Write #1, txtSubTotal4
           Write #1, txtPartName5
           Write #1, txtUnitPrice5
           Write #1, txtQuantity5
           Write #1, txtSubTotal5
       
           Write #1, txtJobName1
           Write #1, txtJobCost1
           Write #1, txtJobName2
           Write #1, txtJobCost2
           Write #1, txtJobName3
           Write #1, txtJobCost3
           Write #1, txtJobName4
           Write #1, txtJobCost4
           Write #1, txtJobName5
           Write #1, txtJobCost5
       
           Write #1, txtTotalParts
           Write #1, txtTotalLabor
           Write #1, txtTotalRepair
           Write #1, txtRecommendations
       
           Close #1
       
       End If
       
       Set dlgSaveFile = Nothing
       cmdResetForm_Click
       
       Exit Sub
       
   cmdSaveRepairOder_Error:
       MsgBox "There is a problem with the repair order. It cannot be saved.", _
              vbOKOnly Or vbInformation, _
              "College Park Auto Repair"
       Resume Next
   End Sub

2. Close Microsoft Visual Basic
3. Close the form
4. When asked whether you want to save, click Yes