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
The command or action 'Copy' isn't avaliable now.
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: The command or action 'Copy' isn't avaliable now.
How do you use RunCommand acCmdCopy ?
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
Re: The command or action 'Copy' isn't avaliable now.
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
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
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: The command or action 'Copy' isn't avaliable now.
Have you changed the setting for the "Behavior entering field" option in File > Options, Client Settings?
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:
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".
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
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
Re: The command or action 'Copy' isn't avaliable now.
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
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
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: The command or action 'Copy' isn't avaliable now.
You could add the following at the beginning of the code:
and at the end
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
Code: Select all
Application.SetOption "Behavior Entering Field", lngEnterBehavior ' restore original setting
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
Re: The command or action 'Copy' isn't avaliable now.
Thank You Hans
Kindest Regards,
Mohamed
Kindest Regards,
Mohamed