acCommandCopy in Datasheet only copies first record

[Luchino]

Hello friends



My setup:
Table called "Contacts" with fields "ID" and "Name".

I am trying to use acCmdCopy to copy the value in "ID" when I click it. This is my code:

Private Sub ID_Click()
Me!ID.SetFocus
Me.Requery
DoCmd.RunCommand acCmdCopy
End Sub

However, regardless of the record I click, it copies the first value on the datasheet. For example, when the I sort "ID" by ascending order, if I click record 10, it will copy record 1 because it appears first in the datasheet. In descending order, if I click record 1, it will copy record 10, because it appears first in the datasheet.

I want to copy the record I click to the clipboard - where am I going wrong?

[ItsMe]

When you do this
Me.Requery

You are telling the form to take a trip to the data and it will navigate to the first Record in the Form's Recordset.

You are going to need to select a record if you want to copy the entire record and not just a single field. I do not think there is a way to use the SetFocus method on a record. You can use this for controls like a form or a textbox, etc. I think you are going to need to find the ordinal position of the record. The ordinal position within the Form's Recordset and use that value in a GoToRecord statement. Actually it would probably be best to use the Bookmark method od a DAO recordset to set focus and then the acCopy ...

GoTo Record

Dim intRecordPosition as integer
'Need the ordinal postion. Here I will guess it is five
intRecordPosition = 5
DoCmd.GoToRecord acDataForm, Me.Name, acGoTo, intRecordPosition

'   https://msdn.microsoft.com/en-us/lib.../ff194117.aspx

Or what will probably be better is to use Bookmark to set focus on the record.

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
    'rs.FindFirst "[ID] = '" & Me!ID & "'"    'for text field
    rs.FindFirst "[ID] = " & Me!ID             'for number field
          If rs.Bookmarkable = False Then
          MsgBox "Can't bookmark this record"
          Else
          Me.Bookmark = rs.Bookmark
          End If
Set rs = Nothing

'   https://msdn.microsoft.com/en-us/lib.../ff823084.aspx

[Luchino]

Thanks ItsMe!

What I wanted to do is in fact select a field, not a record - sorry! Basically, when I click "ID", I want the code to copy the ID value to the clipboard.

[ItsMe]

Directly behind the form that has the field you want to copy ...

Private Sub ID_Click()
Me.ID.SetFocus
DoCmd.RunCommand acCmdCopy
End Sub

Having said that, you may need to add additional code to make sure all of the text within the control is selected.


.

[Luchino]

Thanks again!
This was the first thing that I tried, but it gives me the error:

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

To resolve this, I came here, found a thread, and that's why I ended up putting in the "Me.Requery"

[ItsMe]

OK, no problem, it is not available because the control is busy or there is not anything selected to copy. Try placing focus on another control and then returning to the original control.

Me.SomeotherControlName.SetFocus
Me.ID.SetFocus
DoCmd.RunCommand acCmdCopy