Checking duplicates in a table with composite key

[hureeya]

Hello

I need some help and wondering if anyone will be able to help me on this:
I need to import Access tables, through a form (using procedures)

Some tables have composite primary keys ..with 3 to 4 fields. together making the primary keys. like field name : "XID", and "distanceFrom" and "distanceTo" makes one composite primary key.
in that case how can I check to make sure no duplicate records are imported.
I have used the code below to check the Sample_Number as being the primary key for a database but i have to check the "XID", and "distanceFrom" and "distanceTo" at the same time in a record for another database. How can i do that? Sometimes I have to have 4 fields altogether to check.. in a record.

The codes below is what I have used to check duplicates for one key in a record.. and it works perfectly but now i have to do that checking for a composite key
rs.FindFirst ("Sample_Num='" & rsetext.Fields("Sample_Num") & "'")
If (rs.NoMatch) Then
rs.AddNew


Can anyone help..
thanx

 

[boblarson]

Hello

I need some help and wondering if anyone will be able to help me on this:
I need to import Access tables, through a form (using procedures)

Some tables have composite primary keys ..with 3 to 4 fields. together making the primary keys. like field name : "XID", and "distanceFrom" and "distanceTo" makes one composite primary key.
in that case how can I check to make sure no duplicate records are imported.
I have used the code below to check the Sample_Number as being the primary key for a database but i have to check the "XID", and "distanceFrom" and "distanceTo" at the same time in a record for another database. How can i do that? Sometimes I have to have 4 fields altogether to check.. in a record.

The codes below is what I have used to check duplicates for one key in a record.. and it works perfectly but now i have to do that checking for a composite key
rs.FindFirst ("Sample_Num='" & rsetext.Fields("Sample_Num") & "'")
If (rs.NoMatch) Then
rs.AddNew


Can anyone help..
thanx

If you truly have a composite key set then the table will not let you import a duplicate that matches those four fields.

 

[hureeya]

I am not sure what you mean by 'truly'.. what I have in my table is
the "XID", and "distanceFrom" and "distanceTo" together as primary key .

Does that mean i can never import tables that have these 3 fields as primary keys?

 

[hureeya]

do you have any suggestions as what i can do in this case?? Because I do need to import the tables and i do need to check for duplates in those 3 fields.

 

[boblarson]

You can import the data but if any of the records have those 3 fields that match another record that has those three exact same values then it won't import that record, but it will import all of those which do NOT have a match.

 

[petehilljnr]

Can't you just use:

rs.FindFirst ("XID=" & rsetext.Fields("XID") & " AND distanceFrom = "  & rsetext.Fields("distanceFROM") & " AND distanceTo = "  & rsetext.Fields("distanceTo"))

if rs.nomatch ....

This is assuming that XID, distanceFrom and distanceTo are all numeric or else you'll have to add in some single quotes.

Dunno if it's the most efficient way - but I'm guessing it will work!

Pete.