Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

adding a record to a table

Posted on 2013-02-05
2
Medium Priority
?
356 Views
Last Modified: 2013-02-05
I run the below to add a record to my main table.  

Private Sub CmdATech_Click()

Dim rst As ADODB.Recordset
   
    On Error GoTo err_label
   
    Set rst = New ADODB.Recordset
    rst.Open "techs", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    rst.AddNew
    rst!LName = Me.LName
    rst!FName = Me.FName
    rst!Email_Address = Me.Email_Address
    rst!FolderName = Me.FolderName
    rst.Update
    rst.Close
    Set rst = Nothing
   
    MsgBox "New record created sucessfully", vbInformation, "Record created"
   
    Me.LName = Null
    Me.FName = Null
    Me.Email_Address = Null
    Me.FolderName = Null
   
    Exit Sub

err_label:
    MsgBox "Error creating new record" & vbNewLine & Err.Description, vbCritical, "Error creating new record"


End Sub

' this sub requery's the combobox to add the new tech to the list of techs
' this also works fine

Private Sub cmdCForm_LostFocus()
   
    Forms![techs]![cmbID] = Null
    Forms!techs!txtHLink.Text = ""
    Forms![techs]![cmbID].Requery
 
 
End Sub

This works and once the record has been added the primary key is assigned.

when I try to select the new "ID" (which is the primary key) the query returns nothing.  in my frustration I deleted the code I had written.  
Does anyone have a suggestion on how to get the primary key for the newly added record?

I figure I need to query the original table (the one with the primary key)
0
Comment
Question by:sptech
[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
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 38854203
between the lines rst.Update and rst.Close, you may be able to fetch the key value using
Debug.Print rst!ID
0
 

Author Closing Comment

by:sptech
ID: 38854255
AngelIII thank you so very much! once I knew the ID I could add the record to the other table.  Your suggestion worked great!!!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

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.
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

715 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