Solved

adding a record to a table

Posted on 2013-02-05
2
346 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 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

739 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