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

• Microsoft Access 97

SYMPTOMS

When you try to paste append a record into a form that has bound fields on a tab control, the following error message may appear:

Some of the field names for the data you tried to paste don't match field names on the form.

CAUSE

Some of the bound fields on the tab control are on the pages that are not currently active.

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, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

http://www.microsoft.com/support/supportnet/overview/overview.asp

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.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access 97. This problem no longer occurs in Microsoft Access 2000.

MORE INFORMATION

Steps to Reproduce Problem

1. Open Northwind.mdb's Employees form.



2. On the Edit menu, click Select Record.



3. On the Edit menu, click Copy.



4. On the Edit menu, click Paste Append.

REFERENCES

For additional information about duplicating the subforms records, click the article number below to view the article in the Microsoft Knowledge Base:

Q132032 ACC: How to Duplicate Main Form and Its Subform Detail Records

Additional query words: pra

Keywords : kbdta FmsProb
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbbug