use VBA to copy specific field in table with one record to clipboard

[Cottonshirt]

good morning,



I am trying to add some functionality to a data input form.
specifically, I want to be able to paste one of two pre-written texts into a control on the form

the texts are in the fields of a table with one record. so all I am trying to do is

a. copy the content of a text field to the clipboard
b. paste the clipboard to a control on an open form

turns out not to be as simple as I thought.

I have a button with the following code behind its On Click event, but I can't get it to select the specific field of the table that I want it to copy. I have tried the following

1. tbl_paste.outdoor.SetFocus - this returned a runtime error 424
2. outdoor.SetFocus - this returned a runtime error 424
3. Me.CurrentRecord.outdoor.setFocus - this returned a Compile Error: Invalid qualifier

the last didn't work because I overlooked that Me refers to the current form, whereas I want to refer to the current table. as a kind of desperate last resort I tried looking online for a Tables! version of the Forms! collection, without any luck.

so, am I on the right track here and is there a way to get it to copy a specific field, or should I be using a different approach altogether.

Private Sub cmdtextpaste_Click()
Me.Painting = False
DoCmd.OpenTable "tbl_paste", acViewNormal
` need something to set focus to appropriate field'
DoCmd.RunCommand acCmdCopy
DoCmd.Close acTable, "tbl_paste", acSaveNo
Me.Painting = True
End Sub

as a kind of side issue, I didn't want it to actually open the table, merely to have access to it but I couldn't think of any other way of doing that.


any input greatly appreciated,

Cottonshirt

[Welshgasman]

You could put a very small control on the form, so small, as to be almost invisible.
Then use DLookUp() to get the value and set that control to that value and set focus to it, then copy that.?

However, why not just use Dlookup() on the second form?

Plenty of examples to copy to clipboard. This is one I participated in https://www.access-programmers.co.uk.../#post-1778582

[Cottonshirt]

However, why not just use Dlookup() on the second form?

at least one of us is confused. I wasn't aware that there was a "second form."

either way, use of Dlookup() is not, as far as I am aware, possible in this scenario. the first argument to the function is a text expression that "identifies the field whose value you want to return." since the whole point of the exercise is to decide which field I want at the point of data entry, this seems to be putting the cart before the horse. you might be right, but I'm not clever enough to figure out how to write "expression" in such a way that the user can choose at point of entry.

Plenty of examples to copy to clipboard.

you're right, there are. hundreds of them. I read quite a few before posting my question, and I have since read the ones you linked to. but they all fall into one of two quite distinct scenarios:

1. they are pasting text that has been hard coded into the vba function. this is no advantage over what I'm doing at the moment.
2. they are using setFocus to paste text from a form to the clipboard. this is the exact opposite of what I want to do.

none of the examples I have looked at are, and please do correct me if I'm wrong, taking text from a field in a table and copying it to the clipboard so that it can then be pasted into the form.


thanks for engaging,


Cottonshirt

[Welshgasman]

If you just want a value from a field in a table to be 'copied' into a control on a form, then you can either set the source to be a Dlookup(), but then you would never be able to change that, or do exactly that in code?
No need for any copying involved?

[Micron]

I don't know how you know which field it is you want, but since you know, you could probably figure it out based on the following

Dim rs As DAO.Recordset
Dim strField As String

Set rs = CurrentDb.OpenRecordset("SELECT * FROM Table1")
strField = rs.Fields(1).Name
MsgBox DLookup(strField, "Table1")
Set rs = Nothing

I agree that there's no point of retrieving a value then putting it on the clipboard then retrieving it from the clipboard when you can do it directly. Anyway, I hope that solves the issue about how to dynamically use DLookup.

[moke123]

You can read and write to the clipboard with this:

Option Compare Database
Option Explicit


Private Declare Function OpenClipboard Lib "user32.dll" (ByVal hWnd As Long) As Long
Private Declare Function EmptyClipboard Lib "user32.dll" () As Long
Private Declare Function CloseClipboard Lib "user32.dll" () As Long
Private Declare Function IsClipboardFormatAvailable Lib "user32.dll" (ByVal wFormat As Long) As Long
Private Declare Function GetClipboardData Lib "user32.dll" (ByVal wFormat As Long) As Long
Private Declare Function SetClipboardData Lib "user32.dll" (ByVal wFormat As Long, ByVal hMem As Long) As Long
Private Declare Function GlobalAlloc Lib "kernel32.dll" (ByVal wFlags As Long, ByVal dwBytes As Long) As Long
Private Declare Function GlobalLock Lib "kernel32.dll" (ByVal hMem As Long) As Long
Private Declare Function GlobalUnlock Lib "kernel32.dll" (ByVal hMem As Long) As Long
Private Declare Function GlobalSize Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Function lstrcpy Lib "kernel32.dll" Alias "lstrcpyW" (ByVal lpString1 As Long, ByVal lpString2 As Long) As Long
 


Public Sub SetClipboard(sUniText As String)
    Dim iStrPtr As Long
    Dim iLen As Long
    Dim iLock As Long
    Const GMEM_MOVEABLE As Long = &H2
    Const GMEM_ZEROINIT As Long = &H40
    Const CF_UNICODETEXT As Long = &HD
    OpenClipboard 0&
    EmptyClipboard
    iLen = LenB(sUniText) + 2&
    iStrPtr = GlobalAlloc(GMEM_MOVEABLE Or GMEM_ZEROINIT, iLen)
    iLock = GlobalLock(iStrPtr)
    lstrcpy iLock, StrPtr(sUniText)
    GlobalUnlock iStrPtr
    SetClipboardData CF_UNICODETEXT, iStrPtr
    CloseClipboard
End Sub
 
Public Function GetClipboard() As String
    Dim iStrPtr As Long
    Dim iLen As Long
    Dim iLock As Long
    Dim sUniText As String
    Const CF_UNICODETEXT As Long = 13&
    OpenClipboard 0&
    If IsClipboardFormatAvailable(CF_UNICODETEXT) Then
        iStrPtr = GetClipboardData(CF_UNICODETEXT)
        If iStrPtr Then
            iLock = GlobalLock(iStrPtr)
            iLen = GlobalSize(iStrPtr)
            sUniText = String$(iLen \ 2& - 1&, vbNullChar)
            lstrcpy StrPtr(sUniText), iLock
            GlobalUnlock iStrPtr
        End If
        GetClipboard = sUniText
    End If
    CloseClipboard
End Function


Sub TestCB()

     Dim strMsg As String
     strMsg = "Hello World"

     SetClipboard strMsg

End Sub

BTW, if you plan on distributing as a runtime file you will not have access to a right click copy/paste shortcut menu.

You can add one with this.
I believe you need to run it on startup and then it will be available in the properties menu of controls you wish to allow it in.

'-----------------------------------------------------------------------------'
' General Clipboard context menu, the basis for all forms                     '
'-----------------------------------------------------------------------------'
Public Function CreateGeneralClipBoardMenu()
    On Error Resume Next
    CommandBars("GeneralClipboardMenu").Delete


    Dim cmb As CommandBar
    Set cmb = CommandBars.Add("GeneralClipboardMenu", msoBarPopup, False, False)


        With cmb
            .Controls.Add msoControlButton, 21, , , True  ' Cut
            .Controls.Add msoControlButton, 19, , , True  ' Copy
            .Controls.Add msoControlButton, 22, , , True  ' Paste
        End With


    Set cmb = Nothing
End Function

[Gicu]

I think the solution is easier than what you are trying to do, please have a look at the attached sample.

Cheers,
Vlad

[moke123]

I am trying to add some functionality to a data input form.
specifically, I want to be able to paste one of two pre-written texts into a control on the form

Isn't this what a combox is for?

the texts are in the fields of a table with one record. so all I am trying to do is

You dont store data in field names.

so, am I on the right track here and is there a way to get it to copy a specific field, or should I be using a different approach altogether.

How is "It" deciding what value to use?

[Cottonshirt]

I think the solution is easier than what you are trying to do...

I like the fact that you are thinking outside the box and looking for alternative solutions, but I'm afraid this doesn't work.

left out of my problem description is that I want the control on the form to accept either one of the two pre-written texts, or permit me to enter some text myself. at the moment it is just a text box and I type the entries by hand. the two most common entries are the ones I have entered in the fields of tbl_paste, but I also need the option to be able to enter free text myself.

having tested this myself this morning I can confirm that, even if you include a blank entry in the list, a combo-box simply doesn't permit typing over the entry, and a list box permits typing but then displays the error that the text you typed is not an entry in the list. therefore none of the available list options achieve the desired result.

nice idea, though.


Cottonshirt

[moke123]

I like the fact that you are thinking outside the box and looking for alternative solutions, but I'm afraid this doesn't work.

left out of my problem description is that I want the control on the form to accept either one of the two pre-written texts, or permit me to enter some text myself. at the moment it is just a text box and I type the entries by hand. the two most common entries are the ones I have entered in the fields of tbl_paste, but I also need the option to be able to enter free text myself.

having tested this myself this morning I can confirm that, even if you include a blank entry in the list, a combo-box simply doesn't permit typing over the entry, and a list box permits typing but then displays the error that the text you typed is not an entry in the list. therefore none of the available list options achieve the desired result.

nice idea, though.


Cottonshirt

I think you missed some things in your testing.

See attached database with 2 comboboxes. One allows free text and the other doesn't.
If you compare the properties of the 2 you should see how its done.

here's a hint

[Cottonshirt]

How is "It" deciding what value to use?

"It" isn't deciding. I am.

the original plan was to have two buttons on the form. if either of the two texts applied for this record, I would click the button that related to the text I wanted. if neither text applied I can simply enter free text of my own.

from this you might suppose that it would be easier to have the text hard coded into the button (not that I actually know how to do that, but it has the virtue of at least sounding easier than what I am trying to do). except that the texts themselves change every day. the two I'm using today are different from the two I used yesterday, and they will be different again tomorrow. they re-cycle every thirty two days. it seemed simpler to have a table that is easily edited rather than have the text hard coded into a form that would then need to be opened twice before I can start work for the day.

thanks for engaging,


Cottonshirt

[Gicu]

My suggestion was not built on the combo box, it was just using that to fill the text box with whatever you've chosen in the combo (rather than being limited to two buttons as you say). Once the text box is populated it is fully editable if needed.... So the text box is your target, not the combo (which simply acts as a "switch" to deliver the "canned" data from the table).

Cheers,

[Cottonshirt]

My suggestion was not built on the combo box, it was just using that to fill the text box with whatever you've chosen in the combo (rather than being limited to two buttons as you say). Once the text box is populated it is fully editable if needed.... So the text box is your target, not the combo (which simply acts as a "switch" to deliver the "canned" data from the table).

thank you. yes I understood that the combo-box was a tool, not the target, but events have overtaken that suggestion so that I do not need an extra combo-box but can simply replace the existing text box with a combo-box. however, we should not overlook that it was you who pointed us down this road. I was looking at copy-paste options and it was you who steered us in the right direction. thank you very much indeed.

I think you missed some things in your testing.

thank you.

I had thought of using one of these some time ago but I tried it back then and could not get it to let me type my own text. when Vlad redirected my thinking I thought it worth taking another look and got the same result. it turns out the Wizard is not much of a wizard after all and there are properties it doesn't tell you about. thank you very much for persisting in the face of my ignorance.

you can't imagine how long I've thought about getting this done, or how much difference it's going to make. 2 clicks of a mouse instead of typing 65 characters, and multiply that by around 3000 records a day and it soon adds up. thank you to every one who participated, the road to success often requires a few mis-steps.

have a cold one on me.






many thanks,


Cottonshirt

[Minty]

If your repeated text data is on a regular cycle that you can predict

they re-cycle every thirty two days

Then put all the values into a table with a suitable key field that you can identify based on the 32 day cycle.
Then use that data to populate a combo with the two values you want each day. Choose one of the two values from you combo or type random free text onto the bound control.

[moke123]

@Minty, I had the same thoughts earlier this morning but couldn't respond till now.

Not knowing the criteria for the 32 day cycle, my first thoughts were putting the 64 possibilities into a lookup table with a true/false field as a flag.
In the combobox on the form, include the true/false field in the rowsource and set its criteria to true.
Each day you simply select the 2 phrases of the day by setting their flag to true and the remaining ones to false. The changes are then reflected in the combobox which would only show the chosen phrases.
This would also give you the option of being able to have 3 or more phrases of the day should conditions warrant it.

it turns out the Wizard is not much of a wizard after all and there are properties it doesn't tell you about.

Cant disagree with you there. There's a lot of things you can do manually with listboxes and combos that the wizards dont expose.
Avoid using them, except for the simplist of things, if you can.