asked on
Get record written data
I currently use the following VBA code to get the MAX record just after commit:
<BEGIN VBA> Dim strSQLLot As String
Dim strSQLFailure As String
Dim intFailed As Integer
Dim intCounter As Integer
Dim dbConn As ADODB.Connection
Dim recSet As ADODB.Recordset
Dim intNextFailureId As Integer
Dim intArray() As Integer
Dim dbs As DAO.Database
Dim rsSQL As DAO.Recordset
Dim intMaxLotId As Integer
Dim intNewLotID As Integer
'Open the database
Set dbConn = CurrentProject.Connection
'Write the form's data to the SQL table tblInitialData
DoCmd.RunCommand acCmdSaveRecord
'determine MAX Lot ID number
DoCmd.RunMacro "mcrRunqryMaketblMaxLotID"
intMaxLotId = DLookup("MaxOfTestLotID", "tblMaxLotID")
'Prompt user with MAX Lot ID Number.
MsgBox "Lot number " & intMaxLotId & " has been created.", , "ATE DATABASE"
DoCmd.Close
'open switchboard and close initial form
DoCmd.OpenForm "Switchboard"
<END VBA>
I previously tried to get a SELECT statement to run but had no luck. I would like to use either SCOPE_IDENTITY() or @@IDENTITY but cannot determine the correct VBA to make that run. Indeed, will it even run in scope based on using the DoCmd.RunCommand acCmdSaveRecord statement instead of an INSERT INTO statement? I have tried DoCmd.OpenQuery, CurrentDb.Execute, and others that apparently do not allow SELECT statements.
TIA,
Tim
<BEGIN VBA> Dim strSQLLot As String
Dim strSQLFailure As String
Dim intFailed As Integer
Dim intCounter As Integer
Dim dbConn As ADODB.Connection
Dim recSet As ADODB.Recordset
Dim intNextFailureId As Integer
Dim intArray() As Integer
Dim dbs As DAO.Database
Dim rsSQL As DAO.Recordset
Dim intMaxLotId As Integer
Dim intNewLotID As Integer
'Open the database
Set dbConn = CurrentProject.Connection
'Write the form's data to the SQL table tblInitialData
DoCmd.RunCommand acCmdSaveRecord
'determine MAX Lot ID number
DoCmd.RunMacro "mcrRunqryMaketblMaxLotID"
intMaxLotId = DLookup("MaxOfTestLotID", "tblMaxLotID")
'Prompt user with MAX Lot ID Number.
MsgBox "Lot number " & intMaxLotId & " has been created.", , "ATE DATABASE"
DoCmd.Close
'open switchboard and close initial form
DoCmd.OpenForm "Switchboard"
<END VBA>
I previously tried to get a SELECT statement to run but had no luck. I would like to use either SCOPE_IDENTITY() or @@IDENTITY but cannot determine the correct VBA to make that run. Indeed, will it even run in scope based on using the DoCmd.RunCommand acCmdSaveRecord statement instead of an INSERT INTO statement? I have tried DoCmd.OpenQuery, CurrentDb.Execute, and others that apparently do not allow SELECT statements.
TIA,
Tim
VBAMicrosoft AccessMicrosoft SQL Server
Last Comment
gibneyt
check "Retrieving Microsoft Access Autonumber Values" from https://msdn.microsoft.com/en-us/library/ks9f57t0.aspx?f=255&MSPPError=-2147217396
What you are attempting is a little trickier than you might think.
Dlookup() is a performance pig.
None of the domain lookup functions should EVER be used.
This will find the 'largest' value depending on datatype and sort order
Dim rs As dao.Recordset
Set rs = db.OpenRecordset("SELECT TOP 1 TheFieldOfInterest FROM TheTableOfInterest ORDER BY TheFieldOfInterest DESC;", dbOpenDynaset, dbSeeChanges)
TheMaxValue = rs!TheFieldOfInterest
No matter how you do it though, you have to be aware you are in a multi-user setup.
No matter how fast you think you may be, what gets retrieved may be the value from the record just committed -- or from someone else's new addition.
@@IDENTITY is at the SQL Server end of things.
You can get that back to Access -- but not if you are letting the users create the record via a bound form as you appear to be doing (DoCmd.RunCommand acCmdSaveRecord)
If you create the record with DAO, pulling back the identity is fairly simple.
With rs
.AddNew
!MyField = MyValue
.Update
.Bookmark = .LastModified
lngJobID = ![JobID]
End With
Where my identity field is JobID and a Long (SQL Server bit) value.
But note that this value has NO guarantee of actually being the last record in play.
If you use the value to build a value that's unique (like work order or invoice numbers) you need to build a check in to ensure that an attempted value is ACTUALLY valid when you go to use it.
Dlookup() is a performance pig.
None of the domain lookup functions should EVER be used.
This will find the 'largest' value depending on datatype and sort order
Dim rs As dao.Recordset
Set rs = db.OpenRecordset("SELECT TOP 1 TheFieldOfInterest FROM TheTableOfInterest ORDER BY TheFieldOfInterest DESC;", dbOpenDynaset, dbSeeChanges)
TheMaxValue = rs!TheFieldOfInterest
No matter how you do it though, you have to be aware you are in a multi-user setup.
No matter how fast you think you may be, what gets retrieved may be the value from the record just committed -- or from someone else's new addition.
@@IDENTITY is at the SQL Server end of things.
You can get that back to Access -- but not if you are letting the users create the record via a bound form as you appear to be doing (DoCmd.RunCommand acCmdSaveRecord)
If you create the record with DAO, pulling back the identity is fairly simple.
With rs
.AddNew
!MyField = MyValue
.Update
.Bookmark = .LastModified
lngJobID = ![JobID]
End With
Where my identity field is JobID and a Long (SQL Server bit) value.
But note that this value has NO guarantee of actually being the last record in play.
If you use the value to build a value that's unique (like work order or invoice numbers) you need to build a check in to ensure that an attempted value is ACTUALLY valid when you go to use it.
I would suggest that use MS Access only for single user run and SQL Server for multiple user run, and then using MAX(JobID) as already discuss here, would work in Access, and in SQL Server, you can use SCOPE_IDENTITY() to retrieve the last value.
Hope that helps.
Hope that helps.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
@PatHartman
I am confused
"you cannot reference the Identity column this way"
Perhaps I did not phrase things as clearly as I would have liked.
This will get the largest committed value of [TheTableOfInterest].[TheF
When you have an Access backend, Access will put a hold on an autonumber the minute you begin to make a new record dirty, and you can retrieve that. SQL Server does not behave that way. The identity value isn't set until the record is committed.
This code creates a new record, commits it and retrieves the identity value of that.
and works swimmingly with a SQL Server backend.
But absolutely, if a SQL Server backend is in play, you cannot retrieve the identity value of a 'dirty and new' record of a form -- which is something that is possible with an Access backend. Only committing to save the record causes that identity value to be set in a SQL Server backend.
Is that what you meant?
I am confused
"you cannot reference the Identity column this way"
Perhaps I did not phrase things as clearly as I would have liked.
Dim rs As dao.Recordset
Set rs = db.OpenRecordset("SELECT TOP 1 TheFieldOfInterest FROM TheTableOfInterest ORDER BY TheFieldOfInterest DESC;", dbOpenDynaset, dbSeeChanges)
TheMaxValue = rs!TheFieldOfInterest
This will get the largest committed value of [TheTableOfInterest].[TheF
When you have an Access backend, Access will put a hold on an autonumber the minute you begin to make a new record dirty, and you can retrieve that. SQL Server does not behave that way. The identity value isn't set until the record is committed.
This code creates a new record, commits it and retrieves the identity value of that.
With rs
.AddNew
!MyField = MyValue
.Update
.Bookmark = .LastModified
lngJobID = ![JobID]
End With
and works swimmingly with a SQL Server backend.
But absolutely, if a SQL Server backend is in play, you cannot retrieve the identity value of a 'dirty and new' record of a form -- which is something that is possible with an Access backend. Only committing to save the record causes that identity value to be set in a SQL Server backend.
Is that what you meant?
Sorry, I read the code wrong. It looked like the reference was inside the .addnew rather than outside it.
ASKER
@PatHartman,
Nice! Accessing the form's RecordSource in this instance does appear to be the shortest and sweetest means to an end.
Many thanks.
Nice! Accessing the form's RecordSource in this instance does appear to be the shortest and sweetest means to an end.
Many thanks.