• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 305
  • Last Modified:

URGENT!! CTRL+' (apostrophe) copy previous record

Oh did I not catch this one.  I am going live with a database tomorrow, and I just noticed a problem.

On a form I am using a shortcut key {F2} to copy the previous records to the current record.  I have it doing this by using the CTRL+' sendkeys command, and it works great except for checkboxes.  It is not copying the checkbox records.  I can not simply use a recordsetclone and movetolast technique, because 50% of the time I may not want the last record entered, but the record just before where I am.  I also may not get away with using something like RecordID-1 because sometimes recordID's are not in sequential order.  

Am I doing something wrong with the CTRL+' when dealing with checkboxes?  If so, what must I do to correct it.

Any thoughts on how I can get the previous checkbox copied to the corrent record.

I am running out of time, Yikes!

Thanks,
0
sophisticatediowan
Asked:
sophisticatediowan
  • 3
  • 3
  • 2
1 Solution
 
Jeffrey CoachmanCommented:
Try this,

Straight from the Access button Wizard "Duplicate Record" choice:

Private Sub btnDuplicateRecord_Click()
On Error GoTo Err_btnDuplicateRecord_Click


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

Exit_btnDuplicateRecord_Click:
    Exit Sub

Err_btnDuplicateRecord_Click:
    MsgBox Err.Description
    Resume Exit_btnDuplicateRecord_Click
   
End Sub

See if this helps
0
 
Stephen_PerrettCommented:
Try this idea. It assumes ID field is Autonumber to find previous record to current record and appends new record with duplicate data from specified fields:

Private Sub cmdDuplicatePreviousRecord_Click()
On Error GoTo ErrorHandler
    Dim strSQL As String
    Dim strCurrentID As String
    strCurrentID = CStr(Me!ID)
   
    strSQL = "INSERT INTO tblData ( Field2, Field3, Field4 )"
    strSQL = strSQL & " SELECT TOP 1 tblData.Field2, tblData.Field3, tblData.Field4"
    strSQL = strSQL & " FROM tblData"
    strSQL = strSQL & " WHERE (((tblData.ID) < " & strCurrentID & "))"
    strSQL = strSQL & " ORDER BY tblData.ID DESC;"
   
    CurrentDb.Execute strSQL, dbFailOnError
   
    Me.Requery

ExitProcedure:
    Exit Sub
   
ErrorHandler:
    MsgBox Err.Description
    Resume ExitProcedure

End Sub

Steve
0
 
sophisticatediowanAuthor Commented:
I am not always appending a new record.  Very often I may be updating a record to match the previous.

Thanks,
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Stephen_PerrettCommented:
try this way instead:

Private Sub cmdDuplicatePreviousRecord_Click()
On Error GoTo ErrorHandler
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim strCurrentID As String
    Me!txtField2 = ""
    strCurrentID = CStr(Me!ID)
   
    strSQL = "SELECT TOP 1 tblData.*"
    strSQL = strSQL & " FROM tblData"
    strSQL = strSQL & " WHERE (((tblData.ID) < " & strCurrentID & "))"
    strSQL = strSQL & " ORDER BY tblData.ID DESC;"
    Debug.Print strSQL
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenForwardOnly)
   
    If Not (rst.EOF Or rst.BOF) Then
        Me!txtField2 = rst("Field2")
        Me!txtField3 = rst("Field3")
        Me!chkField4 = rst("Field4")
    End If

ExitProcedure:
    Exit Sub
   
ErrorHandler:
    MsgBox Err.Description
    Resume ExitProcedure

End Sub

Steve
0
 
sophisticatediowanAuthor Commented:
Ok, well we decided that this is not going to be a showstopper, so I can take my time and fix this problem.  I'll try out your suggestions and if you have any more, feel free to post.  I am going to work on another issue for the next day or so before getting back to this one.

Thanks,
0
 
Jeffrey CoachmanCommented:
We'll be here!
0
 
Jeffrey CoachmanCommented:
Award points to Stephen_Perrett
0
 
sophisticatediowanAuthor Commented:
I still have not had time to get back to this problem.  I have been so busy with other prodjects.  I'll go ahead and close up this question and give the points to Stephen_Perrett.  When I do finally get time to get back to this, I'll post my final comments then.

Thank You,
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now