INSERT INTO Question

[Randomblink]

Greetings one and all...
Just a quick question for the masters...

If I am using DoCmd.RunSQL to INSERT a record to a table.
And if that table has a Primary Key with Autonumber.
Is there ANYWAY to INSERT that record AND grab the Autonumber generated?

For instance...
Let's say I have a Customer Table...
and I am running DoCmd.RunSQL to INSERT a new customer.
Let's say I have a field called Customer_ID
THAT Field is the Primary Key / Autonumber
Is there anyway to insert a new customer and then grab the Customer_ID for THAT Customer?

Some command?
rst!Customer_ID right AFTER inserting maybe?

 

[dgm]

Not using RunSQL (that i know of anyway).
U could if u were using ADO:

Private Sub AddRecord_Click()
    Dim rst As ADODB.Recordset
    Dim intVal As Integer

    Set rst = New ADODB.Recordset
    rst.Open "Customers", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    
    rst.AddNew
    rst!custName = "Fred"
    rst!custTitle = "Sir"
    rst.Update
    intVal = rst!custID
    rst.Close
    Debug.Print intVal
End Sub

Other then that, maybe u could use a dlookup to find the customerID for that customer?
intVal = dlookup("[custID]","Customers","[custName] = '" & Fred & "'")

 

[Randomblink]

Excellent...

THANKS!
That did it... I wasn't thinking, I was trying DAO with TableDefs and everything else... ugh! Too much on the ole mind to code...

Ah well...
But thanks alot man...
That did the trick...