Message Box When there is a Duplicate Value while Transferring Data Using Transfer Spreadsheet Method (1 Viewer)

JithuAccess

Member
Local time
Today, 12:13
Joined
Mar 3, 2020
Messages
267
Hello Guys,

I am transferring data from Excel spreadsheet to Access Table by using Transfer Spreadsheet method. Is there any way to Display a Warning Message Box if there is any Duplicate values in the Access Table?

For Example, I have an Employee ID Field and the Value of this Field is 100. When I am Transferring data from Excel to Access and if there is 100 in the Employee ID Field of Excel Spreadsheet, I want to display a Warning Message Box when I am clicking on the "Transfer Data" button in my Access Form. Is this possible?

Thanks
 

Ranman256

Well-known member
Local time
Today, 14:13
Joined
Apr 9, 2015
Messages
3,893
transfer to a 'temp' table.
run a query that counts the Qty of EmpID fld. if the query count > 1 then show the query. (that contains the dupes)
if no dupes, run append query to copy the temp tbl to the main table.

qsCountImportData:
select [empID], count([EmpID) from table where count([EmpID)>1

Code:
docmd.transferspreadsheet....
if Dcount("*", "qsCountImportData") > 0 then docmd.openquery "qsCountImportData"
 

JithuAccess

Member
Local time
Today, 12:13
Joined
Mar 3, 2020
Messages
267
transfer to a 'temp' table.
run a query that counts the Qty of EmpID fld. if the query count > 1 then show the query. (that contains the dupes)
if no dupes, run append query to copy the temp tbl to the main table.

qsCountImportData:
select [empID], count([EmpID) from table where count([EmpID)>1

Code:
docmd.transferspreadsheet....
if Dcount("*", "qsCountImportData") > 0 then docmd.openquery "qsCountImportData"
Thanks a lot
 

Users who are viewing this thread

Top Bottom