I need assistance saving on a click event in ACCESS 2013

Hi Experts,
I need to save a record in an ACCESS 2013 to my tblJOb table on a button click event.  When I save the record using a VBA sql query, I want it to return The JobID (auto generated key) that was given to the newly created record.  How can I do this?  These are my table fields:

tblJob has the following fields:
JobID  (Primary key - auto generated)

thank you very much in advance,
Who is Participating?
Helen FeddemaConnect With a Mentor Commented:
If you are working with an unbound form, or have some other situation which requires saving a record to a table in VBA code, this code will get the ID of the new record for use elsewhere; in this case it is used to create a new record in a junction table for a many-to-many relationship:

Private Sub cmdAddNewTechnician_Click()

On Error GoTo ErrorHandler

   Dim lngProjectID As Long
   Dim lngStaffID As Long
   Dim rstStaff As DAO.Recordset
   Dim rstProjectStaff As DAO.Recordset
   'Add a new contact record and link it to this job
   Set rstStaff = CurrentDb.OpenRecordset("tblStaff")
   Set rstProjectStaff = CurrentDb.OpenRecordset("tblProjectStaff")
   lngProjectID = Nz(Me![ProjectID])
   'Add a new Staff record and get StaffID
   With rstStaff
      lngStaffID = ![StaffID]
   End With
   'Add a new linking record with ProjectID and StaffID
   With rstProjectStaff
      ![ProjectID] = lngProjectID
      ![StaffID] = lngStaffID
   End With
   'Subform is sorted Descending on ProjectStaffID, so it
   'goes to the most recently added record when requeried
   With Me![subTechnicians]
      .Enabled = True
   End With
   Exit Sub

   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

ThommyConnect With a Mentor Commented:
I can think of 3 ways to get the value of an auto-number field after insert:

1. Simply requery your dataset
Get new autonumber field after insert

2. Try using RowUpdated event
Retrieve Access AutoNumber Value After Insert

3. Use ACCESS Dmax() function and add 1 to the result
Obtaining auto number right after insert stmt
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Are you using bound forms? If you are, then there's no need to use DAO to save the data. Let Access do this instead.
mainrotorAuthor Commented:
I am using an unbound table.
I will try Thommy's links and Helen's code a try.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.