The Records of a Database

After creating a table and its column(s), you can populate the database with data. You and the user can use either the table or the form but as mentioned previously, the form is sometimes the appropriate object to do this. Data entry consists of filling a database with the necessary values. A series of values that corresponds to same levels of columns is called a row or a record.

To enter data in a table, after displaying it in the Datasheet View, the user can click a box under a column and type the necessary value. After entering data in a box, the user can press Tab or Enter to move to the next box on the right. Whenever the user enters a value and moves to the next box, Microsoft Access automatically saves that value and it becomes part of the record. When either all horizontal boxes have been covered or the user simply decides to move to another horizontal range of boxes, he or she is said to have created a record. In summary, a record is a series of the same horizontal boxes. A record is actually represented as a row. The intersection of a column and a row is called a cell. This means that data is actually entered into cells. New Record Addition By default, if you generate a form based on a table and intended for data entry, all records would be accessible. When it's time to add a new record, the user must move from one record to an empty one. An alternative is to create a form specially made for data entry. This type of form comes up with empty controls and expects the user to simply enter the new values. To create a form for direct data entry, you have many alternatives. One of the techniques you can use to create a data entry-only form is as follows: Generate a form based on the table that holds the information Set its Navigation Buttons property to No and its Data Entry property to Yes You can also allow the user to open a form for data entry, that is, to a new record. To do this, you can call the GoToRecord() method of the DoCmd object. The syntax of this method is: GoToRecord(ObjectType, ObjectName, Record, Offset) The first argument to this method must be a constant value. In this case, it would be acDataForm. If you are calling it to act on the current form, you can set this argument to acActiveDataObject. In this case, you can omit this argument. The second argument is the name of the form to which the new record will be added. If the record is being added to the same form that is making the call, you can omit this argument. The third argument specifies the action that would be performed. This argument holds a constant value. In the case of adding a new record, the value of this argument would be acNewRec. The last argument has to do with other values of the third argument. Here is an example that opens a form named Customers at a new record: Private Sub cmdAddCustomer_Click() DoCmd.OpenForm "Customers1" DoCmd.GoToRecord acDataForm, "Customers", acNewRec End Sub Instead of writing this code, you can use the Command Button Wizard where you would select Record Operations followed by Add New Record. Practical Learning: Creating a Data Entry Form Open the GCS2 database you created in the previous lesson In the Forms section of the Database window, right-click CleaningOrders and click Save As... In the Save Form To text box, change the name to NewCleaningOrder and click OK Right-click NewCleaningOrder and click Design View 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: Georgetown Cleaning Services - New Cleaning Order Data Entry: Yes Navigation Buttons: No Switch the form to Form View   Save the form Click Customer Phone Type 1026882250 and press Tab In the Customer Name, type Telanie Marba Click the arrow of the Date Left and select a data that corresponds to June 12, 2002 Set the Time Left to 10:15 AM Set the Date Expected by adding 1 day to the date left Set the Time Expected to 8AM Click Tax Rate and type 0.0575 Complete the cleaning order with the following values:   Item Unit Price Qty Shirts 1.75 4 Pants 2.65 2 Jacket 3.25 1 Tie 2.25 5   Close the form 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 To create a new database, on the main menu, click File -> New... In the New dialog box, click Database and click OK 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 Click Create To create a new table, on the main menu, click Insert -> Table In the New Table dialog box, click Table Wizard and click OK In the Sample Tables, click Orders In the Sample Fields, double-click OrderID and click Rename Field Type RepairOrderID and press Enter Click Next and change the Name to RepairOrders Click Next Click the Modify The Table Design radio button and click Finish Complete the table with the following fields (Don't change the properties that are not specified):   Field Name Data Type Caption RepairOrderID   Receipt Number 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   Save and close the table While the table is still selected in the Database window, on the Database toolbar, click New Object: AutoForm Save the form as RepairOrders Right-click it and click Form Header/Footer 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 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 Change its design as follows:   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
     
    
63. Save and close the form
64. Close the database

Record Navigation   Introduction There are two main types of views a user displays when using a database. One view is referred to as data sheet because it displays a series of columns and rows. This is the regular view of a table. Here is an example: This type of view displays as many records as it can afford, based on its dimensions. To navigate among records, the user can click the Navigation Buttons in the bottom section of the window. This moves the caret from one record to the next or from a record to the previous one. When the view displays the last record, it would not move further. To move a particular record based on its number, the user can type an index, such as 12, in the text box located between the navigation buttons and press Enter. Besides the data sheet, another view consists of displaying one record at a time. This is the regular view used with forms: Once again, to move from one record to another, the user can click the buttons in the bottom section of the form. Object Role First Record: allows moving to the first record Previous Record: allows moving one record back (if there is one) from the current record Record Indicator: Displays the number representing the current record Next Record: allows moving one record ahead Last Record: Allows moving to the last record New Record: Used to enter a new record for a form Since you cannot create a new record on a report, this button is not available on it   Programmatic Navigation Most users know how to use navigation buttons to move among records. Some others don't. This means that, sometimes, you will need a way to make it easy for the user to perform this navigation. Fortunately, Microsoft Access provides everything you need to do this. To perform navigation programmatically, the DoCmd object is equipped with the GoToRecord method that we reviewed earlier. After specifying the first argument as acDataForm and the second argument with the name of the form, you use the third argument to specify what record to jump to. The values of this third argument can be: Value Description Same as Clicking acFirst Navigates to the first record acPrevious  Navigates to the previous record acNext Navigates to the Next record acLast Navigates to the last record acGoTo Navigates to a record. In this case, pass a number as the fourth argument acNewRec Opens a new empty record To assist you with creating your own navigation buttons, Microsoft Access provides the Button Wizard. To use it, with the Control Wizards button down, after adding a Command Button to the form, when the Command Button Wizard, in the first page, select Record Navigation (it should be selected by default): In the Actions list, you can select the type of navigation you want the new button to perform. In the second page, you have the option of using a suggested bitmap for the button or you can enter a caption in the top text box and click Next. In the third page, you can enter a name for the button and click Finish. At the end, the wizard will have code for you that essentially calls the DoCmd.GoToRecord method. Here is an example: Private Sub cmdNextStudent_Click() On Error GoTo Err_cmdNextStudent_Click DoCmd.GoToRecord , , acNext Exit_cmdNextStudent_Click: Exit Sub Err_cmdNextStudent_Click: MsgBox Err.Description Resume Exit_cmdNextStudent_Click End Sub This code is used to move to the next record.

 

Previous	Copyright © Yevol, 2007	Next