checking that RS fields don't already exist in table

[alh1002]

I have one base table
then I query from this table to make a record set
I manipulate the data using some looping functions
I then loop thru the array assigning it to a RS.
Then I write this record set to a new table.

What I want is have the write function to check if the data already exists. Example the table to written to "tblNew"
has ladder_date/tdRTp/NAV/id/ytdRTp

if the data already exists I do want the recordset to put in the table.

My ending

loop to assign to table is the code below

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblNew")

Dim i As Integer
i = 0


For i = (LBound(sngArray) + 1) To UBound(sngArray)
.AddNew

!ladder_date = ladderArray(i)
!tdRTp = tdChangeArray(i)
!NAV = navArray(i)
!ytdRTp = sngArray(i)
.Update

Next


End With
rs.Close
Set rs = Nothing

 

[bboffin]

One way is to use the DCount function to check if Ladder_Date already exists:

    ' assuming ladderArray is an array of Date
    If DCount("ladder_date", "tblNew", "ladder_date=#'" & ladderArray(i) & "#") = 0 Then
        .AddNew
        .
        .
    Else
        ' possibly update the record here?
        .
        .
    End If

Bob Boffin

 

[alh1002]

Sorry, I am bit confused on DCount
Is the If going to update table? and the else going to skip it?

When I put my assignment in the
If

If DCount("ladder_date", "tblNew", "ladder_date=" & ladderArray(i) & "") = 0 Then
.AddNew
!ladder_date = ladderArray(i)
!tdRTp = tdChangeArray(i)
!NAV = navArray(i)
!ytdRTp = sngArray(i)
.Update


Else

End If


I tested by having the table have some ladder_dates which already exist in the RS. and this is just adding another record at the bottom of the table. Note the table has one other column id which is Autonumber which is not in the RS.

 

[bboffin]

I think I have misread your spec.

You only want to insert the record if the ladder date DOES exist, is that right?

If so then since DCount will return the number of records that match the criteria, if it returns a non zero value then it's OK to add the record.

The AutoNumber field will look after itself.



Bob Boffin

 

[alh1002]

No, If the ladder date does exist in the table I do not want to insert the record into the table.