Duplicate checking...

[Guest]

Is there a way to query the last record of a table and compare it to the
current data entered in a form? What I was thinking was to use the before
update event to verify that the new entry won't be a duplicate of the last
one.

What I have is a table with 8 fields. I won't need to check against two of
the fields since one is calculated based on the date entered and the other is
a primary key.

Mytable: PK, office(text), refdate(date), prog(text), new(integer),
rpt(integer), wk(integer) (emp contains a comma between last and first name)

The logic is a snap but method has me stumped. Any help would be greatly
appreciated. (my apologies if this should be in the Forms coding group)

 

[Dirk Goldgar]

Is there a way to query the last record of a table and compare it to
the current data entered in a form? What I was thinking was to use
the before update event to verify that the new entry won't be a
duplicate of the last one.

What I have is a table with 8 fields. I won't need to check against
two of the fields since one is calculated based on the date entered
and the other is a primary key.

Mytable: PK, office(text), refdate(date), prog(text), new(integer),
rpt(integer), wk(integer) (emp contains a comma between last and
first name)

The logic is a snap but method has me stumped. Any help would be
greatly appreciated. (my apologies if this should be in the Forms
coding group)

The only hitch is that you need some way to identify what is the "last"
record. Records aren't necessarily retrieved from tables in the order
they were entered, so if the records aren't stamped with the date and
time of entry, there's no real way to know which record is the last one.
Maybe the refdate field contains this information, but if it's just the
date alone, it won't do. If the primary key is a consecutive
autonumber, it may work for this purpose, but it's not really a great
idea to rely on this fact, since autonumbers really only exist for the
purpose of providing a unique key, and you aren't to rely on their
specific values. If your database is ever replicated, for instance, the
autonumbers will switch from consecutive to random.

Supposing that refdate contains both the date and time of entry -- which
may not be true -- you could use logic like this:

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

Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim blnSameData As Boolean

' Get record with the maximum refdate on on file.
Set rs = CurrentDb.OpenRecordset( _
"SELECT office, prog, new, rpt, wk, emp " & _
"FROM MyTable " & _
"WHERE refdate = (SELECT Max(refdate) FROM MyTable)")

blnSameData = True ' Assume for the moment that this is a
duplicate

For Each fld In rs.Fields
If Nz(fld.Value) <> Nz(Me.Controls(fld.Name)) Then
blnSameData = False
Exit For
End If
Next fld

rs.Close
Set rs = Nothing

If blnSameData Then
Cancel = True
MsgBox "This record is the same as the last one entered!"
End If

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

That may not be exactly right, but something along those lines should
work.

An alternative to this approach, if you're only concerned with
duplicates entered in the same editing session, is to capture each
record's field values to form-level variables in the forms AfterUpdate
event, and just compare to those variables in the form's BeforeUpdate
event.

 

[Guest]

Thanks Dirk!

I used the second suggestion since the date field is actually an ocxCalendar
selection and the primary key is autonumber(although it does get reset to 1
after each data export/table wipe) and it worked nicely.