Solved

adding a record to a table

Posted on 2013-02-05
2
315 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
2 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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 Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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.

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now