Import button on a Form needs to add primary key to imported data

[ChrisHannah]

Hello

I need to set up an event and have people upload a list of attendees each time they fill out my form for this event, they will fill out a form and then select a button to import a spreadsheet.
I need to have the primary key associated with the event that i am creating be assigned to each entry that i have imported i make into a new table.

I have a form and button that imports a spreadsheet to a table "StagingTable"
this works fine but i cannot figure out how to assign the primary key to the entries. (i have set up relationships).
my button code is below:

Private Sub Command364_Click()
' This requires a reference to the Microsoft Office 11.0 Object Library.
   Dim fDialog As FileDialog
   Dim varFile As Variant
   ' Set up the File dialog box.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
   With fDialog
      ' Allow the user to make multiple selections in the dialog box.
      .AllowMultiSelect = True
      ' Set the title of the dialog box.
      .Title = "Select Your Excel File"
      ' Clear out the current filters, and then add your own.
      .Filters.Clear
      .Filters.Add "Excel Files", "*.xls;*.xlsx"
      .Filters.Add "All Files", "*.*"

      ' Show the dialog box. If the .Show method returns True, the
      ' user picked at least one file. If the .Show method returns
      ' False, the user clicked Cancel.
      If .Show = True Then
         ' Loop through each file that is selected and then add it to the list box.
         For Each varFile In .SelectedItems
            Me.txtFilePath = varFile
         Next
      Else
         MsgBox "You clicked Cancel in the file dialog box."
      End If
   End With
   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "YourSTAGINGTable", Me.txtFilePath, True
End Sub

 

[Ranman256]

You should have used AutoNum as a primary key. This requires no effort on your part and can't fail.
Building your own keys can create conflict.

 

[missinglinq]

As RanMan256 suggested, this is a no-brainer, using an Autonumber! When you add an Autonumber Field to an established Table (such as one where you've imported a bunch of data) the Access Gnomes automatically create one for each Record.

Linq ;0)>

 

[ChrisHannah]

hello thanks but i am using auto number primary keys for each field that is created, just to explain....

I have a form that creates entries in the table "campaigns"
for each campaign there is an excel file with 30-40 peoples names on it.

I am importing the Excel file with those 30-40 entries in it, the button that imports that is on the form that creates an entry on the table "Campaigns"

Each entry that is imported at each time needs to be associated to the primary key of the entry of the input form i am using.

So copying the primary key from the entry form into the CamapignID field in the incoming excel file so i will be able to say that this particular 30 or so people are associated with this campaign.

i have found that a promary key is not created unless the form is saved so saving it will be part of the function of the button to assign a primary key that can be identified

Thanks