donot allow duplicate records

[dvannoy]

This is a good one..

I have a db that is linked to a DBIV file. The DBVI file are results from an AS400. I need to append the data from the linked table into another table. But If the record has allready been inserted it needs to be skipped. A primary key will not work. I cannot set one of the fields as unique either. I cannot string two fileds together to make a unique field. thats my problem. What it is , is documents #'s will be repeating itself several different times with the same number. with most of the same information. I need to somehow create a sepearte field that I can use as my unique field. but if that document gets saved again in the DBIV file it will be inserted again. how can I get around this??

Thanks in advance dvannoy@onyxes.com

 

[mymou]

Hi dvannoy

In the table you are appending - cant you use a new field which contain a concatination of the fields you need to be unique - and have the field unique in table design?

eg
INSERT INTO Response ( FirstName, Surname, NewField )
SELECT Candidates.FirstName, Candidates.Surname, [FirstName] & [Surname] AS Newfield
FROM Candidates;

If the Newfield cannot be duplicated - this will generate exceptions (that you can just ignore).

Stew

 

[dvannoy]

NO I cant string together fields because it will duplicate some data..and we need to see all the data.. I was trying to use the code below to create a ID for each record. starting with 1 all the time. I cant get it to loop through the recordset. It will only work for the first record.


Dim Db As Database
Dim rs As Recordset
Dim x As Integer

Set Db = DBEngine.Workspaces(0).Databases(0)
Set rs = Db.OpenRecordset("tblName", DB_OPEN_DYNASET)

rs.MoveFirst

Do While Not rs.EOF
x = 1

For x = 1 To rs!ID
rs.Edit
If DCount("id", "tblName&quot = 0 Then
rs![ID] = 1
Else
rs![ID] = DMax("id", "tblName&quot + 1
End If
rs.Update
Next
rs.MoveNext

Loop


End Sub


dvannoy@onyxes.com

 

[mymou]

Hi dvannoy

'I cant string together fields because it will duplicate some data..and we need to see all the data' - dont get.

If you cant use the fields to find the duplicates - then I think you are in trouble.

You might want to check out 'Prevent duplicate values from being entered in a combination of fields' in help

Stew