VBA - Insert Same Values from Multiple Fields in the Previous Record

[zashaikh]

In Access, [Ctrl]+['] inserts the value from the same field in the previous record. I find this shortcut useful and I use it often. However, I want to be able to press a button on my form and have Access automatically “insert the value from the multiple specified fields in the previous record.” How can I accomplish this using VBA?

[Minty]

If this happens frequently, then the easiest route is to set the default value to the current value on that group of controls when you press your button to got to the new record.

Add a Tag value to the controls you want to duplicate. Then loop through those tagged controls setting the default value.
When you then got to a new record those fields will be completed with the previous records values.

If you need help with that shout back here.

[zashaikh]

If this happens frequently, then the easiest route is to set the default value to the current value on that group of controls when you press your button to got to the new record.

Add a Tag value to the controls you want to duplicate. Then loop through those tagged controls setting the default value.
When you then got to a new record those fields will be completed with the previous records values.

If you need help with that shout back here.

This is not something that happens frequently. May once every three weeks do I need something like this. That is why I specifically need a button.

[Minty]

Ok, setting them as default values is probably a bit overkill if it's once a month.
Doing it that way would only be for as long as you have the form open though, it's not a permanent change, so may still be the most efficient way to achieve the end goal.

How many fields are you talking about?

[zashaikh]

Ok, setting them as default values is probably a bit overkill if it's once a month.
Doing it that way would only be for as long as you have the form open though, it's not a permanent change, so may still be the most efficient way to achieve the end goal.

How many fields are you talking about?

About 15 fields

[Minty]

You'll need to test this as it's air code(pretty untested).

On each control that you want to duplicate set the Tag property to Duplicate
On your button to copy the previous record add the following code;

Dim ctrl As Control

'Loop through controls and get current values into defaults values 
    For Each ctrl In Me.Controls
        If ctrl.Tag = "Duplicate" Then
            ctrl.DefaultValue = "'" & ctrl.Value & "'"
        End If
    Next

 DoCmd.GoToRecord , , acNewRec  ' This should now populate the new record with the values from the existing record

I'm pretty sure this will fail on checkboxes, so don't include them.
Closing the form should reset the default values back to their original settings provided you don't save form changes.

[zashaikh]

Thank you!!! I think that did it. I really apprecaite it