MS Access VBA equivalent to Ctrl '?-CodePudding

I am having a difficult time how to properly copy specific field data from previous records on my user form. I don't have a code sample to show but my request is very simplistic.

Currently, out of 12 fields, I have 6 that I often repeat data. I can click on and press Ctrl ' ("Insert the value from the same field in the previous record") and it performs the task I want. However, it adds a lot of time to the task. I simply want to write VBA code to perform that command to those specific fields.

I haven't been able to get SendKeys to work. DLast appears to provide random data at times. I feel like this should be a very simple request but for some reason I am not finding a functional solution for it.

CodePudding user response：

Provided that it's a simple form to edit a simple table, and that the bound data field names match the control names, you may go away with

If Me.Recordset.AbsolutePosition > 0 Then
  With Me.Recordset.Clone()
    .AbsolutePosition = Me.Recordset.AbsolutePosition - 1
    
    Dim control_name As Variant
    For Each control_name In Array("field1", "field2", "field3", "field4", "field5", "field6")
      Me.Controls(control_name).Value = .Fields(control_name).Value
    Next
  End With
End If

which you assign to a separate button on the same form.

CodePudding user response：

You have a good idea post here already.

You could also say place a function in the before insert event. This event ONLY fires when you start typing into a NEW reocrd, and it becomes dirty.

So, maybe this:

Private Sub Form_BeforeInsert(Cancel As Integer)

   Dim rstPrevious     As DAO.Recordset
   Dim strSQL          As String
   
   strSQL = "SELECT TOP 1 * FROM tblPeople ORDER BY ID DESC"
   
   Set rstPrevious = CurrentDb.OpenRecordset(strSQL)
   
   ' auto file out some previous values
   
   If rstPrevious.RecordCount > 0 Then
   
     Me.Firstname = rstPrevious!Firstname
     Me.LastName = rstPrevious!LastName
           
  End If

End Sub

And some good ideas in say having a "list" or "array" of controls/fields to setup, so you don't have to write a lot of code. (as suggested in the other post/answer here)