Home

The Records of a Database: SQL and Data Entry
 

Introduction

With SQL, before performing data entry on a table, you must know how the table is structured, the sequence of its columns, the type of data that each column is made of: it is certainly undesirable to have a numeric value as somebody's first name.

Before performing data entry, you must make sure that the table exists. Otherwise, you would receive a 3192 error:

To enter data in a table, you start with the INSERT combined with the VALUES keywords. The statement uses the following syntax:

INSERT TableName VALUES(Column1, Column2, Column_n)

Alternatively, or to be more precise, you can specify that you are entering data in the table using the INTO keyword between the INSERT keyword and the TableName factor. This is done with the following syntax:

INSERT INTO TableName VALUES(Column1, Column2, Column_n)

The TableName factor must be a valid name of an existing table in the currently selected database. If the name is wrong, the SQL interpreter would simply consider that the table you are referring to doesn't exist. Consequently, you would receive an error.

The VALUES keyword indicates that you are ready to list the values of the columns. The values of the columns must be included in parentheses. Specify the value of each column in the parentheses that follow the VALUES keyword.

If the column is Boolean-based, you must specify its value as 0 or 1.

If the column is a numeric type, you should pay attention to the number you type. If the number is an integer, you should provide a valid natural number without the decimal separator. If the column is for a decimal number, you can type the value with its character separator (the period for US English).

If the data type of a column is a string type, you should include its value between double-quotes if you are using the DoCmd.RunSQL() method of Microsoft Access or you should include it in single-quotes if you are using ADO. For example, a shelf number can be specified as "HHR-604" for DoCmd.RunSQL() or 'HHR-604' for ADO and a middle initial can be given as "D" for Microsoft Access or 'D' for ADO.

If the column was created for a date or a time data type, you should/must use an appropriate formula with the year represented by 2 or 4 digits. You should also include the date in single-quotes. If you want to specify the year with 2 digits, use the formula:

'yy-mm-dd'

Or

'yy/mm/dd'

You can use the dash symbol "-" or the forward slash "/" as the date separator. The year, the month, and the day can each be specified with a single digit. When the year is specified with 1 digit, its number is added to the current decade. For example, a year with 6 is represented as 2006. The 1-year digit formula is suitable for a date that occurs in the current decade. As you may guess, it is better to represent a date with at least two digits, including a leading 0. The 2-year digit formula is suitable for a date that occurs in the current century.

An alternative to representing a year is with 4 digits. In this case, you would use the formulas:

'yyyy-mm-dd'

Or

'yyyy/mm/dd'

The year with 4 digits is more precise as it properly expresses a complete year.

A month from January to September can be represented as 1, 2, 3, 4, 5, 6, 7, 8, or 9. Day numbers follow the same logic.

 

Practical Learning: Introducing SQL Data Entry
  1. To create a new database, on the main menu, click File -> New...
  2. In the New dialog box, click Database and click OK
  3. Change the name of the database to College Park Auto Shop1 and change the folder in the Save In combo box to the folder that contains your exercises
  4. Click Create
  5. To create a new table, on the main menu, click Insert -> Table
  6. In the New Table dialog box, click Table Wizard and click OK
  7. In the Sample Tables, click Orders
  8. In the Sample Fields, double-click OrderID and click Rename Field
  9. Type RepairOrderID and press Enter
  10. Click Next and change the Name to RepairOrders
  11. Click Next
  12. Click the Modify The Table Design radio button and click Finish
  13. Complete the table with the following fields (Don't change the properties that are not specified):
     
    Field Name Data Type Caption
    RepairOrderID    
    CustomerName   Name
    CustomerAddress   Address
    CustomerCity   City
    CustomerState   State
    CustomerZIPCode   ZIP Code
    CarMakeModel   Make/Model
    CarYear   Year
    ProblemDescription Memo  
    Part1Name    
    Part1UnitPrice Number  
    Part1Quantity Number  
    Part1SubTotal Number  
    Part2Name    
    Part2UnitPrice Number  
    Part2Quantity Number  
    Part2SubTotal Number  
    Part3Name    
    Part3UnitPrice Number  
    Part3Quantity Number  
    Part3SubTotal Number  
    Part4Name    
    Part4UnitPrice Number  
    Part4Quantity Number  
    Part4SubTotal Number  
    Part5Name    
    Part5UnitPrice Number  
    Part5Quantity Number  
    Part5SubTotal Number  
    JobPerformed1    
    JobPrice1 Number  
    JobPerformed2    
    JobPrice2 Number  
    JobPerformed3    
    JobPrice3 Number  
    JobPerformed4    
    JobPrice4 Number  
    JobPerformed5    
    JobPrice5 Number  
    TotalParts Number Total Parts
    TotalLabor Number Total Labor
    TaxRate Number Tax Rate
    TaxAmount Number Tax Amount
    RepairTotal Number Repair Total
    RepairDate Date/Time Repair Date
    TimeReady Date/Time Time Ready
    Recommendations Memo  
  14. Save and close the table
  15. While the table is still selected in the Database window, on the Database toolbar, click New Object: AutoForm
  16. Save the form as RepairOrders
  17. Right-click it and click Form Header/Footer
  18. On the Toolbox, make sure the Control Wizard button is down. Click Command Button and click the bottom right section of the Form Footer section
  19. Follow the section to create a button that would be used to close the form. Set the button's caption to Close and its name to cmdClose
  20. Change its design as follows:
     
  21. Save and close the form

Adjacent Data entry

The most common technique of performing data entry requires that you know the sequence of columns of the table in which you want to enter data. With this subsequent list in mind, enter the value of each field in its correct position.

During data entry on adjacent fields, if you don't have a value for a numeric field, you should type 0 as its value. For a string field whose data you don't have and cannot provide, type two double-quotes to specify an empty field. Imagine you have a table equipped with two string columns. Here is an example that creates a record made of two strings:

Private Sub cmdEnterData_Click()
    DoCmd.RunSQL "INSERT INTO Employees VALUES(""Jimmy"", ""Collen"");"
End Sub

Here are two examples of creating a record with two date values:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE Table Employees (" & _
                 "DateHired Date, " & _
                 "DateModified Date);"
End Sub

Private Sub cmdEnterData_Click()
    DoCmd.RunSQL "INSERT INTO Employees " & _
                 "VALUES(""02/08/2004"", ""16-Aug-05"");"
End Sub
 

Random Data Entry

The adjacent data entry requires that you know the position of each column. The SQL provides an alternative that allows you to perform data entry using the name of a column instead of its position. This allows you to provide the values of fields in any order of your choice.

To perform data entry at random, you must provide a list of the columns of the table in the order of your choice. You can either use all columns or provide a list of the same columns but in your own order. Here is an example:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE Table Employees (" & _
                 "DateHired Date, " & _
                 "FirstName Varchar(20), " & _
                 "MI Char(1), " & _
                 "LastName Varchar(20));"
End Sub

Private Sub cmdEnterData_Click()
    DoCmd.RunSQL "INSERT INTO Employees (" & _
                 "DateHired, FirstName, MI, LastName) " & _
                 "VALUES(#02/08/2004#, ""Walter"", ""G"", ""Theal"");"
End Sub

You don't have to provide data for all columns, just those you want, in the order you want. To do this, enter the names of the desired columns on the right side of the name of the table, in parentheses. The syntax used would be:

INSERT TableName(ColumnName1, Columnname2, ColumnName_n)
VALUES(ValueFormColumnName1, ValueFormColumnName2, ValueFormColumnName_n);

Here is an example:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE Table Employees (" & _
                 "DateHired Date, " & _
                 "FirstName Varchar(20), " & _
                 "MI Char(1), " & _
                 "LastName Varchar(20));"
End Sub

Private Sub cmdEnterData_Click()
    DoCmd.RunSQL "INSERT INTO Employees (" & _
                 "LastName, DateHired, MI, FirstName) " & _
                 "VALUES(""Theal"", #02/08/2004#, ""G"", ""Walter"");"
End Sub

Notice that, during data entry, the columns are provided in an order different than that in which they were created.
 

Practical Learning: Creating a Table
  1. In the Database window, click the Forms button and click New...
  2. Save the form as NewRepairOrder
  3. Double-click the button at the intersection of both rulers the access the Properties window for the form and, in the All tab, change the properties as follows:
    Caption: College Park Auto-Shop - New Repair Order
    Navigation Buttons: No
    Auto Center: Yes
    Min Max Buttons: Min Enabled
    Width: 6.25"
  4. Right-click the form and click Form Header/Footer
  5. Click the Form Header bar and, in the Properties window, set its Height to 0
  6. Design the form as follows:
     
    Control Caption Name Tab Stop Tab Index
    Option Group Repair Identification      
    Text Box Customer Name: txtCustomerName Yes 0
    Text Box Address: txtCustomerAddress Yes 1
    Text Box City: txtCustomerCity Yes 2
    Text Box State: txtCustomerState Yes 3
    Text Box ZIP Code: txtCustomerZIPCode Yes 4
    Text Box Car Make/Model: txtCarMakeModel Yes 5
    Text Box Car Year: txtCarYear Yes 6
    Text Box Problem Description: txtProblemDescription Yes 7
    Option Group Parts Used      
    Text Box Part Name txtPart1Name Yes 8
    Text Box Unit Price txtPart1UnitPrice Yes 9
    Text Box Quantity txtPart1Quandity Yes 10
    Text Box Sub Total txtPart1SubTotal No  
    Text Box   txtPart2Name Yes 11
    Text Box   txtPart2UnitPrice Yes 12
    Text Box   txtPart2Quandity Yes 13
    Text Box   txtPart2SubTotal No  
    Text Box   txtPart3Name Yes 14
    Text Box   txtPart3UnitPrice Yes 15
    Text Box   txtPart3Quandity Yes 16
    Text Box   txtPart3SubTotal No  
    Text Box   txtPart4Name Yes 17
    Text Box   txtPart4UnitPrice Yes 17
    Text Box   txtPart4Quandity Yes 17
    Text Box   txtPart4SubTotal No  
    Text Box   txtPart5Name Yes 18
    Text Box   txtPart5UnitPrice Yes 19
    Text Box   txtPart5Quandity Yes 20
    Text Box   txtPart5SubTotal No  
    Option Group Jobs Performed      
    Text Box Job Performed txtJobPerformed1 Yes 21
    Text Box Job Price txtJobPrice1 Yes 22
    Text Box   txtJobPerformed2 Yes 23
    Text Box   txtJobPrice2 Yes 24
    Text Box   txtJobPerformed3 Yes 25
    Text Box   txtJobPrice3 Yes 26
    Text Box   txtJobPerformed4 Yes 27
    Text Box   txtJobPrice4 Yes 28
    Text Box   txtJobPerformed5 Yes 29
    Text Box   txtJobPrice5 Yes 30
    Option Group Order Summary      
    Text Box Total Parts: txtTotalParts No  
    Text Box Total Labor: txtTotalLabor No  
    Text Box Tax Rate: txtTaxRate Yes 31
    Text Box Tax Amount: txtTaxAmount No  
    Text Box Repair Total: txtRepairTotal No  
    Text Box Repair Date: txtRepairDate Yes 32
    Text Box Time Ready: txtTimeReady Yes 33
    Text Box Recommendations: txtRecommendations Yes 34
  7. Save the form
  8. On the Form Design toolbar, click the Code button
  9. In the empty section of the Code Editor, create the following procedure:
      
    Private Sub CalculateOrder()
    On Error GoTo CalculateOrder_Error
        
    Dim dblPart1UnitPrice As Double, bytPart1Quantity As Byte
    Dim dblPart2UnitPrice As Double, bytPart2Quantity As Byte
    Dim dblPart3UnitPrice As Double, bytPart3Quantity As Byte
    Dim dblPart4UnitPrice As Double, bytPart4Quantity As Byte
    Dim dblPart5UnitPrice As Double, bytPart5Quantity As Byte
    
    Dim dblPart1SubTotal As Double, dblPart2SubTotal As Double, _
        dblPart3SubTotal As Double, dblPart4SubTotal As Double, _
        dblPart5SubTotal As Double
        
    Dim dblJob1Price As Double, dblJob2Price As Double, _
        dblJob3Price As Double, dblJob4Price As Double, _
        dblJob5Price As Double
    
    Dim curTotalParts As Currency, curTotalLabor As Currency
    Dim dblTaxRate As Double, curTaxAmount As Currency
    Dim curRepairTotal As Currency
    
    ' Retrieve the unit price of each part
    dblPart1UnitPrice = CDbl([txtPart1UnitPrice])
    dblPart2UnitPrice = CDbl([txtPart2UnitPrice])
    dblPart3UnitPrice = CDbl([txtPart3UnitPrice])
    dblPart4UnitPrice = CDbl([txtPart4UnitPrice])
    dblPart5UnitPrice = CDbl([txtPart5UnitPrice])
    
    ' Retrieve the quantity specified for each part
    bytPart1Quantity = CByte([txtPart1Quantity])
    bytPart2Quantity = CByte([txtPart2Quantity])
    bytPart3Quantity = CByte([txtPart3Quantity])
    bytPart4Quantity = CByte([txtPart4Quantity])
    bytPart5Quantity = CByte([txtPart5Quantity])
    
    ' Calculate the sub-total of each part
    dblPart1SubTotal = dblPart1UnitPrice * bytPart1Quantity
    dblPart2SubTotal = dblPart2UnitPrice * bytPart2Quantity
    dblPart3SubTotal = dblPart3UnitPrice * bytPart3Quantity
    dblPart4SubTotal = dblPart4UnitPrice * bytPart4Quantity
    dblPart5SubTotal = dblPart5UnitPrice * bytPart5Quantity
    
    ' Display the sub totals in the corresponding text boxes
    [txtPart1SubTotal] = dblPart1SubTotal
    [txtPart2SubTotal] = dblPart2SubTotal
    [txtPart3SubTotal] = dblPart3SubTotal
    [txtPart4SubTotal] = dblPart4SubTotal
    [txtPart5SubTotal] = dblPart5SubTotal
    
    ' Calculate the total spent on parts
    curTotalParts = CCur(dblPart1SubTotal + dblPart2SubTotal + _
                    dblPart3SubTotal + dblPart4SubTotal + _
                    dblPart5SubTotal)
    
    ' Retrive the labor amount of each job performed
    dblJob1Price = CDbl([txtJob1Price])
    dblJob2Price = CDbl([txtJob2Price])
    dblJob3Price = CDbl([txtJob3Price])
    dblJob4Price = CDbl([txtJob4Price])
    dblJob5Price = CDbl([txtJob5Price])
    
    ' Calculate the total labor for this repair
    curTotalLabor = CCur(dblJob1Price + dblJob2Price + dblJob3Price + _
                    dblJob4Price + dblJob5Price)
    
    ' Retrieve the tax rate applied
    dblTaxRate = CDbl([txtTaxRate])
    
    ' Calculate the tax amount based on the tax rate applied
    ' on the parts and the labor
    curTaxAmount = CLng(curTotalLabor + curTotalParts) * (dblTaxRate * 100) / 100
    curRepairTotal = curTotalLabor + curTotalParts + curTaxAmount
    
    ' Displays the values in the Order Summary section
    [txtTotalParts] = curTotalParts
    [txtTotalLabor] = curTotalLabor
    [txtTaxAmount] = curTaxAmount
    [txtRepairTotal] = curRepairTotal
    
    Exit Sub

' If there was a problem, address it here
CalculateOrder_Error:
    ' One of the most common problems that may occur here is if the
    ' user enters an invalid value in a text box. Normally, the database
    ' engine is equipped to take care of that and warn the user because
    ' we specified a format for each text box. But just in case...
    If Err.Number = 94 Then
        MsgBox "Make sure you enter the unit price and the quantity " & _
               "of each part used." & vbCrLf & "Please try again!"
    End If

End Sub
  10. Return to the form and click the first text box under Qty
  11. In the Properties window, click the Events tab and double-click On Lost Focus
  12. Click its ellipsis button and implement the event as follows:
      
    Private Sub txtPart1Quantity_LostFocus()
    CalculateOrder
End Sub
  13. Return to the form and click the second text box under Qty
  14. In the Events tab of the Properties window, double-click On Lost Focus
  15. Click its ellipsis button and implement the event as follows:
      
    Private Sub txtPart2Quantity_LostFocus()
    CalculateOrder
End Sub
  16. Return to the form and click the third text box under Qty
  17. In the Events tab of the Properties window, double-click On Lost Focus
  18. Click its ellipsis button and implement the event as follows:
      
    Private Sub txtPart3Quantity_LostFocus()
    CalculateOrder
End Sub
  19. Return to the form and click the fourth text box under Qty
  20. In the Events tab of the Properties window, double-click On Lost Focus
  21. Click its ellipsis button and implement the event as follows:
      
    Private Sub txtPart4Quantity_LostFocus()
    CalculateOrder
End Sub
  22. Return to the form and click the fifth text box under Qty
  23. In the Events tab of the Properties window, double-click On Lost Focus
  24. Click its ellipsis button and implement the event as follows:
      
    Private Sub txtPart5Quantity_LostFocus()
    CalculateOrder
End Sub
  25. Return to the form and click the first text box under Job Price
  26. In the Events tab of the Properties window, double-click On Lost Focus
  27. Click its ellipsis button and implement the event as follows:
      
    Private Sub txtJob1Price_LostFocus()
    CalculateOrder
End Sub
  28. Return to the form and click the second text box under Job Price
  29. In the Events tab of the Properties window, double-click On Lost Focus
  30. Click its ellipsis button and implement the event as follows:
      
    Private Sub txtJob2Price_LostFocus()
    CalculateOrder
End Sub
  31. Return to the form and click the third text box under Job Price
  32. In the Events tab of the Properties window, double-click On Lost Focus
  33. Click its ellipsis button and implement the event as follows:
      
    Private Sub txtJob3Price_LostFocus()
    CalculateOrder
End Sub
  34. Return to the form and click the fourth text box under Job Price
  35. In the Events tab of the Properties window, double-click On Lost Focus
  36. Click its ellipsis button and implement the event as follows:
      
    Private Sub txtJob4Price_LostFocus()
    CalculateOrder
End Sub
  37. Return to the form and click the fifth text box under Job Price
  38. In the Events tab of the Properties window, double-click On Lost Focus
  39. Click its ellipsis button and implement the event as follows:
      
    Private Sub txtJob5Price_LostFocus()
    CalculateOrder
End Sub
  40. Return to the form and click the Tax Rate text box
  41. In the Events tab of the Properties window, double-click On Lost Focus
  42. Click its ellipsis button and implement the event as follows:
      
    Private Sub txtTaxRate_LostFocus()
    CalculateOrder
End Sub
  43. Return to Microsoft Access
  44. In the Toolbox, make sure the Control Wizards button is down .
    Click Command Button and click under the Form Footer bar
  45. In the first page of the Command Button Wizard, click Form Operations in the Categories list
  46. In the Actions list, click Close Form and click Next
  47. In the second page of the wizard, change the string in the top text box to Close and click Next
  48. Change the Name to cmdClose and click Finish
  49. Switch the form to Form View
  50. Save the form
  51. On the Toolbox, click Command Button and click the middle section under the Form Footer bar
  52. When the Command Button Wizard starts, click Cancel and change the properties of the new button as follows:
    Caption: Reset Order
    Name: cmdResetOrder
  53. Right-click the new button and click Build Event
  54. In the Choose Builder dialog box, double-click Code Builder and implement the event as follows:
      
    Private Sub cmdResetOrder_Click()
    txtCustomerName = ""
    txtCustomerAddress = ""
    txtCustomerCity = ""
    txtCustomerState = ""
    txtCustomerZIPCode = ""
    txtCarMakeModel = ""
    txtCarYear = ""
    txtProblemDescription = ""
    txtPart1Name = ""
    txtPart1UnitPrice = "0.00"
    txtPart1Quantity = "0"
    txtPart1SubTotal = "0.00"
    txtPart2Name = ""
    txtPart2UnitPrice = "0.00"
    txtPart2Quantity = "0"
    txtPart2SubTotal = "0.00"
    txtPart3Name = ""
    txtPart3UnitPrice = "0.00"
    txtPart3Quantity = "0"
    txtPart3SubTotal = "0.00"
    txtPart4Name = ""
    txtPart4UnitPrice = "0.00"
    txtPart4Quantity = "0"
    txtPart4SubTotal = "0.00"
    txtPart5Name = ""
    txtPart5UnitPrice = "0.00"
    txtPart5Quantity = "0"
    txtPart5SubTotal = "0.00"
    txtJobPerformed1 = ""
    txtJobPrice1 = "0.00"
    txtJobPerformed2 = ""
    txtJobPrice2 = "0.00"
    txtJobPerformed3 = ""
    txtJobPrice3 = "0.00"
    txtJobPerformed4 = ""
    txtJobPrice4 = "0.00"
    txtJobPerformed5 = ""
    txtJobPrice5 = "0.00"
    txtTotalParts = "0.00"
    txtTotalLabor = "0.00"
    txtTaxRate = "7.75%"
    txtTaxAmount = "0.00"
    txtRepairTotal = "0.00"
    txtRepairDate = ""
    txtTimeReady = ""
    txtRecommendations = ""
End Sub
  55. Return to Microsoft Access
  56. On the Toolbox, click Command Button and click the left section under the Form Footer bar
  57. When the Command Button Wizard starts, click Cancel and change the properties of the new button as follows:
    Caption: Submit Repair Order
    Name: cmdSubmitRepairOrder
  58. Right-click the new button and click Build Event
  59. In the Choose Builder dialog box, double-click Code Builder
  60. To perform adjacent data entry, execute the following statement:
      
    Private Sub cmdSubmitRepairOrder_Click()
    On Error GoTo cmdSubmitRepairOrder_Error
    
    Dim conCPAS As ADODB.Connection
    
    Dim strCustomerName As String, strCustomerAddress As String
    Dim strCustomerCity As String, strCustomerState As String
    Dim strCustomerZIPCode As String, strCarMakeModel As String
    Dim intCarYear As Integer, strProblemDescription As String
    
    Dim strPart1Name As String, strPart2Name As String, _
        strPart3Name As String, strPart4Name As String, _
        strPart5Name As String

    Dim dblPart1UnitPrice As Double, dblPart2UnitPrice As Double, _
        dblPart3UnitPrice As Double, dblPart4UnitPrice As Double, _
        dblPart5UnitPrice As Double
        
    Dim intPart1Quantity As Integer, intPart2Quantity As Integer, _
        intPart3Quantity As Integer, intPart4Quantity As Integer, _
        intPart5Quantity As Integer
        
    Dim dblPart1SubTotal As Double, dblPart2SubTotal As Double, _
        dblPart3SubTotal As Double, dblPart4SubTotal As Double, _
        dblPart5SubTotal As Double
    
    Dim strJobPerformed1 As String, dblJobPrice1 As Double
    Dim strJobPerformed2 As String, dblJobPrice2 As Double
    Dim strJobPerformed3 As String, dblJobPrice3 As Double
    Dim strJobPerformed4 As String, dblJobPrice4 As Double
    Dim strJobPerformed5 As String, dblJobPrice5 As Double
    
    Dim curTotalParts As Currency, curTotalLabor As Currency
    Dim dblTaxRate As Double, curTaxAmount As Currency
    Dim curRepairTotal As Currency, dteRepairDate As Date
    Dim dteTimeReady As Date, strRecommendations As String
                 
    If IsNull([txtCustomerName]) Then
        MsgBox "Please enter the name of the customer to process an order"
        txtCustomerName.SetFocus
        Exit Sub
    End If
    
    If IsNull([txtCustomerAddress]) Then
        strCustomerAddress = ""
    Else
        strCustomerAddress = [txtCustomerAddress]
    End If
    
    If IsNull([txtCustomerCity]) Then
        strCustomerCity = ""
    Else
        strCustomerCity = [txtCustomerCity]
    End If
    
    If IsNull([txtCustomerState]) Then
        strCustomerState = ""
    Else
        strCustomerState = [txtCustomerState]
    End If
    
    If IsNull([txtCustomerZIPCode]) Then
        strCustomerZIPCode = ""
    Else
        strCustomerZIPCode = [txtCustomerZIPCode]
    End If
    
    If IsNull([txtCarMakeModel]) Then
        MsgBox "You must specify the make and the model of the car"
        txtCarMakeModel.SetFocus
        Exit Sub
    End If
    
    If IsNull([txtCarYear]) Then
        intCarYear = 0
    Else
        intCarYear = CInt([txtCarYear])
    End If

    If IsNull(txtProblemDescription) Then
        MsgBox "Make sure you describe the problem that needs " & _
               "to be fixed on the car"
        txtProblemDescription.SetFocus
        Exit Sub
    End If
    
    If IsNull([txtPart1Name]) Then
        strPart1Name = ""
    Else
        strPart1Name = [txtPart1Name]
    End If
    
    If IsNull([txtPart1UnitPrice]) Then
        dblPart1UnitPrice = ""
    Else
        dblPart1UnitPrice = CDbl([txtPart1UnitPrice])
    End If
    
    If IsNull([txtPart1Quantity]) Then
        intPart1Quantity = 0
    Else
        intPart1Quantity = CInt([txtPart1Quantity])
    End If
    
    If IsNull([txtPart1SubTotal]) Then
        dblPart1SubTotal = 0
    Else
        dblPart1SubTotal = CDbl([txtPart1SubTotal])
    End If
    
    If IsNull([txtPart2Name]) Then
        strPart2Name = ""
    Else
        strPart2Name = [txtPart2Name]
    End If
    
    If IsNull([txtPart2UnitPrice]) Then
        dblPart2UnitPrice = 0
    Else
        dblPart2UnitPrice = CDbl([txtPart2UnitPrice])
    End If
    
    If IsNull([txtPart2Quantity]) Then
        intPart2Quantity = 0
    Else
        intPart2Quantity = CInt([txtPart2Quantity])
    End If
    
    If IsNull([txtPart2SubTotal]) Then
        dblPart2SubTotal = 0
    Else
        dblPart2SubTotal = CDbl([txtPart2SubTotal])
    End If
    
    If IsNull([txtPart3Name]) Then
        strPart3Name = ""
    Else
        strPart3Name = [txtPart3Name]
    End If
    
    If IsNull([txtPart3UnitPrice]) Then
        dblPart3UnitPrice = 0
    Else
        dblPart3UnitPrice = CDbl([txtPart3UnitPrice])
    End If
    
    If IsNull([txtPart3Quantity]) Then
        intPart3Quantity = 0
    Else
        intPart3Quantity = CInt([txtPart3Quantity])
    End If
    
    If IsNull([txtPart3SubTotal]) Then
        dblPart3SubTotal = 0
    Else
        dblPart3SubTotal = CDbl([txtPart3SubTotal])
    End If
    
    If IsNull([txtPart4Name]) Then
        strPart4Name = ""
    Else
        strPart4Name = [txtPart4Name]
    End If
    
    If IsNull([txtPart4UnitPrice]) Then
        dblPart4UnitPrice = 0
    Else
        dblPart4UnitPrice = CDbl([txtPart4UnitPrice])
    End If
    
    If IsNull([txtPart4Quantity]) Then
        intPart4Quantity = 0
    Else
        intPart4Quantity = CInt([txtPart4Quantity])
    End If
    
    If IsNull([txtPart4SubTotal]) Then
        dblPart4SubTotal = 0
    Else
        dblPart4SubTotal = CDbl([txtPart4SubTotal])
    End If
    
    If IsNull([txtPart5Name]) Then
        strPart5Name = ""
    Else
        strPart5Name = [txtPart5Name]
    End If
    
    If IsNull([txtPart5UnitPrice]) Then
        dblPart5UnitPrice = 0
    Else
        dblPart5UnitPrice = CDbl([txtPart5UnitPrice])
    End If
    
    If IsNull([txtPart5Quantity]) Then
        intPart5Quantity = 0
    Else
        intPart5Quantity = CInt([txtPart5Quantity])
    End If
    
    If IsNull([txtPart5SubTotal]) Then
        dblPart5SubTotal = 0
    Else
        dblPart5SubTotal = CDbl([txtPart5SubTotal])
    End If

    If IsNull([txtJobPerformed1]) Then
        strJobPerformed1 = ""
    Else
        strJobPerformed1 = [txtJobPerformed1]
    End If
    
    If IsNull([txtJobPrice1]) Then
        dblJobPrice1 = 0
    Else
        dblJobPrice1 = CDbl([txtJobPrice1])
    End If

    If IsNull([txtJobPerformed2]) Then
        strJobPerformed2 = ""
    Else
        strJobPerformed2 = [txtJobPerformed2]
    End If
    
    If IsNull([txtJobPrice2]) Then
        dblJobPrice2 = 0
    Else
        dblJobPrice2 = CDbl([txtJobPrice2])
    End If

    If IsNull([txtJobPerformed3]) Then
        strJobPerformed3 = ""
    Else
        strJobPerformed3 = [txtJobPerformed3]
    End If
    
    If IsNull([txtJobPrice3]) Then
        dblJobPrice3 = 0
    Else
        dblJobPrice3 = CDbl([txtJobPrice3])
    End If

    If IsNull([txtJobPerformed4]) Then
        strJobPerformed4 = ""
    Else
        strJobPerformed4 = [txtJobPerformed4]
    End If
    
    If IsNull([txtJobPrice4]) Then
        dblJobPrice4 = 0
    Else
        dblJobPrice4 = CDbl([txtJobPrice4])
    End If

    If IsNull([txtJobPerformed5]) Then
        strJobPerformed5 = ""
    Else
        strJobPerformed5 = [txtJobPerformed5]
    End If
    
    If IsNull([txtJobPrice5]) Then
        dblJobPrice5 = 0
    Else
        dblJobPrice5 = CDbl([txtJobPrice5])
    End If
    
    If IsNull([txtTotalParts]) Then
        curTotalParts = 0
    Else
        curTotalParts = [txtTotalParts]
    End If
    
    If IsNull([txtTotalLabor]) Then
        curTotalLabor = 0
    Else
        curTotalLabor = CCur([txtTotalLabor])
    End If
    
    If IsNull([txtTaxRate]) Then
        dblTaxRate = 0
    Else
        dblTaxRate = CDbl([txtTaxRate])
    End If
    
    If IsNull([txtTaxAmount]) Then
        curTaxAmount = 0
    Else
        curTaxAmount = CCur([txtTaxAmount])
    End If
    
    If IsNull([txtRepairTotal]) Then
        curRepairTotal = 0
    Else
        curRepairTotal = CCur([txtRepairTotal])
    End If
    
    If IsNull([txtRepairDate]) Then
        dteRepairDate = 0
    Else
        dteRepairDate = CDate([txtRepairDate])
    End If
    
    If IsNull([txtTimeReady]) Then
        dteTimeReady = 0
    Else
        dteTimeReady = [txtTimeReady]
    End If
    
    If IsNull([txtRecommendations]) Then
        strRecommendations = ""
    Else
        strRecommendations = [txtRecommendations]
    End If
    
    ' Just in case the order total was not calculated, do it now
    CalculateOrder
    
    Dim strInsertInto As String, strValues As String
    
    strInsertInto = "INSERT INTO RepairOrders(CustomerName, CustomerAddress, " & _
                    "CustomerCity, CustomerState, CustomerZIPCode, CarMakeModel, " & _
                    "CarYear, ProblemDescription, Part1Name, Part1UnitPrice, " & _
                    "Part1Quantity, Part1SubTotal, Part2Name, Part2UnitPrice, " & _
                    "Part2Quantity, Part2SubTotal, Part3Name, Part3UnitPrice, " & _
                    "Part3Quantity, Part3SubTotal, Part4Name, Part4UnitPrice, " & _
                    "Part4Quantity, Part4SubTotal, Part5Name, Part5UnitPrice, " & _
                    "Part5Quantity, Part5SubTotal, JobPerformed1, JobPrice1, " & _
                    "JobPerformed2, JobPrice2, JobPerformed3, JobPrice3, " & _
                    "JobPerformed4, JobPrice4, JobPerformed5, JobPrice5, " & _
                    "TotalParts, TotalLabor, TaxRate, TaxAmount, RepairTotal, " & _
                    "RepairDate, TimeReady, Recommendations)"
    
    strValues = "VALUES(""" & [txtCustomerName] & " "", """ & strCustomerAddress & """, """ & _
                        strCustomerCity & """, """ & strCustomerState & """, """ & _
                        strCustomerZIPCode & """, """ & [txtCarMakeModel] & """, """ & _
                        intCarYear & """, """ & [txtProblemDescription] & """, """ & _
                        strPart1Name & """, """ & dblPart1UnitPrice & """, """ & _
                        intPart1Quantity & """, """ & dblPart1SubTotal & """, """ & _
                        strPart2Name & """, """ & dblPart2UnitPrice & """, """ & _
                        intPart2Quantity & """, """ & dblPart2SubTotal & """, """ & _
                        strPart3Name & """, """ & dblPart3UnitPrice & """, """ & _
                        intPart3Quantity & """, """ & dblPart3SubTotal & """, """ & _
                        strPart4Name & """, """ & dblPart4UnitPrice & """, """ & _
                        intPart4Quantity & """, """ & dblPart4SubTotal & """, """ & _
                        strPart5Name & """, """ & dblPart5UnitPrice & """, """ & _
                        intPart5Quantity & """, """ & dblPart5SubTotal & """, """ & _
                        strJobPerformed1 & """, """ & dblJobPrice1 & """, """ & _
                        strJobPerformed2 & """, """ & dblJobPrice2 & """, """ & _
                        strJobPerformed3 & """, """ & dblJobPrice3 & """, """ & _
                        strJobPerformed4 & """, """ & dblJobPrice4 & """, """ & _
                        strJobPerformed5 & """, """ & dblJobPrice5 & """, """ & _
                        curTotalParts & """, """ & curTotalLabor & """, """ & _
                        dblTaxRate & """, """ & curTaxAmount & """, """ & _
                        curRepairTotal & """, """ & dteRepairDate & """, """ & _
                        dteTimeReady & """, """ & strRecommendations & """);"
    
    Set conCPAS = Application.CurrentProject.Connection
    conCPAS.Execute strInsertInto & " " & strValues
    MsgBox "The new repair order has been added to the database"
    
    ' Reset the form in case the user wants to create a new record
    cmdResetOrder_Click()

    Exit Sub
    
cmdSubmitRepairOrder_Error:
    MsgBox "There was a problem processing this order" & vbCrLf & _
           "Please call the IT Support team and report the error as" & vbCrLf & _
           CStr(Err.Number) & ": " & Err.Description
    Resume Next
End Sub
  61. Return to Microsoft Access
  62. Switch the form to Form View
     
    College Park Auto-Shop - New Repair Order
  63. Save and close the form
  64. Close the database
 
Previous Copyright © 2005-2016, FunctionX Next