Help with duplicate record, with changed field

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • capdownlondon
    New Member
    • Mar 2007
    • 14

    Help with duplicate record, with changed field

    Working in Access 2003.
    I have managed to duplicate a record using a button on a form with code:
    Private Sub Command16_Click ()
    On Error GoTo Err_Command16_C lick


    DoCmd.DoMenuIte m acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuIte m acFormBar, acEditMenu, 2, , acMenuVer70
    DoCmd.DoMenuIte m acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

    Exit_Command16_ Click:
    Exit Sub

    Err_Command16_C lick:
    MsgBox Err.Description
    Resume Exit_Command16_ Click

    End Sub


    But this just creates a duplicate of the fields on the form, which is fine, but i would also like to +1 to one of the fields on the new record.

    Is this possible? If so how would i do it.



    Also.....
    If this is possible,
    would it also be possible to have a drop down menu in which to select say (n)1-9 and that is how many duplicates it will make with field +(n).
    so the first duplicate will be the same but a field will have +1, and the next +2, etc.
    for the selected number of fields
  • fauxanadu
    New Member
    • Mar 2007
    • 60

    #2
    Let's Assume that there is a table tblStuff with fields txtName, txtAddress, and numID. Let's also assume that you wanted to increment numID and you are copying the last record made.

    Code:
    Private Sub Command16_Click()
        On Error GoTo errCommand16Click
    
        Dim strName as String
        Dim strAddress as String
        Dim intIDNo as Integer
        Dim rstStuff as ADODB.Recordset
    
        With rstStuff
            'Open tblStuff to read and write
            .Open "tblStuff", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
            'Moves to the last record, and then copies its values to temporary variables
            .MoveLast
            strName = rstStuff![txtAddress]
            strAddress = rstStuff![txtAddress]
            intIDNo = rstStuff![numID] + 1 'Adds one to the ID number
            'Add a new record to tblStuff and give it the values pulled from the last record in the table
            .AddNew
            ![txtName] = strName
            ![txtAddress] = strAddress
            ![numID] = intIDNo
            .Update
            .Close
        End With
    
        Set rstStuff = Nothing
    
        Exit Sub
    
    errCommand16Click:
        MsgBox Err.Description
    
    End Sub
    In order to make several copies, grab the value from the sub-menu and store it in a variable (such as i) and then use For i = 1 to X just before the With rstStuff and End For just after Set rstStuff = Nothing

    Comment

    • Denburt
      Recognized Expert Top Contributor
      • Mar 2007
      • 1356

      #3
      After the new duplicate record is created then simply refer to the control in question and add 1

      Me!YOURTEXTBOX = Me!YOURTEXTBOX+ 1



      To break it down it would simply look like so:


      Private Sub Command16_Click ()
      On Error GoTo Err_Command16_C lick
      Dim i as integer, cnt as integer

      cnt = Me!COUNTTEXTBOX NAME

      For i = 0 to cnt
      DoCmd.DoMenuIte m acFormBar, acEditMenu, 8, , acMenuVer70
      DoCmd.DoMenuIte m acFormBar, acEditMenu, 2, , acMenuVer70
      DoCmd.DoMenuIte m acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

      Me!YOURTEXTBOX = Me!YOURTEXTBOX+ 1

      next

      Exit_Command16_ Click:
      Exit Sub

      Err_Command16_C lick:
      MsgBox Err.Description
      Resume Exit_Command16_ Click

      End Sub

      Comment

      • Denburt
        Recognized Expert Top Contributor
        • Mar 2007
        • 1356

        #4
        As a side note I didnt check to see what you were doing with the menubar commands those commands can be troublesome at times (not function correctly) I would suggest doing this in code it would be much more reliable..


        Check out the following article:

        Comment

        • capdownlondon
          New Member
          • Mar 2007
          • 14

          #5
          thanks i will give that a try, will that only work for the last record? or will it work for whatever record is currently selected in the form?

          Comment

          • capdownlondon
            New Member
            • Mar 2007
            • 14

            #6
            what i have already duplicates just the name, date index, and target 1, target 2, target 3 fields, and the autonumber already increases as it should, but i want to +1 to the date index field.

            Comment

            • Denburt
              Recognized Expert Top Contributor
              • Mar 2007
              • 1356

              #7
              If your adding one day to a date it would look more like:

              Me!YOURTEXTBOX = DateAdd("d",1,M e!YOURTEXTBOX)


              if you want to copy a particular record not just the last one it just needs a few adjustments.

              Remove this:

              If Not F.NewRecord Then Exit Function


              Adjust the following lines to read:


              Set RS = F.RecordsetClon e
              'This line can be removed RS.MoveLast

              rs.Bookmark=Me. Bookmark
              docmd.GoToRecor d ,,acNewRec

              I think that should work for you let me know.
              Good Luck

              Comment

              • capdownlondon
                New Member
                • Mar 2007
                • 14

                #8
                its a date index, that refers to a date in another table. just need to +1.

                Comment

                • capdownlondon
                  New Member
                  • Mar 2007
                  • 14

                  #9
                  one last thing, i think. I've managed to get the form to do what i want using:

                  Private Sub Command16_Click ()
                  On Error GoTo Err_Command16_C lick
                  Dim i As Integer, cnt As Integer

                  cnt = Me!Combo17

                  For i = 0 To cnt
                  DoCmd.DoMenuIte m acFormBar, acEditMenu, 8, , acMenuVer70
                  DoCmd.DoMenuIte m acFormBar, acEditMenu, 2, , acMenuVer70
                  DoCmd.DoMenuIte m acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

                  Me!Text22 = Me!Text22 + 1

                  Next

                  Exit_Command16_ Click:
                  Exit Sub

                  Err_Command16_C lick:
                  MsgBox Err.Description
                  Resume Exit_Command16_ Click

                  End Sub




                  Now when the records have been added it automatically takes me to the last record added. Is it possible to add the duplicates and stay with the current record on the form?

                  Comment

                  • Denburt
                    Recognized Expert Top Contributor
                    • Mar 2007
                    • 1356

                    #10
                    At the beggining of the code mark your location theen at the end return to your original location.

                    Dim varBk As Integer
                    varBk = Me.Bookmark






                    Me.Bookmark = varBk

                    That should work.

                    Comment

                    • capdownlondon
                      New Member
                      • Mar 2007
                      • 14

                      #11
                      ive tried that, but i get a "type mismatch" error now

                      Comment

                      • capdownlondon
                        New Member
                        • Mar 2007
                        • 14

                        #12
                        Ok, well i got it all sorted now. using this code:
                        Private Sub Command16_Click ()
                        On Error GoTo Err_Command16_C lick
                        Dim i As Integer, cnt As Integer, varBk As String

                        varBk = Me.Bookmark
                        cnt = Me!Combo17

                        For i = 0 To cnt - 1
                        DoCmd.DoMenuIte m acFormBar, acEditMenu, 8, , acMenuVer70
                        DoCmd.DoMenuIte m acFormBar, acEditMenu, 2, , acMenuVer70
                        DoCmd.DoMenuIte m acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

                        Me!Text22 = Me!Text22 + 1



                        Next

                        Exit_Command16_ Click:
                        Me.Bookmark = varBk
                        Exit Sub

                        Err_Command16_C lick:
                        MsgBox Err.Description


                        Resume Exit_Command16_ Click


                        End Sub





                        But......

                        Is it also possible to stop this solution making a duplicate duplicate.

                        What i mean is; this solution creates a duplicate of fields x, y, z and w+1.

                        So this is not creating exact duplicates, as w value has changed for each record.

                        Now if you hit this button, or run this, twice, there will be REAL duplicates, as there will be 2 copies of x ,y, z and w+1.

                        So..

                        Is it possible to add code that will replace the old duplicate.
                        Also because z is a variable and i may want this changed on the duplicates, if they already exist i can't just block the new duplicate being sent.

                        Comment

                        • Denburt
                          Recognized Expert Top Contributor
                          • Mar 2007
                          • 1356

                          #13
                          You can use something like the following to prevent duplication of data or change it up to suite your needs.

                          rs= me.recordsetclo ne
                          if rs.findfirst (PutWhereCondit ionHere) Then
                          rs.close
                          set rs = nothing
                          exit sub
                          end if

                          Comment

                          Working...