Access Copy Entire Column to Clipboard

I'm using the following code that produces an error message below.

Error.JPG
Option Compare Database
Option Explicit

Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) _
   As Long
Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) _
   As Long
Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, _
   ByVal dwBytes As Long) As Long
Declare Function CloseClipboard Lib "User32" () As Long
Declare Function OpenClipboard Lib "User32" (ByVal hwnd As Long) _
   As Long
Declare Function EmptyClipboard Lib "User32" () As Long
Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, _
   ByVal lpString2 As Any) As Long
Declare Function SetClipboardData Lib "User32" (ByVal wFormat _
   As Long, ByVal hMem As Long) As Long

Public Const GHND = &H42
Public Const CF_TEXT = 1
Public Const MAXSIZE = 4096
'The following procedure illusrtrates how to send information to the Clipboard.
Function ClipBoard_SetData(MyString As String)
   Dim hGlobalMemory As Long, lpGlobalMemory As Long
   Dim hClipMemory As Long, X As Long

   ' Allocate moveable global memory.
   '-------------------------------------------
   hGlobalMemory = GlobalAlloc(GHND, Len(MyString) + 1)

   ' Lock the block to get a far pointer
   ' to this memory.
   lpGlobalMemory = GlobalLock(hGlobalMemory)

   ' Copy the string to this global memory.
   lpGlobalMemory = lstrcpy(lpGlobalMemory, MyString)

   ' Unlock the memory.
   If GlobalUnlock(hGlobalMemory) <> 0 Then
      MsgBox "Could not unlock memory location. Copy aborted."
      GoTo OutOfHere2
   End If

   ' Open the Clipboard to copy data to.
   If OpenClipboard(0&) = 0 Then
      MsgBox "Could not open the Clipboard. Copy aborted."
      Exit Function
   End If

   ' Clear the Clipboard.
   X = EmptyClipboard()

   ' Copy the data to the Clipboard.
   hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)

OutOfHere2:

   If CloseClipboard() = 0 Then
      MsgBox "Could not close Clipboard."
   End If

   End Function

Open in new window


Ref.JPGError.JPG
shieldscoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
what if you adding:

 Microsoft Office 16.0 Access Database Engine Object Library

Open in new window

?
0
Gustav BrockCIOCommented:
Yes. DAO has been unchecked. It is even viewable on your screenshot:

  • Microsoft Office 16.0 Access database engine Object Library

/gustav
0
shieldscoAuthor Commented:
My mistake ..... new error message after correcting reference:
Error1.JPG
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Gustav BrockCIOCommented:
You cannot close ws after setting it to Nothing.

/gustav
0
shieldscoAuthor Commented:
Disregard previous comment

My mistake ..... new error message after correcting reference
Error.JPG
0
Gustav BrockCIOCommented:
If you have that function declared, it could be a copy-paste error.
Delete the offending line and retype it.

/gustav
0
shieldscoAuthor Commented:
After deleting and retyping same error message
0
Gustav BrockCIOCommented:
The you are calling the function incorrectly. Should be:

x = Foo("") 
'or: 
Foo ""

Open in new window

/gustav
0
Helen FeddemaCommented:
Do you have a command button called Command16?  Perhaps you renamed it with a more meaningful name (a good idea!).  In that case, just replace Command16 with the new command button name, and the procedure should run when the button is clicked.
0
shieldscoAuthor Commented:
Same message after renaming command button
Error.JPG
0
Helen FeddemaCommented:
Try creating the procedure from the property sheet of the control, then copy the code into it and delete the old procedure.
0
shieldscoAuthor Commented:
after copying and pasting same error message
0
Gustav BrockCIOCommented:
You should set

Option Explicit

Open in new window

in the top of all your modules.

That would show you that there is no Cuentdb

And be more careful. You also miss a space before FROM.

/gustav
0
shieldscoAuthor Commented:
After verifying same error message
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
what if try:

call clipboard_setdata("")

?
0
Gustav BrockCIOCommented:
Then continue deleting and retyping.
And why do you have that leading white-space?

/gustav
0
shieldscoAuthor Commented:
After cleanup new error message

Error.JPG
0
Gustav BrockCIOCommented:
Yes. Correct that. See my previous comment. It should be CurrentDb.

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shieldscoAuthor Commented:
Thanks -
0
shieldscoAuthor Commented:
Final Code:

Option Compare Database
Option Explicit

Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) _
   As Long
Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) _
   As Long
Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, _
   ByVal dwBytes As Long) As Long
Declare Function CloseClipboard Lib "User32" () As Long
Declare Function OpenClipboard Lib "User32" (ByVal hwnd As Long) _
   As Long
Declare Function EmptyClipboard Lib "User32" () As Long
Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, _
   ByVal lpString2 As Any) As Long
Declare Function SetClipboardData Lib "User32" (ByVal wFormat _
   As Long, ByVal hMem As Long) As Long

Public Const GHND = &H42
Public Const CF_TEXT = 1
Public Const MAXSIZE = 4096

'The following procedure illusrtrates how to send information to the Clipboard.

Function ClipBoard_SetData(MyString As String)
   Dim hGlobalMemory As Long, lpGlobalMemory As Long
   Dim hClipMemory As Long, X As Long

   ' Allocate moveable global memory.
   '-------------------------------------------
   hGlobalMemory = GlobalAlloc(GHND, Len(MyString) + 1)

   ' Lock the block to get a far pointer
   ' to this memory.
   lpGlobalMemory = GlobalLock(hGlobalMemory)

   ' Copy the string to this global memory.
   lpGlobalMemory = lstrcpy(lpGlobalMemory, MyString)

   ' Unlock the memory.
   If GlobalUnlock(hGlobalMemory) <> 0 Then
      MsgBox "Could not unlock memory location. Copy aborted."
      GoTo OutOfHere2
   End If

   ' Open the Clipboard to copy data to.
   If OpenClipboard(0&) = 0 Then
      MsgBox "Could not open the Clipboard. Copy aborted."
      Exit Function
   End If

   ' Clear the Clipboard.
   X = EmptyClipboard()

   ' Copy the data to the Clipboard.
   hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)

OutOfHere2:

   If CloseClipboard() = 0 Then
      MsgBox "Could not close Clipboard."
   End If

   End Function

Open in new window


Private Sub ClipBoard_Click()
Dim sSQl As String

Dim rst As DAO.Recordset
Dim sEq As String

ClipBoard_SetData ("")



sSQl = "SELECT tblContractorLineClaimCount.[QIC Appeal Number] FROM tblContractorLineClaimCount"
Set rst = CurrentDb.OpenRecordset(sSQl)



rst.MoveFirst

Do Until rst.EOF
    If sEq = "" Then
       sEq = Nz(rst.Fields("QIC Appeal Number"), "") & vbCr
       Else
       sEq = sEq & Nz(rst.Fields("QIC Appeal Number"), "") & vbCr
      End If
    rst.MoveNext
  Loop
     If sEq <> "" Then ClipBoard_SetData (sEq)

 
  Set rst = Nothing
  SysCmd acSysCmdSetStatus, "done."
End Sub

Open in new window

0
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
well, I thought I was the first to resolve your initial error? : /
0
shieldscoAuthor Commented:
Ryan - sorry it did not resolve my initial error (call clipboard_setdata("") ... please look at my initial post.
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
My mistake ..... new error message after correcting reference

I'm mentioning the error in your question relating to error: User-defined type not defined not the second question regarding clipboard_setdata.

the error in your question was being resolved by adding the correct library in References.

pls take note of that
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.