Add user input to Excel table upon upload to Acces

Question

I'm trying to combine user inputs with existing Excel file data so they will all be included in a single table when they are uploaded to the Access database.

Here is the way I have it laid out, but I'm open to changing it however need be.

I'm just completely stuck with what to do next. Upload Date is automatically filled in, but the rest of the parameters will vary based on the product and will have to be filled in manually. I also want it to be mandatory that every field is filled in, so have an error message saying "Enter all parameters" or something like that if they aren't, which wouldn't allow the upload to be completed.

The reason why this is necessary to do in Access and simply not Excel is because the Excel file is generated by AutoCad Electrical and is limited in what data it can include. I tried adding the columns in Excel and importing them and it worked, but my boss said we NEED the user input box to make things easier.

This is the code I have to import the Excel file and add it to the correct table (_MCL UPLOAD). Now I just want to be able to have the user inputs add to this table as extra columns:

Private Sub ImportMCL_Click()

On Error GoTo ErrorHandler
'disable ms access warnings
DoCmd.SetWarnings False

'load spreadsheet
DoCmd.TransferSpreadsheet acImport, 8, "_MCL UPLOAD", selectFile(), True
MsgBox "MCL Imported Successfully!"
're-enable ms access warnings
DoCmd.SetWarnings True

Exit Sub

ErrorHandler:
MsgBox "There was an Error: " & Err & ": " & Error(Err)


End Sub

Function selectFile()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd
    If .Show Then
        selectFile = .SelectedItems(1)
    Else
        'stop execution if nothing selected
        End
    End If
End With

Set fd = Nothing
End Function

Answer 1

My final code looks like this:

'Import MCL Files Code
Private Sub ImportMCL_Click()

On Error GoTo ErrorHandler
'disable ms access warnings
DoCmd.SetWarnings False

'load spreadsheet in .xls format
DoCmd.TransferSpreadsheet acImport, 8, "_MCL_UPLOAD", selectFile(), True
DoCmd.OpenQuery "UpdateMCL"
Call InsertInto_MASTER_UPLOAD
Call Delete_MCL_UPLOAD
MsgBox "MCL Imported Successfully!"
're-enable ms access warnings
DoCmd.SetWarnings True

Exit Sub

ErrorHandler:
MsgBox "There was an Error: " & Err & ": " & Error(Err)

End Sub

'Function called in Import MCL Code above
Function selectFile()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd
    If .Show Then
        selectFile = .SelectedItems(1)
    Else
        'stop execution if nothing selected
        End
    End If
End With

Set fd = Nothing
End Function

'Function Used to Delete MCL Uploaded file after it's moved to Master Table
Sub Delete_MCL_UPLOAD()

Dim dbs As Database, rst As Recordset

' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("default_cat.mdb")

' Delete employee records where title is Trainee.
dbs.Execute "DELETE * FROM " _
    & "_MCL_UPLOAD "

dbs.Close

End Sub

'Function Appends _MCL UPLOAD into the _MASTER_UPLOAD table
Sub InsertInto_MASTER_UPLOAD()

Dim dbs As Database

' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("default_cat.mdb")

' Select all records in the New Customers table
' and add them to the Customers table.
dbs.Execute " INSERT INTO _MASTER_UPLOAD " _
    & "SELECT * " _
    & "FROM [_MCL_UPLOAD];"

dbs.Close

End Sub

I basically created another table to dump all of the combined information into. Thanks for all of your help!