Stop from keying in twice

  • Thread starter Thread starter Bonnie
  • Start date Start date
B

Bonnie

Using A02. I have a Checklog DB and 2 ladies that enter
data from checks received on a DataEntryOnly form. Their
info comes via AWD (paperless) and in no order. We find
they are accidentally entering the same check info and we
end up with duplicate records. I have 3 fields that could
be used to check for dupes: GPNum, CkNum and Amt. Have
never used DLookUp, is that what I want here? What is the
best way to do this? Just want a message to say, "That
record already exists."

Thanks in advance for any and all help and advice. LUV U
GUYS!!!
 
Bonnie said:
Using A02. I have a Checklog DB and 2 ladies that enter
data from checks received on a DataEntryOnly form. Their
info comes via AWD (paperless) and in no order. We find
they are accidentally entering the same check info and we
end up with duplicate records. I have 3 fields that could
be used to check for dupes: GPNum, CkNum and Amt. Have
never used DLookUp, is that what I want here? What is the
best way to do this? Just want a message to say, "That
record already exists."

Thanks in advance for any and all help and advice. LUV U
GUYS!!!
Click to expand...

If some combination of those three fields can be used to identify a
duplicate record, and duplicate records must not be stored, then you can
create a unque index (in the table design) on that combination of
fields. That will prevent duplicate entry, because the index won't
permit keys. An attempt to save a duplicate key will raise an error. I
don't know whether your index should include all three fields, or just
GPNum and CkNum.

If the table in question doesn't have a primary key, you could make this
combination of fields into the primary key, which will automatically be
a unique index. If the table already has some other primary key, then
you can still build a compound key by clicking the Indexes button in
table design view and setting up an index on multiple fields.

The unique index will prevent a duplicate record from being saved, but
the error message isn;t very friendly. If you want to make it more
friendly, you can either trap the form's Error event, identify this
specific error number, and display your own message, or you can use
DLookup, as you suggest, in the form's BeforeUpdate event, to see if
there is already a matching record in the table. If I were you, though,
I would still set the unique index to ensure that a duplicate record
*cannot* make its way into the table.
 
Thanks SO MUCH Dirk! I was able to create that triple
primary key index but had to discover that it only saves
if the table is empty. Ugh! I made a shell, put the index
in and appended my rows back in. I really appreciate the
advice.

And yes, I DO want to put the DLookUp in my BeforeUpdate
event as an extra precaution but have never done one. I
can't figure out how to nest 3 together. Access VB Help
shows an example but uses only one 'if this field equals
that field'. Where could I find an example that uses more
than one field for the DLookUp? Thanks again and you have
been a great help!!!
 
Bonnie said:
Thanks SO MUCH Dirk! I was able to create that triple
primary key index but had to discover that it only saves
if the table is empty. Ugh! I made a shell, put the index
in and appended my rows back in. I really appreciate the
advice.

And yes, I DO want to put the DLookUp in my BeforeUpdate
event as an extra precaution but have never done one. I
can't figure out how to nest 3 together. Access VB Help
shows an example but uses only one 'if this field equals
that field'. Where could I find an example that uses more
than one field for the DLookUp? Thanks again and you have
been a great help!!!
Click to expand...

The trick is to string together the various independent criteria into a
single criteria string for the DLookup, joining them with the "AND"
conjunction. You could have code similar to this:

'----- start of example code -----
Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then

If Not IsNull(DLookup("CkNum","tblCheckLog", _
"GPNum=" & Me.GPNum & _
" AND CkNum=" & Me.CkNum & _
" AND Amt=" & Me.Amt)) _
Then
MsgBox "That record already exists!", _
vbExclamation, "Duplicate Record"
Cancel = True
End If

End If

End Sub
'----- end of example code -----
 
Back
Top