Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2018
    Posts
    12

    Unhappy Help with VBA--copying last entry to form fields.

    Hello all,



    I have a form that I'm using to add computers to an inventory table. Often we get the same type of computer, just has a different serial number. I'm trying to make a button that would copy the last record's information into the fields with the exception of the serial number field.

    I'm following this tutorial. And I got it to where it will add a new record, but when I try to click my button, it alternates between copying the last and second to last record. So if I create Record 99 and then Record 100, and use my button it alternates between copying the values for Record 99 and Record 100. It's like the DMax function alternates which record it thinks is last. I'm really at a loss on what I'm doing wrong. Any help would be greatly appreciated.

    Here's a link to my DB if that helps.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    why not just set the defaultvalues in the form beforeupdate event? Something like

    ctrlname1.defaultvalue=ctrlname1
    ctrlname2.defaultvalue=ctrlname2
    ctrlname3.defaultvalue=ctrlname3
    etc

  3. #3
    Join Date
    Dec 2018
    Posts
    12
    Quote Originally Posted by Ajax View Post
    why not just set the defaultvalues in the form beforeupdate event? Something like

    ctrlname1.defaultvalue=ctrlname1
    ctrlname2.defaultvalue=ctrlname2
    ctrlname3.defaultvalue=ctrlname3
    etc
    Sorry, I'm really new and not really sure what you're getting at. Messing around with it more, I think maybe it's an issue with having both a button to Add New Record and a button to Add Similar Item. If I open the form and use the Add Similar Item button, it works fine. But when I break away and add a New Record and try to go back to using the Similar Item button is when it gets messed up.

    So it's going:
    Computer Type 1 (Similar Item Button)
    Computer Type 1 (Similar Item Button)
    Computer Type 1 (New Item Button)
    Computer Type 2 (New Item Button)
    Computer Type 3 (If I click Similar Item Button here, it will give me the details for Computer Type 2 in the next record. Here's the issue.)

    Maybe I just need to not have multiple buttons...

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    think you are overcomplicating it.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    it alternates between copying the last and second to last record.
    Likely you are sometimes performing this operation before the current record is committed and sometimes after. If before, the last record isn't the one you're on - it's the one before because the current one isn't a record yet. Something has to occur to save a record before you try to do what you're doing.

    To know this about your form means I would have to admit that I downloaded from your drop box link, which I usually don't. Suggest you learn how to compact/repair and then zip your db for uploading here (Go Advanced button). You will probably get more lookers in the future - especially if you can post a 2003 version.

    EDIT: I closed your db, went to close the vid and had a second thought. Went back to your code and spotted what appears to be the problem. You have
    DoCmd.GoToRecord , "", acNewRec Where did you get the double quotes from? It should be DoCmd.GoToRecord , , acNewRec
    Now your record gets committed, as I said. It seems that by providing an empty string ("") for the object type parameter it was going to a new record but the object that contains the record is non existent. More info here https://docs.microsoft.com/en-us/off...cmd.gotorecord
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    DoCmd.GoToRecord , "", acNewRec Where did you get the double quotes from?
    Pretty sure that code came from converting macros to VBA code via the ribbon selection. Microsoft provides some rather sketchy code, like that example, and including a lot of "On Error Resume Next" in the converted procedures.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The explanation makes sense but that would be strange considering that properly written code is provided in the video.

  8. #8
    Eugene-LS's Avatar
    Eugene-LS is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Dec 2018
    Location
    Murmansk
    Posts
    17
    Code:
    Public Function Duplicate(frm As Form) As Boolean
    'Make a new record as a duplicate of the current
    Dim i As Integer, n As Integer, v() As Variant, rs As DAO.Recordset
        Duplicate = False
        If frm.NewRecord Then Exit Function
        n = frm.Controls.Count - 1          'number of controls
        ReDim v(n)
        On Error Resume Next
        For i = 0 To n                      'old values
           v(i) = frm.Controls(i).Value
        Next i
        DoCmd.GoToRecord acDataForm, frm.Name, acNewRec
        For i = 0 To n                      'old values
            If Not IsEmpty(v(i)) Then frm.Controls(i).Value = v(i)
        Next i
        Duplicate = True
    End Function
    Usage:
    Code:
        Duplicate Me.Form
        Me!SerialNumber = "N/D"

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    A little more complicated but still looks effective. However, it will also copy the serial number, which is a field that shouldn't be copied? I guess that''s why it's later set to "N/D" which for serial numbers is probably ok - as long as they're not numeric.
    Wondering why Dim a recordset that isn't used, and why it's a function that returns a boolean that isn't used?

  10. #10
    Eugene-LS's Avatar
    Eugene-LS is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Dec 2018
    Location
    Murmansk
    Posts
    17
    Fine!
    You are right about Recordset
    That code is not mine ... I have own same, but with remarks in Russian. So it was quicker write not tested one.
    Let's try that way:
    Code:
    If Duplicate(Me.Form) = True Then 'Record successfully duplicated
         Me!SerialNumber = Null
    End If
    And:
    Code:
    Public Function Duplicate(frm As Form) As Boolean
    'Make a new record as a duplicate of the current
    Dim i As Integer, n As Integer, v() As Variant
        Duplicate = False
        If frm.NewRecord Then Exit Function
        n = frm.Controls.count - 1          'number of controls
        ReDim v(n)
        On Error Resume Next
        For i = 0 To n                      'old values
            v(i) = frm.Controls(i).Value
        Next i
        DoCmd.GoToRecord acDataForm, frm.Name, acNewRec
        For i = 0 To n                      'old values
            If Not IsEmpty(v(i)) Then frm.Controls(i).Value = v(i)
        Next i
        Duplicate = True
    End Function

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 12-02-2017, 02:04 PM
  2. Replies: 6
    Last Post: 09-27-2012, 08:27 AM
  3. Copying fields includes column name
    By julioot in forum Access
    Replies: 14
    Last Post: 04-04-2012, 08:25 AM
  4. Copying entry form, change control source
    By Bdowns in forum Access
    Replies: 11
    Last Post: 02-06-2012, 05:39 PM
  5. Copying into empty fields until...
    By cochi30 in forum Programming
    Replies: 8
    Last Post: 06-07-2009, 07:38 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums