Link to home
Start Free TrialLog in
Avatar of Adimitro

asked on

Access form - add value from previous row when inserting a new row

I have a tabular form design in access 2016. Each row requires about 6 values (items).
When a new row is added I want the first item to match the value in the previous row.

how can I do this in vba
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

I want the first item to match the value in the previous row.
Note that the "Previous Row" can change if the table is resorted.

One way to do this, would be to copy the entire "previous" record, then clear out all the fields you don't want copied.

For example, create a button on the form and insert code like this on the Click event:

    DoCmd.RunCommand acCmdSelectRecord
    'Copy the current record
    DoCmd.RunCommand acCmdCopy
    'Create a new record
    DoCmd.RunCommand acCmdRecordsGoToNew
    'Select this new record
    DoCmd.RunCommand acCmdSelectRecord
    'Paste in the copied record
    DoCmd.RunCommand acCmdPaste
    'Clear the fields you don't want copied
    Me.FirstName = ""
    Me.LastName = ""

Open in new window

Lets see what others may post.

Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Adimitro


in the end the following code worked for me

Dim itemRow As Integer
Dim itemDefaultValue As String
Dim formOpen As Boolean
Private Sub Form_Current()
On Error Resume Next
If FormLoad.DefaultValue = "1" Then
    formOpen = True
    formOpen = False
End If

If formOpen And Not IsNull(ITEM.Value) Then itemDefaultValue = ITEM.Value
If IsNull(ITEM.Value) Then
    ITEM.DefaultValue = itemDefaultValue
    ITEM.Value = itemDefaultValue
    itemDefaultValue = ITEM.Value
    ITEM.DefaultValue = itemDefaultValue
    FormLoad.DefaultValue = "0"
End If
End Sub

Private Sub Form_Load()
End Sub

Private Sub Form_Open(Cancel As Integer)
    formOpen = True
End Sub

Private Sub item_Change()
If Not IsNull(ITEM.Value) Then
    itemDefaultValue = ITEM.Text
    itemRow = ITEM.ListIndex
End If
End Sub

Private Sub ITEM_GotFocus()
If Not IsNull(itemvalue) Then
    ITEM.DefaultValue = itemDefaultValue
End If
End Sub
I've requested that this question be closed as follows:

Accepted answer: 0 points for Adimitro's comment #a41376428
Assisted answer: 500 points for cactus_data's comment #a41376197

for the following reason:

there were some errors that appeared with other expert comments.  This code solved all of the errors
OK, the value for this field will "Always" be the same?