How-To: Create a New Record Using ADO

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.

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
 
Home Copyright © 2005 FunctionX, Inc.