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!