Copy and pasting data in form view help

[Ash1402]

Name: Screen Shot 2012-09-20 at 11.17.40 AM.jpg  Views: 33  Size: 143.9 KB  ID: 9219" class="thumbnail" style="float:CONFIG" />

On the image above I have a subform within a form. The subform under "Admissions/Transfers" does not accept pasted data when I try to paste the full forms data into it.

There is a data entry person and in some circumstances the same fields apply for one or more entries. I copy/paste in the grey bar on the left hand side but once a new form is started only the top part and not "Admissions/Transfers" part which sometimes contains a lot of data, too difficult to re enter in some instances.

Is there any way to make sure that, in form view, I can copy and paste data from one entry to another?

Thank you for your help on this!

[June7]

Can't copy/paste entire record on a form, only in tables and queries.

You can set the value entered in a control to carry forward into another record. Do this by setting the DefaultValue property of the control. In the AfterUpdate event of control:

Me.controlname.DefaultValue = Me.controlname

[Missinglinq]

As June7 said, you can use the AfterUpdate event of the Control holding your data to set the DefaultValue for the Field. From that time forward, until you either manually change the data or close your form, the data will be entered automatically in each New Record.

The syntax for each Datatype is slightly different, or you can use this syntax

Private Sub YourControlName_AfterUpdate()
   Me.YourControlName.DefaultValue = """" & Me.YourControlName.Value & """"
End Sub

which is valid for Text, Number and Date Datatypes. Never tried it on a Yes/No Field!

You’ll need to do this for each Control that you want to ‘carry forward.’

If you have a slew of Fields, you can use the Tag Property to mark the ones to be defaulted and then loop through them, setting the DefaultValue.

• In Form Design View
• Select all Controls you want to 'carry forward,' by pressing and holding down the <Shift> Key and clicking on each one.
• Go to Properties - Other
• In the Tag Property enter CarryForward (without quotes)

Now use this code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctl As Control

For Each ctl In Me.Controls
  If ctl.Tag = "CarryForward" Then
    ctl.DefaultValue = """" & ctl.Value & """"
  End If
Next ctl

End Sub

Each of the "tagged" Controls' values will be carried forward into a New Record until you either Edit the data in a given Control or Close the Form or Access itself.

Linq ;0)>

[Ash1402]

Thanks for the replies

I'm still having trouble though. I did like you said and highlighted all of the cells I'd like to carry on then tagged them with CarryForward. I think my problem is coming with the code as I am effectively a newbie at Access.

To add the code I clicked on "Database tools" then "Visual Basic." Inside of Visual Basic I tried both "Module" and "Class Module" with the code but could not get it to work by either clicking on the New Record button at the bottom of the form or the Next Record arrow that is underneath the form.

What do you think I am doing wrong?

[Ash1402]

Sorry for the quick second reply... But do you think there is a way to add a button to the form such as "Continue record" that will duplicate all of the data throughout and allow me to update the "suffix" field. The suffix field is the only field that would need updating if I had to process the same form more than once.

[June7]

The code goes behind the form in its code module, not a general module or a class module. Select [Event Procedure] in the form BeforeUpdate event property, double click the ellipses (...) to go to the VBA procedure, type (or copy/paste) code.

With this code, all you would have to do in a new record is to enter value in the 'suffix' field. No 'Continue record' button needed.

Interesting code, I have often wondered what the Tag property could be used for.

[Missinglinq]

...Interesting code, I have often wondered what the Tag property could be used for.

The Tag Property of a Control is a way to mark or 'tag' that Control as a part of a group. You can then use code to loop through all Controls on a Form, but only perform a given action if the Tag matches.

You can use this technique, for instance, to Tag all Controls that display Required Fields. Then, in the Form_BeforeUpdate event, you can run Validation code to make sure that all of these Fields are populated, and you can do so without having to use a separate If...Then clause for each Controls, naming each Control individually!

You can 'tag' Controls and assign them to different groups within a Form, performing one action on one group, and something else on another, you simply give them different Tags.

You can even do this with some of the Controls overlapping, i.e. appearing in multiple groups! You'd simply use two or more 'sub-Tags,' if you will, for one Control! You could have a Tag that reads

CarryForward;Required

To identify Controls that were 'tagged' with CarryForward, instead of

If ctl.Tag = "CarryForward" Then

you'd use

If Instr(ctl.Tag, "CarryForward") > 0 Then

Then, to identify Controls that were 'tagged' with Required, you'd use

If Instr(ctl.Tag, "Required") > 0 Then

Besides the use above, you can use Tags to mark Controls for formatting. If different types of items get different discounts, i.e. services as opposed to capital budget items, you can use this technique. The uses are really almost unlimited!

Linq ;0)>

[Ash1402]

As June7 said, you can use the AfterUpdate event of the Control holding your data to set the DefaultValue for the Field. From that time forward, until you either manually change the data or close your form, the data will be entered automatically in each New Record.

The syntax for each Datatype is slightly different, or you can use this syntax

Private Sub YourControlName_AfterUpdate()
   Me.YourControlName.DefaultValue = """" & Me.YourControlName.Value & """"
End Sub

which is valid for Text, Number and Date Datatypes. Never tried it on a Yes/No Field!

I used the code quoted above on all of the boxes I wanted to carry on to the next portion and it worked I have one slight problem with one of the boxes and I'm sure it'll just be a quick thing but I have no experience with VBA.

One of the dropdown boxes contains 2 parts that are separated by a line. Using the code above does not bring that dropdown box forward. It instead brings everything else and leaves that behind.

Is it a quick fix?

[June7]

Does '2 parts that are separate by a line' mean two fields are concatented to look like one? Is this a multi-column combobox with first column as ID field? Linq, does the code assume values brought forward are text strings, not number?

Show settings for properties of combobox:

RowSource
ControlSource
BoundColumn
ColumnWidths