Please help with double entries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have put together a Questionnaire database that can accommodate double
entries, where the user must make 2 entries for each value. The first entry
will go to one table and the second will go to a twin copy of the same table.
Entries do not have necessarily be entered in the same time they can by days
or weeks apart. I have 15 questionnaires that means 30 tables with hundreds
of questions. It is a nightmare to place a code in the "after update event"
for every field on different forms to compare the 2nd entry with the first
one and see if they were different or the same. Is there a way to check for
that without having to place the code in every field (text box). This is a
scientific research and that is how they want the database structured. Can
anyone help.
Thanks

Al
 
Hi Al,

There's no way out of putting *some* code in an event procedure for all
the controls, but you can minimise it.

The idea is to create one validation function that takes information
from the active form and control, does whatever lookups and comparison
are necessary, and then displays a message for the user before returns
(say) True, False or Null for same, different, and not entered yet.

A basic form of the function might look like this (ID being the primary
key):

DoubleEntryCorrect(TableName As String, FieldName As String, _
ID As Long, TheValue As String) As Variant

Dim blTemp As Variant
'Is there a corresponding record in the other table?
If DCount(ID, TableName, "ID=" & ID) = 0 Then
'not found
blTemp = Null
MsgBox "There is no record for " & ID " in " & TableName, _
vbInformation

ElseIf DLookup(FieldName, TableName, "ID=" & ID) = TheValue Then
'values match
blTemp = True
MsgBox = "Matching record in " & TableName, vbInformation
Else
'they don't match
blTemp = False
MsgBox "WARNING: non-matching record in " & TableName, _
vbExclamation
End If
DoubleEntryCorrect = blTemp

The next stage is to generalise the arguments that the function needs.
For instance, when calling the function,
Me.ActiveControl.ControlSource
will normally return the field name, so you can use this every time
rather than having to enter the literal name. Likewise, if the form is
bound to a table or query
Me.RecordSource
will return its name.

If you need to do more than alert the user - e.g. open another form that
displays the two non-matching records for comparison - you can get full
access to the calling form within the shared validation procedure by
passing the form itself as an argument, e.g.

DoubleEntryCorrect(F As Form)

after you can get FieldName from F.ActiveControl.ControlSource,
TableName from F.RecordSource, and so on.
 
Thank you John. This certainly helps. Is there a way, though, to load this
generalized code to all controls on the form, say, in the on load even of the
form itself. What I mean is to have a code in the "ON Load" event of the form
that adds the doubleentry function to all controls or the active one?
thanks
Al
 
I've never done it myself, but you can modify the code in form and other
modules using
Application.Modules("Name").Lines()
, and
DoCmd.OpenForm "Name", acDesign
opens a form in design mode.

Alternatively you can use the (undocumented) Application.SaveAsText
method to export each form (design and code) to a text file ready for
manipulation with your favourite scriptable editing tool.
Application.LoadFromText will then import the modified file.

None of these are suitable for use in the ordinary operation of an
application, but they do let you write code to generate code or set
properties.
 
Back
Top