?
Solved

I need assistance saving on a click event in ACCESS 2013

Posted on 2014-12-14
4
Medium Priority
?
334 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

771 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