Excel File Import Button on Form

[Marlene23]

Good Day Everyone!

I am a newbie to Access programming and after two days of searching finally found what I was looking for and management to get it to work but am now stuck with the next steps and are hoping that there might be someone that will be able to assist me with the relevant coding to add.

I created a form with a button for front end users to be able to import an excel spreadsheet. The import works perfectly I do however need assistance with the following:

1. Adding a message somewhere to say "Import Successful" - There is no way you know wether the file imported or not unless you go to the tables or look at the datasheet in the form.
2. A way to stop importing duplicates - The same sheet can be imported multiple times and the data just keeps on adding to the existing data in the tables in other words there are duplicates.
3. Delete the temp table that is created after every import - There is a table created "Name AutoCorrect Save Failures" every time a sheet is imported.

I used the following coding:

In a Module:

Function selectFile()
Dim fd As FileDialog, fileName As String

On Error GoTo ErrorHandler

Set fd = Application.FileDialog(msoFileDialogFilePicker)

fd.AllowMultiSelect = False

If fd.Show = True Then
If fd.SelectedItems(1) <> vbNullString Then
fileName = fd.SelectedItems(1)
End If
Else
'Exit code if no file is selected
End
End If

'Return Selected FileName
selectFile = fileName

Set fd = Nothing

Exit Function

ErrorHandler:
Set fd = Nothing
MsgBox "Error " & Err & ": " & Error(Err)

End Function


And the following code on my import button:

Private Sub cmdImportNoDelete_Click()
'Unset warnings
DoCmd.SetWarnings False

'Import spreadsheet
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tbl_Mentors_Salaries", selectFile, True



DoCmd.SetWarnings True
End Sub


Any assistance with the correct coding and where to insert it would be greatly appreciated!

Thank you in advance!

[June7]

1. MsgBox at end of Sub

2. Why are there duplicates? Is there a unique identifier field(s) that should be set to not allow duplicates?

3. Why are there autocorrect failures? I have imported spreadsheets without this happening. DoCmd.DeleteObject