Duplicating fields with Conditional Formatting.
To make data entry tasks faster in Microsoft Access, we duplicate certain field values (Ctrl+") to bring them forward to the current record from the previous record field, wherever it becomes necessary. If there is more than one field to duplicate the same data into more than one record, then repeating Ctrl+” everywhere is asking for more manual action, even though it is quicker than typing all the information literally.
For example, let us assume that we are creating a Mailing List of all Family members of all the residents in our locality, and the sample data entry Form looks like the image given below:
If there are five members in a family, the next four records Family Code and Address lines must be repeated for each member of the family, as shown in the image below. Only the unique information needs to be keyed in.
A Sample Trial Run - Duplicating Field Values.
Create a new Table with the following structure and save it as FamilyDetails.
Table: FamilyDetails Srl Field Name Data Type Field Size 1 ID AutoNumber 2 FamilyID Integer 3 Title Text 15 4 FName Text 25 5 LName Text 25 6 Add1 Text 50 7 Add2 Text 50 8 City Text 50 9 State Text 50 10 Country Text 50 11 PIN Text 10 Use the Form Wizard to create a Form in Column format, as shown in the image above, using the FamilyDetails Table, and save the Form as frmFamilyDetails.
Create a TextBox (wide enough to type a list of field names separated by commas) in the Form Header as shown in the image above.
Click on the TextBox to select it and display its Property Sheet (F4).
Change the Name Property Value to FieldList.
Create a Command Button below the TextBox, change its Name Property Value to cmdDup, and change the Caption Property Value to Set Carry Forward.
Display the Form’s VBA Module (Design -> Tools -> View Code). Copy and paste the following Code into the Form
Module and save the form:The Form Module Code.
Private Sub cmdDup_Click() 'Set 'CarryForward' Text in the submitted field's TAG property SetTagValue Me, "FieldList" End Sub Private Sub Form_AfterUpdate() 'Set Default Property Value SetDefaultValue Me End Sub Private Sub Form_Current() 'Change color of Duplicated field to Red. SetColorChange Me End Sub
Save and close the Form.
Open the VBA Editing Window (ALT+F11).
Create a new Standard Module (Insert -> Module).
Functions in the Standard Module.
Copy and paste the following VBA Code (consisting of three Functions) into the new Module and save it:
Public Function SetTagValue(ByVal frm As Form, ByVal fldList As String) '--------------------------------------------------------------------- 'Author: a.p.r. pillai 'Date : Feb 2012 'Rights: All Rights Rserved by www.msaccesstips.com '--------------------------------------------------------------------- Dim txt, splt, ctl As Control Dim ctlName As String, ctlType As Integer, j As Integer Dim resetFlag As Boolean, ctrl_name As String resetFlag = False txt = Nz(frm.Controls(fldList).Value, "") ctrl_name = frm.Controls(fldList).Name If Len(txt) = 0 Then resetFlag = True Else 'split the field list sepparate with commas 'and load them into the Array: splt() splt = Split(txt, ",") End If ' Set frm = Me 'initialize all Textboxes and Combobox Tag & Default Value 'Properties, except the FieldList textbox. 'and change all field's Forecolor to black. For Each ctl In frm.Controls ctlType = ctl.ControlType ctlName = ctl.Name If ctlName = ctrl_name Then GoTo nextitem If ctlType = 109 Or ctlType = 111 Then ctl.Tag = "" ctl.DefaultValue = "" ctl.ForeColor = vbBlack End If nextitem: Next frm.Repaint 'show the change color on the form If resetFlag Then 'if the fieldlist textbox was empty then exit Exit Function End If 'Compare each field's name on the form with the field names 'selected and stored in the 'FieldList' Array. For Each ctl In frm.Controls ctlName = ctl.Name ctlType = ctl.ControlType 'control-type 109 is Textbox and 111 is combobox 'only these controls are duplicated If ctlType = 109 Or ctlType = 111 Then For j = 0 To UBound(splt) 'if a match found then change it's Tag Property Value to 'CarryForward'. If Trim(splt(j)) = ctlName Then ctl.Tag = "CarryForward" End If Next End If Next End Function
Public Function SetDefaultValue(ByVal frm As Form) '--------------------------------------------------------------------- 'Author: a.p.r. pillai 'Date : Feb 2012 'Rights: All Rights Rserved by www.msaccesstips.com 'Run this Procedure when the Form_Update() Event Procedure fires. '--------------------------------------------------------------------- Dim ctl As Control For Each ctl In frm.Controls If ctl.ControlType = 109 Or ctl.ControlType = 111 Then 'if a control found with it's Tag Property set with the text 'CarryForward' 'copy the current field value into the Default Property. 'When the new record is created the Default Value will automatically 'appear in the new record. If ctl.Tag = "CarryForward" Then ctl.DefaultValue = Chr$(34) & ctl.Value & Chr$(34) End If 'change the color of the data to black ctl.ForeColor = vbBlack End If Next ctl frm.Repaint End Function
Public Function SetColorChange(ByVal frm As Form) '--------------------------------------------------------------------- 'Author: a.p.r. pillai 'Date : Feb 2012 'Rights: All Rights Rserved by www.msaccesstips.com 'Note : This Function is run from the Form_Current() Event Procedure. '--------------------------------------------------------------------- Dim ctl As Control If frm.NewRecord Then For Each ctl In frm.Controls If ctl.ControlType = 109 Or ctl.ControlType = 111 Then 'Format the copied values with Red Color. If ctl.Tag = "CarryForward" Then ctl.ForeColor = vbRed End If End If Next frm.Repaint End If End FunctionData Entry.
Open the frmFamilyDetails Form in Design View.
Key in your own personal details (or the sample data from the first image above), but don't move to the next New record yet.
Key in the following data field names in the text box, in the header of the Form, separated with commas.
FamilyID,Add1,Add2,City,State,Country,PIN
Set the TextBoxes Child Label Captions to the actual field names (as shown in the above image) so that Users can type the required field names correctly from these labels.
Click on the Command Button.
Note: The Command Button’s Click event procedure calls the SetTagValue() function, passing the Form object and the Textbox name (FieldList) as parameters. This function reads the field list and assigns the text "CarryForward" to each field’s Tag property. Remember, the current record has not yet been updated in the table.
Press Ctrl+S to update the current record, and run the Form_AfterUpdate() procedure runs the SetDefaultValue() function.
The current record field (the Tag Property is assigned the text 'CarryForward') values are copied into the Default Value Property of that field. The default values will appear in those fields in a new record.
Note: The values assigned to the Tag and Default Value properties are stored only while the form is open in Form View, not in Design View. Once the Form is closed, these property values are cleared. Therefore, when you reopen the Form, you must re-enter the list of fields in the TextBox and click the CommandButton again to set the "CarryForward" Text in the Tag Property.
However, if you want certain fields to permanently use the duplication feature for new records, open the form in Design View, enter the text "CarryForward" in the Tag property of those fields, and then save the form. In this case, you no longer need the text box for the field list or the command button on the form header.
Press Ctrl++ to create a new record.
The values we have saved in the Default Value Property (through the Form_AfterUpdate() event procedure) appear in the new record. The Form_Current() event procedure runs too, and the color of the duplicated text changes to red. Now, all that's to do is to key in the rest of the information.
Press Ctrl+S first to update the current record, then press Ctrl+Plus to create a new record.
After keying data in a record, the User can press Ctrl+Plus (click on the New (Blank) Record control on the navigation control at the bottom of the form) to create a new record. This action will also fire the Form_AfterUpdate() event first, followed by the Form_Current() Event.
DateAndSeq.zip
