By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,968 Members | 1,178 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,968 IT Pros & Developers. It's quick & easy.

Import from Excel and prevent duplicate entries

100+
P: 547
I use this code to import into the RaceEntry tbl
Expand|Select|Wrap|Line Numbers
  1.     Dim strPath As String
  2.  
  3.  
  4.  
  5.     With Me
  6.          strPath = Left(.lblFile.Caption, InStrRev(.lblFile.Caption, "\"))
  7.         strPath = FSBrowse(strStart:=strPath, _
  8.                            lngType:=msoFileDialogFilePicker, _
  9.                            strPattern:="MS Excel,*.xls")
  10. If strPath > "" Then
  11.              .lblFile.Caption = strPath
  12.              MsgBox "Please be patient. All Race Entry Data is now imported into the program ", vbInformation, "Data imported"
  13.            Call DoCmd.SetWarnings(False)
  14.  
  15.               Call DoCmd.TransferSpreadsheet(acImport, acSpreadsheetTypeExcel9, "RaceEntry", strPath, True, "")
  16.  
  17.  
  18.  
  19.             Call DoCmd.SetWarnings(True)
  20.  
  21. End If
  22.      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?
Nov 17 '11 #1
Share this Question
Share on Google+
2 Replies


NeoPa
Expert Mod 15k+
P: 31,709
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.
Nov 17 '11 #2

100+
P: 547
thx i will try that first.
Nov 18 '11 #3

Post your reply

Sign in to post your reply or Sign up for a free account.