By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,871 Members | 2,575 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,871 IT Pros & Developers. It's quick & easy.

Help with recordset autonumber

P: n/a
I have the following code.

Set rstDB = CurrentDb
Set rstEvalForm = CurrentDb.OpenRecordset("tblRMAEval", dbOpenDynaset)
With rstEvalForm
'Add a new record to the end of the Recordset Object.
.AddNew
![idsRMA#] = Me.[txtRMA#]
![dtmDate] = Me.cboEvalDate
![chrTech] = Me.cboTechName
![blnLooseHardwareCheck] = Me.chkHardware
![blnWiringConnectionsCheck] = Me.chkWiring
![blnOpticsCheck] = Me.chkOptical
![chrVisualComments] = Me.txtVisualComments
![blnShortsCheck] = Me.chkShorts
![chrShotsCount] = Me.txtShots
![intInputE] = Me.txtInputE
![intDoubleE] = Me.txtDoubleE
![intPFNE] = Me.txtPFNE
![intSCRE] = Me.txtSCRE
![chrPowerComments] = Me.txtPowerComments
![blnLaserFire] = Me.chkFired
![int10ShotAvg] = Me.txt10Shot
![intPW] = Me.txtPW
![blnATREnergy] = Me.chkATR
![blnGUITest] = Me.chkGUI
![chrLaserComments] = Me.txtLaserComments
![intFirstTargetActual] = Me.txtFirstBIT
![intLastTargetActual] = Me.txtLastBIT
.Update
End With
'Close the recordset and set it to nothing.
rstEvalForm.Close
Set rstEvalForm = Nothing
Set rstDB = Nothing
No problem. It all works good.

However, I want one more piece of information. How do I grab the
autonumber value created in the table after the recordset update has
run? I want to display this number in a hidden text box on the form I
am creating the recordset from. I want to do this while the form has
focus and is visible.

Thanks in advace.

Troy Lee
Aug 6 '08 #1
Share this Question
Share on Google+
3 Replies


P: n/a
JvC
Tack this on after the .Update. lngL is any old long integer
..Bookmark = .LastModified
lngL = !MyIDField

John

tr******@comcast.net submitted this idea :
I have the following code.

Set rstDB = CurrentDb
Set rstEvalForm = CurrentDb.OpenRecordset("tblRMAEval", dbOpenDynaset)
With rstEvalForm
'Add a new record to the end of the Recordset Object.
.AddNew
![idsRMA#] = Me.[txtRMA#]
![dtmDate] = Me.cboEvalDate
![chrTech] = Me.cboTechName
![blnLooseHardwareCheck] = Me.chkHardware
![blnWiringConnectionsCheck] = Me.chkWiring
![blnOpticsCheck] = Me.chkOptical
![chrVisualComments] = Me.txtVisualComments
![blnShortsCheck] = Me.chkShorts
![chrShotsCount] = Me.txtShots
![intInputE] = Me.txtInputE
![intDoubleE] = Me.txtDoubleE
![intPFNE] = Me.txtPFNE
![intSCRE] = Me.txtSCRE
![chrPowerComments] = Me.txtPowerComments
![blnLaserFire] = Me.chkFired
![int10ShotAvg] = Me.txt10Shot
![intPW] = Me.txtPW
![blnATREnergy] = Me.chkATR
![blnGUITest] = Me.chkGUI
![chrLaserComments] = Me.txtLaserComments
![intFirstTargetActual] = Me.txtFirstBIT
![intLastTargetActual] = Me.txtLastBIT
.Update
End With
'Close the recordset and set it to nothing.
rstEvalForm.Close
Set rstEvalForm = Nothing
Set rstDB = Nothing
No problem. It all works good.

However, I want one more piece of information. How do I grab the
autonumber value created in the table after the recordset update has
run? I want to display this number in a hidden text box on the form I
am creating the recordset from. I want to do this while the form has
focus and is visible.

Thanks in advace.

Troy Lee

Aug 6 '08 #2

P: n/a
tr******@comcast.net wrote:
>I have the following code.

Set rstDB = CurrentDb
Set rstEvalForm = CurrentDb.OpenRecordset("tblRMAEval", dbOpenDynaset)
With rstEvalForm
'Add a new record to the end of the Recordset Object.
.AddNew
![idsRMA#] = Me.[txtRMA#]
![dtmDate] = Me.cboEvalDate
![chrTech] = Me.cboTechName
![blnLooseHardwareCheck] = Me.chkHardware
![blnWiringConnectionsCheck] = Me.chkWiring
![blnOpticsCheck] = Me.chkOptical
![chrVisualComments] = Me.txtVisualComments
![blnShortsCheck] = Me.chkShorts
![chrShotsCount] = Me.txtShots
![intInputE] = Me.txtInputE
![intDoubleE] = Me.txtDoubleE
![intPFNE] = Me.txtPFNE
![intSCRE] = Me.txtSCRE
![chrPowerComments] = Me.txtPowerComments
![blnLaserFire] = Me.chkFired
![int10ShotAvg] = Me.txt10Shot
![intPW] = Me.txtPW
![blnATREnergy] = Me.chkATR
![blnGUITest] = Me.chkGUI
![chrLaserComments] = Me.txtLaserComments
![intFirstTargetActual] = Me.txtFirstBIT
![intLastTargetActual] = Me.txtLastBIT
.Update
End With
'Close the recordset and set it to nothing.
rstEvalForm.Close
Set rstEvalForm = Nothing
Set rstDB = Nothing
No problem. It all works good.

However, I want one more piece of information. How do I grab the
autonumber value created in the table after the recordset update has
run? I want to display this number in a hidden text box on the form I
am creating the recordset from. I want to do this while the form has
focus and is visible.

For a Jet database, you can add a line of code like this
before the .Update line:
Me.nameoftextbox = !nameofautonumberfield

--
Marsh
Aug 6 '08 #3

P: n/a
On Aug 6, 5:30 pm, Marshall Barton <marshbar...@wowway.comwrote:
troy_...@comcast.net wrote:
I have the following code.
Set rstDB = CurrentDb
Set rstEvalForm = CurrentDb.OpenRecordset("tblRMAEval", dbOpenDynaset)
With rstEvalForm
'Add a new record to the end of the Recordset Object.
.AddNew
![idsRMA#] = Me.[txtRMA#]
![dtmDate] = Me.cboEvalDate
![chrTech] = Me.cboTechName
![blnLooseHardwareCheck] = Me.chkHardware
![blnWiringConnectionsCheck] = Me.chkWiring
![blnOpticsCheck] = Me.chkOptical
![chrVisualComments] = Me.txtVisualComments
![blnShortsCheck] = Me.chkShorts
![chrShotsCount] = Me.txtShots
![intInputE] = Me.txtInputE
![intDoubleE] = Me.txtDoubleE
![intPFNE] = Me.txtPFNE
![intSCRE] = Me.txtSCRE
![chrPowerComments] = Me.txtPowerComments
![blnLaserFire] = Me.chkFired
![int10ShotAvg] = Me.txt10Shot
![intPW] = Me.txtPW
![blnATREnergy] = Me.chkATR
![blnGUITest] = Me.chkGUI
![chrLaserComments] = Me.txtLaserComments
![intFirstTargetActual] = Me.txtFirstBIT
![intLastTargetActual] = Me.txtLastBIT
.Update
End With
'Close the recordset and set it to nothing.
rstEvalForm.Close
Set rstEvalForm = Nothing
Set rstDB = Nothing
No problem. It all works good.
However, I want one more piece of information. How do I grab the
autonumber value created in the table after the recordset update has
run? I want to display this number in a hidden text box on the form I
am creating the recordset from. I want to do this while the form has
focus and is visible.

For a Jet database, you can add a line of code like this
before the .Update line:
Me.nameoftextbox = !nameofautonumberfield

--
Marsh
Thanks so much. That is perfect.

Troy
Aug 7 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.