Microsoft Access Database Development With VBA

Example File Processing Application: College Park Auto-Repair

   

Introduction

File processing gives you the ability to save values or record to tthe computer. To illustrate it, we will create an application used by a fictitious car-repair business.

Practical LearningPractical Learning: Introducing File Processing

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

Saving a File

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

Practical LearningPractical Learning: Introducing the File Dialog Box

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

    Microsoft Office 15.0 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

Creating File

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.

Closing a File

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

Printing to a File

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

Writing Values

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

Practical LearningPractical Learning: Saving a File

  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
 
 
 

The Open File Dialog Box

As opposed to saving a document, Microsoft Windows provides the Open File dialog box used to open a file. To display it, call the FileDialog() method of the Application class and pass the argument as msoFileDialogOpen). Here is an example:

Private Sub cmdFileDialog_Click()
    Dim Action As Integer
    Dim dlgOpenFile As FileDialog
    
    Set dlgOpenFile = Application.FileDialog(msoFileDialogOpen)
    
    Action = dlgOpenFile.Show
End Sub

Reading From a File

To let you create a file, the VBA language provides a procedure named Open. Its syntax is:

Open PathName For Input [Access access] [lock] As [#]FileNumber [Len=RecordLength]

The Open word, the For Input expression, and the As # combination are required. The first argument, PathName, is required. This is a string that can be the name of the file. The file can have an extension or not. The mode is required. To open a file, the mode argument can be:

  • Binary: The file will be opened and its value(s) would be read as (a) binary value(s)
  • Append: The file will be opened and new values can be added to the end of the existing values
  • Input: The file will be opened normally
  • Random: The file will be opened for random access

The access argument is optional and can have one of the following values:

  • Read: After the file has been opened, values will be read from it
  • Read Write: Whether the file was created or opened, values can be read from it and/or written to it

If you decide to specify the access argument, precede its value with the Access keyword. The lock parameter is optional and its possible values are:

  • Shared: Other applications can access this file while the current application is accessing it
  • Lock Read: Other applications are not allowed to access this file while the current application is reading from it
  • Lock Read Write: Other applications are not allowed to access this file while the current application is using it

On the right side of #, type a number for the FileNumber, between 1 and 511. Use the same rules/description we saw for creating a file.

The RecordLength parameter is optional. If the file was opened, this argument specifies the length of the record that was read.

Reading Values

To support the ability to open a file, the VBA language provides two procedures. If you wrote the values using the Print statement, to read the values, use the Input or the Line Input statement. The syntax of the Input procedure is:

Input #FileNumber, VariableList

The FileNumber argument is the FileNumber parameter you would have used to open the file. The FileNumber is followed by a comma. The VariableList argument can be made of 1 or more parts. To read only one value, after the comma of the FileNumber argument, type the name of the variable that will receive the value. You can read each value on its own line. One of the better uses of the Input statement is the ability to read many values using a single statement. To do this, type the variables on the same Input line but separate them with commas. Here is an example:

Private Sub cmdOpen_Click()
    Dim FirstName As String
    Dim LastName As String
    Dim IsFullTimeEmployee As Boolean

    Open "Employee.txt" For Input As #1

    Input #1, FirstName, LastName, IsFullTimeEmployee 
    
    Close #1
End Sub

If you have a file that contains many lines, to read one line at a time, you can use the Line Input statement. Its syntax is:

Line Input #FileNumber, VariableName

This statement takes two arguments and both are required. The FileNumber is the number you would have used to open the file. When the Line Input statement is called, it reads a line of text until it gets to the end of the file. One of the limitations of the Line Input statement is that it has a hard time reading anything other than text because it may not be able to determine where the line ends.

Practical LearningPractical Learning: Introducing File Opening

  1. In the Navigation Pane, right-click the NewRepairOrder form and click Copy
  2. Right-click an empty area in the Navigation Pane and click Paste
  3. Set the name of the form to OpenRepairOrder and click OK
  4. In the Navigation Pane, right-click the OpenRepairOrder form and click Design View
  5. Change the design of the form as follows:
     
    College Park Auto Repair - Opem Repair Order
    Control Name Caption Other Properties
    Button Button cmdOpenRepairOrder Open Repair Order  
    Button Button cmdSaveRepairOrder Save Repair Order Enabled: No
    Button Button cmdClose Close  
  6. Double-click the button at the intersection of the rulers
  7. In the Properties window, click Event and double-click On Load
  8. Click the ellipsis button and change the file as follows:
    Option Compare Database
    
    Private strFileName As String
    
    Private Sub Form_Load()
        strFileName = ""
    End Sub
  9. In the object combo box, select cmdOpenRepairOder
  10. Implement the event as follows:
    Private Sub cmdOpenRepairOder_Click()
    On Error GoTo cmdOpenRepairOder_Error
        Dim Action As Integer
        Dim dlgOpenFile As FileDialog
        
        Dim CustomerName As String, Address As String
        Dim City As String, State As String
        Dim ZIPCode As String, Make As String
        Dim Model As String, CarYear As String
        Dim ProblemDescription As String
        Dim PartName1 As String, UnitPrice1 As String
        Dim Quantity1 As String, SubTotal1 As String
        Dim PartName2 As String, UnitPrice2 As String
        Dim Quantity2 As String, SubTotal2 As String
        Dim PartName3 As String, UnitPrice3 As String
        Dim Quantity3 As String, SubTotal3 As String
        Dim PartName4 As String, UnitPrice4 As String
        Dim Quantity4 As String, SubTotal4 As String
        Dim PartName5 As String, UnitPrice5 As String
        Dim Quantity5 As String, SubTotal5 As String
        Dim JobName1 As String, JobCost1 As String
        Dim JobName2 As String, JobCost2 As String
        Dim JobName3 As String, JobCost3 As String
        Dim JobName4 As String, JobCost4 As String
        Dim JobName5 As String, JobCost5 As String
        Dim TotalParts As String, TotalLabor As String
        Dim TotalRepair As String, Recommendations As String
        
        Set dlgOpenFile = Application.FileDialog(msoFileDialogOpen)
    
        Action = dlgOpenFile.Show
        
        If Action <> 0 Then
            Open dlgOpenFile.SelectedItems(1) For Input As #1
            
            strFileName = dlgOpenFile.SelectedItems(1)
            Caption = "College Park Auto-Repair - Open Repair Order: " & strFileName
            cmdSaveRepairOrder.Enabled = True
        
            Input #1, CustomerName
            Input #1, Address
            Input #1, City
            Input #1, State
            Input #1, ZIPCode
            Input #1, Make
            Input #1, Model
            Input #1, CarYear
            Input #1, ProblemDescription
        
            Input #1, PartName1
            Input #1, UnitPrice1
            Input #1, Quantity1
            Input #1, SubTotal1
            Input #1, PartName2
            Input #1, UnitPrice2
            Input #1, Quantity2
            Input #1, SubTotal2
            Input #1, PartName3
            Input #1, UnitPrice3
            Input #1, Quantity3
            Input #1, SubTotal3
            Input #1, PartName4
            Input #1, UnitPrice4
            Input #1, Quantity4
            Input #1, SubTotal4
            Input #1, PartName5
            Input #1, UnitPrice5
            Input #1, Quantity5
            Input #1, SubTotal5
        
            Input #1, JobName1
            Input #1, JobCost1
            Input #1, JobName2
            Input #1, JobCost2
            Input #1, JobName3
            Input #1, JobCost3
            Input #1, JobName4
            Input #1, JobCost4
            Input #1, JobName5
            Input #1, JobCost5
        
            Input #1, TotalParts
            Input #1, TotalLabor
            Input #1, TotalRepair
            Input #1, Recommendations
        
            If CustomerName = "#NULL#" Then
                txtCustomerName = ""
            Else
                txtCustomerName = CustomerName
            End If
            If Address = "#NULL#" Then
                txtAddress = ""
            Else
                txtAddress = Address
            End If
            If City = "#NULL#" Then
                txtCity = ""
            Else
                txtCity = City
            End If
            If State = "#NULL#" Then
                txtState = ""
            Else
                txtState = State
            End If
            If ZIPCode = "#NULL#" Then
                txtZIPCode = ""
            Else
                txtZIPCode = ZIPCode
            End If
            If Make = "#NULL#" Then
                txtMake = ""
            Else
                txtMake = Make
            End If
            If Model = "#NULL#" Then
                txtModel = ""
            Else
                txtModel = Model
            End If
            If CarYear = "#NULL#" Then
                txtCarYear = ""
            Else
                txtCarYear = CarYear
            End If
            If ProblemDescription = "#NULL#" Then
                txtProblemDescription = ""
            Else
                txtProblemDescription = ProblemDescription
            End If
            
            If PartName1 = "#NULL#" Then
                txtPartName1 = ""
            Else
                txtPartName1 = PartName1
            End If
            If UnitPrice1 = "#NULL#" Then
                txtUnitPrice1 = ""
            Else
                txtUnitPrice1 = UnitPrice1
            End If
            If Quantity1 = "#NULL#" Then
                txtQuantity1 = ""
            Else
                txtQuantity1 = Quantity1
            End If
            If SubTotal1 = "#NULL#" Then
                txtSubTotal1 = ""
            Else
                txtSubTotal1 = SubTotal1
            End If
            
            If PartName2 = "#NULL#" Then
                txtPartName2 = ""
            Else
                txtPartName2 = PartName2
            End If
            If UnitPrice2 = "#NULL#" Then
                txtUnitPrice2 = ""
            Else
                txtUnitPrice2 = UnitPrice2
            End If
            If Quantity2 = "#NULL#" Then
                txtQuantity2 = ""
            Else
                txtQuantity2 = Quantity2
            End If
            If SubTotal2 = "#NULL#" Then
                txtSubTotal2 = ""
            Else
                txtSubTotal2 = SubTotal2
            End If
            
            If PartName3 = "#NULL#" Then
                txtPartName3 = ""
            Else
                txtPartName3 = PartName3
            End If
            If UnitPrice3 = "#NULL#" Then
                txtUnitPrice3 = ""
            Else
                txtUnitPrice3 = UnitPrice3
            End If
            If Quantity3 = "#NULL#" Then
                txtQuantity3 = ""
            Else
                txtQuantity3 = Quantity3
            End If
            If SubTotal3 = "#NULL#" Then
                txtSubTotal3 = ""
            Else
                txtSubTotal3 = SubTotal3
            End If
            
            If PartName4 = "#NULL#" Then
                txtPartName4 = ""
            Else
                txtPartName4 = PartName4
            End If
            If UnitPrice4 = "#NULL#" Then
                txtUnitPrice4 = ""
            Else
                txtUnitPrice4 = UnitPrice4
            End If
            If Quantity4 = "#NULL#" Then
                txtQuantity4 = ""
            Else
                txtQuantity4 = Quantity4
            End If
            If SubTotal4 = "#NULL#" Then
                txtSubTotal4 = ""
            Else
                txtSubTotal4 = SubTotal4
            End If
            
            If PartName5 = "#NULL#" Then
                txtPartName5 = ""
            Else
                txtPartName5 = PartName5
            End If
            If UnitPrice5 = "#NULL#" Then
                txtUnitPrice5 = ""
            Else
                txtUnitPrice5 = UnitPrice5
            End If
            If Quantity5 = "#NULL#" Then
                txtQuantity5 = ""
            Else
                txtQuantity5 = Quantity5
            End If
            If SubTotal5 = "#NULL#" Then
                txtSubTotal5 = ""
            Else
                txtSubTotal5 = SubTotal5
            End If
        
            If JobName1 = "#NULL#" Then
                txtJobName1 = ""
            Else
                txtJobName1 = JobName1
            End If
            If JobCost1 = "#NULL#" Then
                txtJobCost1 = ""
            Else
                txtJobCost1 = JobCost1
            End If
            If JobName2 = "#NULL#" Then
                txtJobName2 = ""
            Else
                txtJobName2 = JobName2
            End If
            If JobCost2 = "#NULL#" Then
                txtJobCost2 = ""
            Else
                txtJobCost2 = JobCost2
            End If
            If JobName3 = "#NULL#" Then
                txtJobName3 = ""
            Else
                txtJobName3 = JobName3
            End If
            If JobCost3 = "#NULL#" Then
                txtJobCost3 = ""
            Else
                txtJobCost3 = JobCost3
            End If
            If JobName4 = "#NULL#" Then
                txtJobName4 = ""
            Else
                txtJobName4 = JobName4
            End If
            If JobCost4 = "#NULL#" Then
                txtJobCost4 = ""
            Else
                txtJobCost4 = JobCost4
            End If
            If JobName5 = "#NULL#" Then
                txtJobName5 = ""
            Else
                txtJobName5 = JobName5
            End If
            If JobCost5 = "#NULL#" Then
                txtJobCost5 = ""
            Else
                txtJobCost5 = JobCost5
            End If
        
            If TotalParts = "#NULL#" Then
                txtTotalParts = ""
            Else
                txtTotalParts = TotalParts
            End If
            If TotalLabor = "#NULL#" Then
                txtTotalLabor = ""
            Else
                txtTotalLabor = TotalLabor
            End If
            If TotalRepair = "#NULL#" Then
                txtTotalRepair = ""
            Else
                txtTotalRepair = TotalRepair
            End If
            
            If Recommendations = "#NULL#" Then
                txtRecommendations = ""
            Else
                txtRecommendations = Recommendations
            End If
            
            Close #1
        
        End If
        
        Set dlgOpenFile = Nothing
        
        Exit Sub
        
    cmdOpenRepairOder_Error:
        MsgBox "There is a problem with the repair order. It cannot be opened.", _
               vbOKOnly Or vbInformation, _
               "College Park Auto Repair"
        Resume Next
    End Sub
  11. In the Object combo box, select cmdSaveRepairOrder
  12. Implement the Click event as follows:
    Private Sub cmdSaveRepairOrder_Click()
    On Error GoTo cmdSaveRepairOder_Error
        Dim Action As Integer
        
        If strFileName <> "" Then
            Open strFileName 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
        
        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
  13. Close Microsoft Visual Basic
  14. Close the form
  15. When asked whether you save, click Yes
 
 
   
 

Home Copyright © 2013 FunctionX, Inc. Home