Double data entry is a technique to ensure that the data is correctly
entered. You compare the bound and unbound controls and they must agree
before a record can be saved. I have forms on which I do this. I've
attached the code for the form below.
Some hints on how I do this to save myself SOME of the tedium.
Form Set up (Single record)
Add all the bound controls to the form
Set up the other properties as desired.
Select them all and set
On Lost Focus: =MasterConfirm()
Control Source: = "xx" and then delete that to unbind all the controls
Copy all the bound controls
Paste them into the form and move them below the bound controls
While they are all selected set ther properties
On Lost Focus: =ConfirmEntryAfter()
Tag: DualEntry
Set each unbound control's name to the same as the bound control with the
addition of "Check" at the end.
Use the code below.
You still need to develop code to keep the record from being saved, but you
should be able to do that by using code to step through all the controls
with the dual entry tag and comparing them to their counterpart controls
before you permit the save.
'===============================================================================
' Module : Form_frm_InputForm
' DateTime : 10/16/2006 11:15
' Author : John Spencer
' Notes : This is a dual entry screen. The user must enter certain data
twice
' and the data in the two fields is compared.
' The subordinate controls are unbound and have the same names
as the
' primary controls with the addition of "Check" to the end of
the name.
'
' The subordinate fields also have a tag value of "DualEntry" so
you
' can loop through the controls and reset them as needed - clear
all
' or compare all values
'===============================================================================
Option Compare Database
Option Explicit
Const lBackcolor As Long = -2147483643 '- system setting
Const lLightYellow As Long = 11468799
Private Sub btnCloseForm_Click()
DoCmd.Close acForm, Me.name
DoCmd.OpenForm "frm_SwitchBoard"
End Sub
Private Sub Form_Current()
Me.NavButtons.Form.EnableDisableButtons
End Sub
Private Sub Clearall()
'===============================================================================
' Procedure : Clearall
' Created : 10/16/2006 12:13
' Last Update :
' Author : John Spencer
' Purpose : Clear all dual entry controls
' Arguments : None
'===============================================================================
Dim cntlAny As Control
On Error GoTo ERROR_Clearall
For Each cntlAny In Me.Controls
With cntlAny
If .Tag = "DualEntry" Then
'Dual entry copy
.BackColor = lBackcolor
.FontBold = False
.Value = Null
'Dual entry master
Me.Controls(Left(.name, Len(.name) - 5)).BackColor = lBackcolor
Me.Controls(Left(.name, Len(.name) - 5)).FontBold = False
End If
End With 'cntlAny
Next cntlAny
EXIT_Clearall:
On Error GoTo 0
Exit Sub
ERROR_Clearall:
Select Case Err.Number
Case 9999
'Do something with exceptions
Case Else
LogError Err.Number, Err.Description, "Clearall" _
, "Form_frm_InputForm", True
End Select
End Sub
Private Function ConfirmEntry(ControlName As String)
'===============================================================================
' Procedure : ConfirmEntry
' Created : 10/16/2006
' Last Update :
' Author : John Spencer
' Purpose : Highlight dual entry discrepancies
' Arguments : ControlName is name of dependent control. Name should end
in "check"
' Dependent ControlName is same as the master control with the
' addition of "check" on the end.
'===============================================================================
Dim cntlMaster As Control
Dim cntlCopy As Control
On Error GoTo ERROR_ConfirmEntry
Set cntlCopy = Me.Controls(ControlName)
Set cntlMaster = Me.Controls(Left(ControlName, Len(ControlName) - 5))
If Me.Controls(ControlName).Tag = "DualEntry" Then
If cntlCopy = cntlMaster Or (IsNull(cntlCopy) And IsNull(cntlMaster))
Then
cntlCopy.BackColor = lBackcolor
cntlCopy.FontBold = False
cntlMaster.BackColor = lBackcolor
cntlMaster.FontBold = False
Else
cntlCopy.BackColor = lLightYellow
cntlCopy.FontBold = True
cntlMaster.BackColor = lLightYellow
cntlMaster.FontBold = True
End If
End If
EXIT_ConfirmEntry:
On Error GoTo 0
Exit Function
ERROR_ConfirmEntry:
Select Case Err.Number
Case Else
LogError Err.Number, Err.Description, "ConfirmEntry" _
, "Form_frm_InputForm" & " / ControlName: " & ControlName,
True
End Select
End Function
Private Function ConfirmEntryAfter()
ConfirmEntry Screen.ActiveControl.name
End Function
Private Function MasterConfirm()
ConfirmEntry Screen.ActiveControl.name & "Check"
End Function
