Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

adding a record to a table

Posted on 2013-02-05
2
Medium Priority
?
362 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 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

926 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