The command or action 'Copy' isn't avaliable now.

[MSingh]

Hi,

Lately I have been getting the following error:

Run-time error '2046:
The command or action 'Copy' isn't available now.

This occurs in any database where RunCommand acCmdCopy is used.
I have tried compact & repair to no avail.

Your assistances would be greatly appreciated.

Kind Regards,
Mohamed

[HansV]

How do you use RunCommand acCmdCopy ?

[MSingh]

Hi Hans,

This is one example:
Code from Allen Browne's converting SQL to VBA:

Private Sub cmdSql2Vba_Click()
Dim strSQL As String
'Purpose: Convert a SQL statement into a string to paste into VBA code.
Const strcLineEnd = " "" & vbCrLf & _" & vbCrLf & """"

If IsNull(Me.txtSql) Then
Beep
Else
strSQL = Me.txtSql
strSQL = Replace(strSQL, """", """""") 'Double up any quotes.
strSQL = Replace(strSQL, vbCrLf, strcLineEnd)
strSQL = "strSql = """ & strSQL & """"
Me.txtVBA = strSQL
Me.txtVBA.SetFocus
RunCommand acCmdCopy
End If
End Sub

This worked perfectly for a long time.
Justly recently it fails on: RunCommand acCmdCopy

Kind Regards,
Mohamed

[HansV]

Have you changed the setting for the "Behavior entering field" option in File > Options, Client Settings?

S228.png

If it's set to "Select entire field", the code works OK for me, but if it's set to "Go to start of field" or to "Go to end of field", I get the error that you report.

To prevent the error, you can set the option to "Select entire field" again, or modify the code to work around the problem:

Code: Select all

Private Sub cmdSql2Vba_Click()
    Dim strSQL As String
    'Purpose: Convert a SQL statement into a string to paste into VBA code.
    Const strcLineEnd = " "" & vbCrLf & _" & vbCrLf & """"

    If IsNull(Me.txtSQL) Then
        Beep
    Else
        strSQL = Me.txtSQL
        strSQL = Replace(strSQL, """", """""") 'Double up any quotes.
        strSQL = Replace(strSQL, vbCrLf, strcLineEnd)
        strSQL = "strSql = """ & strSQL & """"
        Me.txtVBA = strSQL
        Me.txtVBA.SetFocus
        ' *** Addition: select the text in the text box
        Me.txtVBA.SelStart = 0
        Me.txtVBA.SelLength = Len(Me.txtVBA.Text)
        ' *** End of addition
        RunCommand acCmdCopy
    End If
End Sub

With this change, the code works for me even if "Go to start of field" or "Go to end of field" is selected in "Behavior entering field".

[MSingh]

Thank You,
100% Correct yet again Hans!
Seems I changed the setting like you said.
Code works perfectly

Is there another way to set each option of the "Behavior entering field" without altering this global setting?

Kind Regards,
Mohamed

[HansV]

You could add the following at the beginning of the code:

Code: Select all

    Dim lngEnterBehavior As Long
    lngEnterBehavior = Application.GetOption("Behavior Entering Field") ' save current setting
    Application.SetOption "Behavior Entering Field" = 0 ' select entire field

and at the end

Code: Select all

    Application.SetOption "Behavior Entering Field", lngEnterBehavior ' restore original setting

[MSingh]

Thank You Hans
Kindest Regards,
Mohamed