Depending on how you run the appends you could incorporate the number in the INSERT statement itself or do it immediately afterwards using an UPDATE statement as they will be empty for the last appended set of records:
Code:
Dim f As Object 'FileDialog
Dim lNumber as long
Set f = Application.FileDialog(3) 'msoFileDialogFilePicker
With f
.AllowMultiSelect = True 'default
.InitialFileName = "C:\Temp\"
' Specify filters
.Filters.Clear
.Filters.Add "Excel files", "*.xlsx"
.Show
Dim varFile As Variant
For Each varFile In .SelectedItems
'MsgBox Trim(varFile)
lNumber=Replace(Right(varFile,9),".xlsx","")
Docmd.TransferSpreadsheet ........'
'run your append from the new linked table to your final one
CurrentDb.Execute "qryAppendNewDelivery",dbFailOnError
'now update the number field which should be null for the newly added records
CurrentDb.Execute "UPDATE tblYourFinalDeleveriesTable SET YourNumberField = " & lNUmber & " WHERE YourNumberField Is NULL;",dbFailOnError
Next
msgbox "Done"
End With
Cheers,