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

Adimitro
Adimitro used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
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
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
You can also set the default value in the AfterUpdate and/or OnCurrent event of the form:

    Me!YourField.DefaultValue = Me!YourField.Value

/gustav

Author

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

Author

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 Liason
Most Valuable Expert 2012

Commented:
OK, ...so the value for this field will "Always" be the same?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial