UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Add Record VBA or SQL, return new primary key    
 
   
fritz
post Nov 14 2007, 11:34 PM
Post#1



Posts: 930
Joined: 24-October 03
From: Dana Point, CA


I've tried two ways of adding a record to a table - both work, but I can't figure out how to get the new PK created by the new record. I'm missing something basic here crazy.gif
The table to enter into is LChemOrderT, the fields are ChemicalID (FK from the Parent record), and DtMade (Date field).
The data is the PK Me.ChemicalID, and the system Date
With SQL:
CODE
             strSQL = "INSERT INTO LChemOrderT (ChemicalID, DtMade) VALUES (" & Me.ChemicalID & ", " & Date & ");"
            CurrentDb.Execute strSQL

(BTW, adding ", vbFailOnError" generates an undefined variable error - any idea why they would be?)
It adds the record, but I can't figure out how to get the record PK.
2nd method is with a module:
' Call the function, variable to hold returned value
Dim Rtn as long
AddChemOnHand (Me.ChemicalID)
CODE
  Public Function AddChemOnHand(lgChemicalID As Long) As Long
   [color="green"] ' Comments   : This function adds new records to the LChemOrderT Table. [/color]
  
On Error GoTo Err_AddChemOnHand
    Dim cnn As ADODB.Connection
    Dim rst1 As ADODB.Recordset
    Dim strSQL As String
        
    Set cnn = CurrentProject.Connection
    Set rst1 = New ADODB.Recordset
    strSQL = "Select * From LChemOrderT"
    rst1.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
    
        With rst1
            .AddNew
                !ChemicalID = lgChemicalID
                !DtMade = Date
            .Update
        End With
[color="green"]' rst1!LchemOrderID contains the new PK number[/color]
        lgChemicalID = rst1!LChemOrderID
  
  [color="green"] 'Close the recordsets and the connection[/color]
   rst1.Close
   cnn.Close
  
Exit_AddChemOnHand:
   Exit Function
Err_AddChemOnHand:
   MsgBox Err.Description
   Resume Exit_AddChemOnHand
      
End Function

But now unsure how to get the new PK back from the module to the original sub.
Harry
crazy.gif
Go to the top of the page
 
mishej
post Nov 14 2007, 11:41 PM
Post#2


Retired Moderator
Posts: 11,289
Joined: 25-September 02
From: Milwaukee, WI


When using the DAO object module and the CurrentDb.Execute statement you want to use "dbFailOnError" not "vbFailOnError" (note the different first character).
The 2nd example uses the ADO object module and will work. To return the new ID add this line prior to closing the rstl recordset:
CODE
AddChemOnHand = rst1!LChemOrderID
Go to the top of the page
 
datAdrenaline
post Nov 15 2007, 12:17 AM
Post#3


UtterAccess Editor
Posts: 17,929
Joined: 4-December 03
From: Northern Virginia, USA


John gave you how to get it via entering the record with a recordset ... here's how with a SQL Statement:
!--c1-->
CODE
strSQL = "INSERT INTO LChemOrderT (ChemicalID, DtMade) " & _
              " VALUES (" & Me.ChemicalID & ", " & Date & ");"
With CurrentDb
    .Execute strSQL, dbFailOnError
    x = .OpenRecordset("Select @@Identity")(0)
End With

Note "x" will hold the PK AutoID of the new record.
Go to the top of the page
 
fritz
post Nov 15 2007, 01:06 AM
Post#4



Posts: 930
Joined: 24-October 03
From: Dana Point, CA


Thanks for the responses!
John, Thanks for the reminder ... I also had to remember to set the original call:
strMyResult= AddChemOnHand(Me.ChemicalID)
rather than just:
AddChemOnHand(Me.ChemicalID)
Sigh. Works fine now, though.
Brent, Thanks for the second part. Works just fine - was wondering though what the following means/does:
<
I've not seen that confirguration before - esp double @@.
H
Go to the top of the page
 
LPurvis
post Nov 15 2007, 03:48 AM
Post#5


UtterAccess Editor
Posts: 16,085
Joined: 27-June 06
From: England (North East / South Yorks)


The Select @@Identity statement is the entire crux of fetching the newly added autonumber value.
It was an addition to Jet 4 which allowed for the SQL compliant @@Identity function - which returns the last inserted Identity (autonumber) value for that connection.
Note that it is connection specific - so you'll not get an ID that someone else has added after you but before checking. Equally you must use the same data access method to obtain the value (e.g. add a record through DAO and check the @@Identity through ADO and you'll *not* get the correct value back).
All the statement
.OpenRecordset("Select @@Identity")(0)
is doing is opening a recordset to select the Identity value (for it must be *selected* via the db engine).
Incase you're also not familiar with the syntax used - that's just opening a recordset and returning the first (and only) ordinal field without creating an explicit object variable for the recordset.
You could have
.OpenRecordset("Select 'Bob', @@Identity")(1)
though it would be pointless to do so - but perhaps illustrates what's going on?
Go to the top of the page
 
fritz
post Nov 15 2007, 11:00 AM
Post#6



Posts: 930
Joined: 24-October 03
From: Dana Point, CA


Thanks Leigh!
o in the (corrected) ADO routine above, instead of:
CODE
         AddChemOnHand= rst1!LChemOrderID

could I use:
CODE
AddChemOnHand = .OpenRecordset("Select @@Identity")(0) ?

H
Go to the top of the page
 
LPurvis
post Nov 15 2007, 11:38 AM
Post#7


UtterAccess Editor
Posts: 16,085
Joined: 27-June 06
From: England (North East / South Yorks)


Not quite. That's an exact example of what I was talking about.
You'd need to use your ADO connection to get the Identity value.
ddChemOnHand = cnn.Execute("Select @@Identity")(0)
Go to the top of the page
 
fritz
post Nov 15 2007, 11:40 AM
Post#8



Posts: 930
Joined: 24-October 03
From: Dana Point, CA


Got it. Thanks!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 02:48 AM