Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

I need assistance saving on a click event in ACCESS 2013

Posted on 2014-12-14
4
Medium Priority
?
338 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 19

Assisted Solution

by:Thommy
Thommy earned 1000 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 85
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 1000 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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 …
Suggested Courses

604 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