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
 

Home Copyright 2004 FunctionX, Inc.