ACC97: Paste Append Doesn't Work for All Fields in Tab Control (201025)

RESOLUTION

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to the "Building Applications with Microsoft Access 97" manual.

You can use a Visual Basic for Applications (VBA) procedure to add the form's current record into a new record on the form. The VBA procedure will detect tab controls and ensure that the data is inserted even in inactive tabs. You can call the procedure from a button on the form. This procedure can be demonstrated using the Employees form in the sample database Northwind.mdb.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

Type VBA Procedures in a New Module

1. Create a module and type the following line in the Declarations section:

   Option Compare Database
   Option Explicit
   					

2. Type the following procedures:

   Sub DupRecord()
      
      Dim frm As Form, rs As Recordset 'Calling form
      Dim ctr As Control               'controls to loop through
      Dim pg As Page                   'Tab's pagest to loop through
      Dim strFieldName As String       'For testing Field type
   
      Set frm = CodeContextObject   'point to calling form
      Set rs = frm.RecordsetClone   'open copy of form's records
      If frm.NewRecord = True Then  'already in a new record
         MsgBox "This is already a new record. Nothing to duplicate.", , _
           "No saved record to duplicate"
         Exit Sub
      End If
   
      rs.Bookmark = frm.Bookmark 'set recordset to form's current record
      DoCmd.GoToRecord acDataForm, frm.Name, acNewRec 'go to new record
   
      For Each ctr In frm.Controls  'loop through all of the controls
         If VarType(ctr.Parent) = vbObject Then    'parent is a form
            Select Case ctr.ControlType   'check for data bound controls
               Case acOptionButton, acCheckBox, acToggleButton, _
                 acOptionGroup, acBoundObjectFrame, acTextBox, _
                 acListBox, acComboBox
                  If ctr.ControlSource <> "" Then  'bound control
                     If InStr(1, "=[", _
                       Left(ctr.ControlSource, 1)) = 0 Then  'not calc'd
                          strFieldName = ctr.ControlSource 'get fld name
                        If (rs(strFieldName).Attributes _
                          And dbAutoIncrField) = 0 _
                          And (rs(strFieldName).Attributes _
                          And dbUpdatableField) <> 0 _
                          And ctr.Enabled = True _
                          And ctr.Locked = False Then 'ctl is updateable
                           ctr = rs(strFieldName) 'transfer data
                        End If
                     End If 'otherwise this is a calculated control
                  End If      'or an unbound control
               Case acTabCtl  'This control is a Tab Control
                  For Each pg In ctr.Pages  'Loop through each tab
                     RecurseTabPage pg, rs  'Copy current record to page
                  Next
            End Select
         End If
      Next
   End Sub
   					

   Sub RecurseTabPage(pg As Page, rs As Recordset)
   
      Dim ctr As Control, fsub As Form, rsub As Recordset, psub As Page
      Dim strFieldName As String
      
      For Each ctr In pg.Controls
         If VarType(ctr.Parent) = vbObject Then 'isn't option group member
            Select Case ctr.ControlType
               Case acOptionButton, acCheckBox, acOptionGroup, _
                 acBoundObjectFrame, acTextBox, acListBox, acComboBox
                  If ctr.ControlSource <> "" Then  'bound control
                     If InStr(1, "=[", Left(ctr.ControlSource, 1)) _
                       = 0 Then  'not calculated control
                        strFieldName = ctr.ControlSource 'save fld name
                        If (rs(strFieldName).Attributes _
                          And dbAutoIncrField) = 0 _
                          And (rs(strFieldName).Attributes _
                          And dbUpdatableField) <> 0 _
                          And ctr.Enabled = True _
                          And ctr.Locked = False Then
                           ctr = rs(strFieldName)
                        End If
                     End If
                  End If
               Case acTabCtl
                  'if contains other tab controls, recursively copy records
                  For Each psub In ctr.Pages
                     RecurseTabPage psub, rs
                  Next
            End Select
         End If
      Next
   End Sub
   					

Modify the Existing Employees Form

1. Open the Employees form in design view and insert a command button.

      Command Button
      --------------
         Name: cmdDup
         Caption: &Duplicate Record
         OnClick: [Event Procedure]
   					

2. Type or paste the following code into the Click event for the command button.

   Private Sub cmdDup_Click()
   
      On Error GoTo Err_cmdDup_Click
   
      ' Set a constant to the non-Tabbed form
      DupRecord
   
   Exit_cmdDup_Click:
      Exit Sub
   
   Err_cmdDup_Click:
      MsgBox Err.Description, , "Record failed to Duplicate"
      Resume Exit_cmdDup_Click
   
   End Sub
   					

3. Switch to form view and click the Duplicate Record button.

You see that the selected record is pasted as expected, and the form is now on the newly entered record.

NOTE: The procedure does not duplicate the records within subforms. Techniques for duplicating subform records are described in another Knowledge Base article referenced later in this article.