Hopefully there is at least one other object that he can set the focus to so that he can hide the text box that he wants to copy from. If not, spend the extra nickel and create one.
I think your problem is that the text box is not yet filled with any data when the form first opens. The code is running so fast compared to the bound fields populating with your data. I had a simular problem once. My solution was to use the timer event and one second after the form was opened I "did something". You could try setting the record source of the Text0 text box in the forms OnOpen event and then the rest of your code to set the focus and copy. Or you could use the Sleep API and delay the copy command by one second.
The problem of using the, DoCmd.RunCommand acCmdCopy, statement is that my default setting for the “Behavior Entering Field” property of the database is set to “Go to start of field” and not “Select entire field”, (Options).
The solution is to select the entire field before trying to copy the data to the clipboard.
Put in a standard module (works in 2010 and 2013, not sure about newer versions)
Code:
Public Sub CopyToClip(ByVal Expression As String)
'http://bytecomb.com/copy-and-paste-in-vba/
With CreateObject(DATAOBJECT_BINDING)
.SetText Expression
.PutInClipboard
End With
End Sub
Put this in the declarations section of the same module
Code:
Const DATAOBJECT_BINDING As String = "new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}" 'http://bytecomb.com/copy-and-paste-in-vba/
To call it:
Code:
CopyToClip (textstring)
I have used it in an onclick event, (user single clicks on a textbox and it copies the contents which can then be pasted in another app with the paste command.
Mouseup event where user double clicks or highlights the portion of text needed, however, I imagine it can be called from vba without needing user intervention if you want it to refer to a textbox.
I used this a few years back.
Needs the Microsoft Forms reference I seem to recall.
Code:
Private Sub cmdCopy_Click()
Dim cb As Object
On Error GoTo Err_Handler
Set cb = New MSForms.DataObject
cb.SetText Me.contact_reference
cb.PutInClipboard
Set cb = Nothing
Exit_Handler:
Exit Sub
Err_Handler:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Handler
End Sub