Creating a New Record

Introduction

By default, when you create a form in Microsoft Access, it is equipped with all necessary navigation buttons. For the user to create a new record, he or she must click a New Record button or use the main menu. In many or most applications, you would want a user to be presented with a form with empty controls to create a new record. We have covered this aspect.

An alternative to using the wizard to create a new record is ADO. With ADO, you can create an unbound form, that is, a form whose data is not tied to a table or record. This technique uses a Recordset object.

Practical Learning: 

1. Download the Domba Ice Cream database
2. Start a new blank form and save it as NewCustomerOrder
3. Design the form as follows (any property not mentioned should be ignored) (you can use the Combo Box Wizard to create the combo boxes):
   

   Control
   Form	Caption: Domba Ice Cream - New Customer Order
   	Navigation Buttons: No
   	Min Max Buttons: Min Enabled
   Combo Box	Accompanying Label - Caption: Processed By
   	Name: cboEmployeeID
   	Row Source: SELECT DISTINCTROW Employees.EmployeeID, [FirstName] & " " & [LastName] AS Employee, Employees.Title FROM Employees;
   	Column Count: 3
   	Column Widths: 0";1.25";1.5"
   	Bound Column: 1
   	List Rows: 8
   	List Width: 2.75"
   	Limit To List: Yes
   Text Box	Accompanying Label - Caption: Order Date
   	Name: txtOrderDate
   	Format: Medium Date
   	Input Mask: 99/99/0000;0;_
   Text Box	Accompanying Label - Caption: Week Day
   	Name: txtDayOfWeek
   Text Box	Accompanying Label - Caption: Order Time
   	Name: txtOrderTime
   	Format: Medium Time
   	Input Mask: 00:00;0;_
   Text Box	Accompanying Label - Caption: Period of Day
   	Name: txtPeriodOfDay
   Combo Box	Accompanying Label - Caption: Container
   	Name: cboContainerID
   	Column Count: 2
   	Column Heads: Yes
   	Column Widths: 0";1"
   	Bound Column: 1
   	List Rows: 8
   	List Width: 1"
   	Limit To List: Yes
   Text Box	Accompanying Label - Caption: Scoops
   	Name: txtScoops
   Combo Box	Accompanying Label - Caption: Flavor
   	Name: cboFlavorID
   	Row Source: SELECT DISTINCTROW [Flavors].[FlavorID], [Flavors].[Flavor] FROM [Flavors];
   	Column Count: 2
   	Column Widths: 0";1.5"
   	Bound Column: 1
   	List Rows: 8
   	List Width: 1.5"
   	Limit To List: Yes
   Combo Box	Accompanying Label - Caption: Ingredient
   	Name: cboIngredientID
   	Row Source:
   	Column Count: 2
   	Column Widths: 0";1"
   	Bound Column: 1
   	List Rows: 8
   	List Width: 1"
   	Limit To List: Yes
   Text Box	Accompanying Label - Caption: Notes
   	Name: txtNotes
   Command Button	Name: cmdSubmit
   	Caption: Submit
   Command Button	Name: cmdReset
   	Caption: Reset
   Command Button	Name: cmdClose
   	Caption: Close

4. Right-click the Submit button, click Build Event and double-click Code Builder
5. On the main menu of Visual Basic, click Tools -> References...
6. In the References dialog box, click the check box of your latest Microsoft ActiveX Data Objects driver
    
   
7. Click OK
8. Implement the Click event of the cmbSubmit control as follows:
    

   Private Sub cmbSubmit_Click() On Error GoTo Err_cmbSubmit_Click Dim rstOrder As ADODB.Recordset Set rstOrder = New ADODB.Recordset rstOrder.Open "CustomersOrders", CurrentProject.Connection, adOpenStatic, adLockOptimistic If rstOrder.Supports(adAddNew) Then With rstOrder .AddNew .Fields("EmployeeID") = cboEmployeeID .Fields("OrderDate") = txtOrderDate .Fields("DayOfWeek") = txtDayOfWeek .Fields("OrderTime") = txtOrderTime .Fields("PeriodOfDay") = txtPeriodOfDay .Fields("ContainerID") = cboContainerID .Fields("FlavorID") = cboFlavorID .Fields("IngredientID") = cboIngredientID .Fields("Notes") = txtNotes .Update cmdReset_Click End With End If rstOrder.Close Set rstOrder = Nothing ' DoCmd.Close Exit_cmbSubmit_Click: Exit Sub Err_cmbSubmit_Click: MsgBox Err.Description Resume Exit_cmbSubmit_Click End Sub

9. Right-click the Reset button, click Build Event and double-click Code Builder
10. Implement the event as follows:
      

    Private Sub cmdReset_Click() On Error GoTo Err_cmdReset_Click cboEmployeeID = "" txtOrderDate = "" txtDayOfWeek = "" txtOrderTime = "" txtPeriodOfDay = "" cboContainerID = "" txtScoops = "" cboFlavorID = "" cboIngredientID = "" txtNotes = "" cboEmployeeID.SetFocus Exit_cmdReset_Click: Exit Sub Err_cmdReset_Click: MsgBox Err.Description Resume Exit_cmdReset_Click End Sub

11. Right-click the Close button, click Build Event and double-click Code Builder: 
12. Implement the event as follows:
      

    Private Sub cmdClose_Click() On Error GoTo Err_cmdClose_Click DoCmd.Close Exit_cmdClose_Click: Exit Sub Err_cmdClose_Click: MsgBox Err.Description Resume Exit_cmdClose_Click End Sub

13. Test the application