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)
JobName
COntactName
ContactPhone
ContactEmail

thank you very much in advance,
mrotor
mainrotorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ThommyCommented:
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
0
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.
0
Helen FeddemaCommented:
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
      .AddNew
      lngStaffID = ![StaffID]
      .Update
      .Close
   End With
   
   'Add a new linking record with ProjectID and StaffID
   With rstProjectStaff
      .AddNew
      ![ProjectID] = lngProjectID
      ![StaffID] = lngStaffID
      .Update
      .Close
   End With
   
   'Subform is sorted Descending on ProjectStaffID, so it
   'goes to the most recently added record when requeried
   With Me![subTechnicians]
      .Enabled = True
      .Requery
   End With
   
ErrorHandlerExit:
   Exit Sub

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

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mainrotorAuthor Commented:
Scott,
I am using an unbound table.
I will try Thommy's links and Helen's code a try.

mrotor
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.