Help with recordset autonumber

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • troy_lee@comcast.net
    #1

    Help with recordset autonumber

    I have the following code.

    Set rstDB = CurrentDb
    Set rstEvalForm = CurrentDb.OpenR ecordset("tblRM AEval", dbOpenDynaset)
    With rstEvalForm
    'Add a new record to the end of the Recordset Object.
    .AddNew
    ![idsRMA#] = Me.[txtRMA#]
    ![dtmDate] = Me.cboEvalDate
    ![chrTech] = Me.cboTechName
    ![blnLooseHardwar eCheck] = Me.chkHardware
    ![blnWiringConnec tionsCheck] = Me.chkWiring
    ![blnOpticsCheck] = Me.chkOptical
    ![chrVisualCommen ts] = Me.txtVisualCom ments
    ![blnShortsCheck] = Me.chkShorts
    ![chrShotsCount] = Me.txtShots
    ![intInputE] = Me.txtInputE
    ![intDoubleE] = Me.txtDoubleE
    ![intPFNE] = Me.txtPFNE
    ![intSCRE] = Me.txtSCRE
    ![chrPowerComment s] = Me.txtPowerComm ents
    ![blnLaserFire] = Me.chkFired
    ![int10ShotAvg] = Me.txt10Shot
    ![intPW] = Me.txtPW
    ![blnATREnergy] = Me.chkATR
    ![blnGUITest] = Me.chkGUI
    ![chrLaserComment s] = Me.txtLaserComm ents
    ![intFirstTargetA ctual] = Me.txtFirstBIT
    ![intLastTargetAc tual] = Me.txtLastBIT
    .Update
    End With


    'Close the recordset and set it to nothing.
    rstEvalForm.Clo se
    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
    Tags: None
    • JvC
      #2
      Re: Help with recordset autonumber

      Tack this on after the .Update. lngL is any old long integer
      ..Bookmark = .LastModified
      lngL = !MyIDField

      John

      troy_lee@comcas t.net submitted this idea :
      I have the following code.
      >
      Set rstDB = CurrentDb
      Set rstEvalForm = CurrentDb.OpenR ecordset("tblRM AEval", dbOpenDynaset)
      With rstEvalForm
      'Add a new record to the end of the Recordset Object.
      .AddNew
      ![idsRMA#] = Me.[txtRMA#]
      ![dtmDate] = Me.cboEvalDate
      ![chrTech] = Me.cboTechName
      ![blnLooseHardwar eCheck] = Me.chkHardware
      ![blnWiringConnec tionsCheck] = Me.chkWiring
      ![blnOpticsCheck] = Me.chkOptical
      ![chrVisualCommen ts] = Me.txtVisualCom ments
      ![blnShortsCheck] = Me.chkShorts
      ![chrShotsCount] = Me.txtShots
      ![intInputE] = Me.txtInputE
      ![intDoubleE] = Me.txtDoubleE
      ![intPFNE] = Me.txtPFNE
      ![intSCRE] = Me.txtSCRE
      ![chrPowerComment s] = Me.txtPowerComm ents
      ![blnLaserFire] = Me.chkFired
      ![int10ShotAvg] = Me.txt10Shot
      ![intPW] = Me.txtPW
      ![blnATREnergy] = Me.chkATR
      ![blnGUITest] = Me.chkGUI
      ![chrLaserComment s] = Me.txtLaserComm ents
      ![intFirstTargetA ctual] = Me.txtFirstBIT
      ![intLastTargetAc tual] = Me.txtLastBIT
      .Update
      End With
      >
      >
      'Close the recordset and set it to nothing.
      rstEvalForm.Clo se
      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

        Comment

        • Marshall Barton
          #3
          Re: Help with recordset autonumber

          troy_lee@comcas t.net wrote:
          >I have the following code.
          >
          >Set rstDB = CurrentDb
          >Set rstEvalForm = CurrentDb.OpenR ecordset("tblRM AEval", dbOpenDynaset)
          With rstEvalForm
          'Add a new record to the end of the Recordset Object.
          .AddNew
          ![idsRMA#] = Me.[txtRMA#]
          ![dtmDate] = Me.cboEvalDate
          ![chrTech] = Me.cboTechName
          ![blnLooseHardwar eCheck] = Me.chkHardware
          ![blnWiringConnec tionsCheck] = Me.chkWiring
          ![blnOpticsCheck] = Me.chkOptical
          ![chrVisualCommen ts] = Me.txtVisualCom ments
          ![blnShortsCheck] = Me.chkShorts
          ![chrShotsCount] = Me.txtShots
          ![intInputE] = Me.txtInputE
          ![intDoubleE] = Me.txtDoubleE
          ![intPFNE] = Me.txtPFNE
          ![intSCRE] = Me.txtSCRE
          ![chrPowerComment s] = Me.txtPowerComm ents
          ![blnLaserFire] = Me.chkFired
          ![int10ShotAvg] = Me.txt10Shot
          ![intPW] = Me.txtPW
          ![blnATREnergy] = Me.chkATR
          ![blnGUITest] = Me.chkGUI
          ![chrLaserComment s] = Me.txtLaserComm ents
          ![intFirstTargetA ctual] = Me.txtFirstBIT
          ![intLastTargetAc tual] = Me.txtLastBIT
          .Update
          End With
          >
          >
          >'Close the recordset and set it to nothing.
          >rstEvalForm.Cl ose
          >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.nameoftextbo x = !nameofautonumb erfield

          --
          Marsh

            Comment

            • troy_lee@comcast.net
              #4
              Re: Help with recordset autonumber

              On Aug 6, 5:30 pm, Marshall Barton <marshbar...@wo wway.comwrote:
              troy_...@comcas t.net wrote:
              I have the following code.
              >
              Set rstDB = CurrentDb
              Set rstEvalForm = CurrentDb.OpenR ecordset("tblRM AEval", dbOpenDynaset)
              With rstEvalForm
              'Add a new record to the end of the Recordset Object.
              .AddNew
              ![idsRMA#] = Me.[txtRMA#]
              ![dtmDate] = Me.cboEvalDate
              ![chrTech] = Me.cboTechName
              ![blnLooseHardwar eCheck] = Me.chkHardware
              ![blnWiringConnec tionsCheck] = Me.chkWiring
              ![blnOpticsCheck] = Me.chkOptical
              ![chrVisualCommen ts] = Me.txtVisualCom ments
              ![blnShortsCheck] = Me.chkShorts
              ![chrShotsCount] = Me.txtShots
              ![intInputE] = Me.txtInputE
              ![intDoubleE] = Me.txtDoubleE
              ![intPFNE] = Me.txtPFNE
              ![intSCRE] = Me.txtSCRE
              ![chrPowerComment s] = Me.txtPowerComm ents
              ![blnLaserFire] = Me.chkFired
              ![int10ShotAvg] = Me.txt10Shot
              ![intPW] = Me.txtPW
              ![blnATREnergy] = Me.chkATR
              ![blnGUITest] = Me.chkGUI
              ![chrLaserComment s] = Me.txtLaserComm ents
              ![intFirstTargetA ctual] = Me.txtFirstBIT
              ![intLastTargetAc tual] = Me.txtLastBIT
              .Update
              End With
              >
              'Close the recordset and set it to nothing.
              rstEvalForm.Clo se
              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.nameoftextbo x = !nameofautonumb erfield
              >
              --
              Marsh
              Thanks so much. That is perfect.

              Troy

                Comment

                Previous template Next
                Working...