473,366 Members | 1,125 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,366 software developers and data experts.

Help with recordset autonumber

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
3 2180
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: Roger Withnell | last post by:
I'm inserting a new record into an MS SQL database table and I want to obtain the new records autonumber immediately afterwards, as follows: MadminRS.CursorLocation = adUseServer...
7
by: ren | last post by:
Hello, When I open with Access 2000 a .DBF table and try to modify the content of a (random) record, I get the message 'This recordset is not updateable". And indeed, I can't change the content...
5
by: Lapchien | last post by:
I have list of numbers in a table (originally from autonumber in a different database) from 1 to 1,000,000. The list is not in sequential order - there are loads of numbers missing. How can I...
0
by: AR Hall | last post by:
This code goes through 3 work sheets in the same XL workbook, day, eve and night shift in each sheet their are columns representing production work orders and reject and downtime for each order...
22
by: Gerry Abbott | last post by:
Hi all, I having some confusing effects with recordsets in a recent project. I created several recordsets, each set with the same number of records, and related with an index value. I create...
9
by: Sandy | last post by:
Hi all, I have a form to list records (frmListIssue) which I call from different other forms. My wish is to display a message when the form is called and empty; no records to display. I want to...
22
by: RayPower | last post by:
I'm having problem with using DAO recordset to append record into a table and subsequent code to update other tables in a transaction. The MDB is Access 2000 with the latest service pack of JET 4....
1
imrosie
by: imrosie | last post by:
Please help with this one,,,,,I've been trying everything in my arsenal to fix this one. I'm stumped.... I"ve got a unbound combo box (customername) that has two events (on click); AfterUpdate and...
3
by: troy_lee | last post by:
I have a linked table (two primary keys: one coming from Me.txtRMA# and the other is an Autonumber) that I am writing to from an unbound form. Please review my code and tell me what is wrong with...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.