Solved

I need assistance saving on a click event in ACCESS 2013

Posted on 2014-12-14
4
331 Views
Last Modified: 2014-12-16
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
0
Comment
Question by:mainrotor
4 Comments
 
LVL 19

Assisted Solution

by:Thommy
Thommy earned 250 total points
ID: 40499781
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
 
LVL 84
ID: 40500035
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
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 250 total points
ID: 40500434
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
 

Author Comment

by:mainrotor
ID: 40504126
Scott,
I am using an unbound table.
I will try Thommy's links and Helen's code a try.

mrotor
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question