CmdCopy Question

Question

Hi Community,

I added the code below to a command button in a form which copies a record, that said, I don’t want to paste all fields within the record ---  how do I tweak the code to select only the fields desired?  Let’s assume that I have fields called RepCode, AccountNumber and LastName which I want to copy from the record.  This is the code I currently use that selects all fields:

Private Sub Command974_Click()

    DoCmd.RunCommand acCmdSelectRecord

    DoCmd.RunCommand acCmdCopy

End Sub

As always, many thanks!

Ken

Answer 1

Am I right in thinking that what you are attempting is to create a new record in which selected fields have the values of those fields in the current record?  If so I'd suggest a different approach, setting the DefaultValue property of the relevant controls.  If we assume you want to copy the FirstName and LastName values only for instance, the button's code would be:

Private Sub cmdCopyRecord_Click()

    ' set default values of controls to be copied

    ' to values in current record

    Me.FirstName.DefaultValue = """" & Me.FirstName & """"

    Me.LastName.DefaultValue = """" & Me.LastName & """"

    ' move form to a new record

    DoCmd.GoToRecord acForm, Me.Name, acNewRec

    ' initiate a new record by setting the value of any

    ' non-Null control to itself

    Me.FirstName = Me.FirstName

    ' set default values of relevant controls

    ' back to zero-length strings

    Me.FirstName.DefaultValue = """"""

    Me.LastName.DefaultValue = """"""

End Sub

A few explanatory points:

1.  The DefaultValue property is always a string expression, so should be wrapped in literal quotes characters as above regardless of the data type of the field in question.

2.  The reason for there being so many quotes characters is that, when building a string expression a literal double quotes character is represented by a contiguous pair of double quotes characters.  This is better than using single quote characters, particularly with personal names, as the value might include an apostrophe, whereas a double quotes character within the value is far less likely.

3.  Note that to initiate the new record after setting the default  values you only have to set the value of one of the controls to itself, not all of them.  This 'dirties' the form, so the default values become the actual values of the controls, and the record will be saved even if the user does not add any more data or edit any of the default values.

4.  Note that I've given the button a meaningful name rather than accepting the name Access gives it.  Do this as soon as a button, or any type of control, is added to a form, before entering any code in one of its event procedures.

Answer 2

For the life of me I cannot seem to figure out how I'm adding this variable.  I still don't see how I declare the variable inside the form code but outside of any sub.  I choose to test with the RepCode field and added this to the original code:

Private Sub Command974_Click()

    DoCmd.RunCommand acCmdSelectRecord

    m_RepCode = Me.RepCode

    DoCmd.RunCommand acCmdCopy

End Sub

No declaration has been made and I'm sure that I messed up the code even if I had. 

Sorry to be dense.

Ken

Answer 3

The declaration section is at the top of a code window. The two dropdowns are at "(General)" and "(Declarations)".

Answer 4

Thanks for the input.  I will have to Google form-level variables.  I don't understand Access well enough to apply your suggestion.

So SO much to learn.

Thank you, Tom ----

Ken

Answer 5

You would save the values to form-level variables:

(in the declaration section of your form)

private m_LastName as String

'etc.

(in the Copy button's Click event)

m_LastName = Me.LastName  'Assuming you have a control named LastName

'etc.

(in the Paste button's Click event)

Me.LastName = m_LastName

'etc.