preventing duplicate record entry

I have a form, pulling data from a Products table that has many fields
that do lookups to other tables. These are in a one-to-many
relationship to a pk in each of said tables.

I'd like to be able to compare the data on a BeforeUpdate event to be
sure that this data is not duplicated.

Here is the structure:

Products 8-1 Frames
8-1 Color
8-1 Size
8-1 Branch

What I'd like to do:

Sudo-code: If Frames.FrameID AND Color.ColorID AND Size.SizeID AND
Branch.BranchID
Are found in any other recordset THEN
MsgBox "Duplicate Record"
Exit Sub
End If

There are only about 4k records so far in the products table, but I'm
not sure if this will get slower doing it in code as data grows, and
I'm no SQL coder...

Thanks in advance.

.

Relevant Pages

  • Re: preventing duplicate record entry
    ... Why not put a unique index over these 4 fields? ... that do lookups to other tables. ... Are found in any other recordset THEN ... MsgBox "Duplicate Record" ...
    (comp.databases.ms-access)
  • Re: Go to new record on a new day
    ... testing and comparison of all of the lookup methods and DLookup() ... a Recordset with the Recordset actually being the slower of the ... Seek, of course, is useless for lookups on more than one table. ... you can optimize the FindFirst by ...
    (microsoft.public.access.formscoding)
  • Re: Go to new record on a new day
    ... shown that using DLookup is now probably the equal of building a recordset. ... Until Access 2000, I belive, all the domain aggregate functions were ... Seek, of course, is useless for lookups on more than one table. ... you can optimize the FindFirst by ...
    (microsoft.public.access.formscoding)
  • Re: Validating data before adding a recordset
    ... Well spotted Tim - the recordset is actually closed at that point! ... ' Duplicate record found*** ... > Well, in that case the problem is with the rctDriverAdd object, not the ...
    (microsoft.public.access.modulesdaovba)
  • Validating data before adding a recordset
    ... applications, unfortunately, there is only so much you ... The table I am adding the recordset to is set to ... a duplicate record I get an error... ... I can validate the data in the field before it ...
    (microsoft.public.access.modulesdaovba)