Microsoft KB Archive/201025

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

Article ID: 201025

Article Last Modified on 1/23/2007

-

APPLIES TO


 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q201025



Moderate: Requires basic macro, coding, and interoperability skills.



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.

NOTE: This problem also occurs while using the Duplicate Record button created by the Command Button Wizard.



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. 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
  Create a module and type the following line in the Declarations section: Option Compare Database Option Explicit   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
  Open the Employees form in design view and insert a command button.   Command Button --     Name: cmdDup Caption: &Duplicate Record OnClick: [Event Procedure]   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 </li> Switch to form view and click the Duplicate Record button.</li></ol>

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.

<div class="status_section">

STATUS
Microsoft has confirmed that this is a problem in Microsoft Access 97. This problem no longer occurs in Microsoft Access 2000.

<div class="moreinformation_section">

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.

You will see the error message described in the "Symptoms" section of this article.

<div class="references_section">