Import from Excel and prevent duplicate entries

[neelsfer]

I use this code to import into the RaceEntry tbl

    Dim strPath As String
 
 
 
    With Me
         strPath = Left(.lblFile.Caption, InStrRev(.lblFile.Caption, "\"))
        strPath = FSBrowse(strStart:=strPath, _
                           lngType:=msoFileDialogFilePicker, _
                           strPattern:="MS Excel,*.xls")
If strPath > "" Then
             .lblFile.Caption = strPath
             MsgBox "Please be patient. All Race Entry Data is now imported into the program ", vbInformation, "Data imported"
           Call DoCmd.SetWarnings(False)
 
              Call DoCmd.TransferSpreadsheet(acImport, acSpreadsheetTypeExcel9, "RaceEntry", strPath, True, "")
 
 
 
            Call DoCmd.SetWarnings(True)
 
End If
     End With

In the RaceEntry table there are 2 "unique" fields namely RaceDate (shortdate field) and RaceNo (number field), that can be used to block duplicate data imports in combination, from an Excel spreadsheet. (both are set to accept duplications)

Ie Racenumber = 50 on RaceDate 31/10/2011, should only appear once in the RaceEntry tbl, but the RaceNumber may appear numerous times in combination with other dates.

A lot of my race entries come from online websites, and it is supplied in Excel format by the web administator. Having these validation checks will save a lot of time.

Is this possible?

[NeoPa]

Neels:
Is this possible?

If you mean "Is it possible to ensure that no duplicate data is entered into the table [RaceEntry]?" then the answer is yes, but you'd need to change the code to import into a temporary table first. Once the data is imported without any checks, the data in the temp table can be tested for validity. You can also report on any invalid data of course at this point, but valid data could then be copied across to your main table.

[neelsfer]

thx i will try that first.