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
AdimitroAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
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.

JeffCoachman
Gustav BrockCIOCommented:
You can also set the default value in the AfterUpdate and/or OnCurrent event of the form:

    Me!YourField.DefaultValue = Me!YourField.Value

/gustav

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AdimitroAuthor Commented:
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
Else
    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
Else
    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
AdimitroAuthor Commented:
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
Jeffrey CoachmanMIS LiasonCommented:
OK, ...so the value for this field will "Always" be the same?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.