Possible to clear clip board by VBA in Access 2007? (1 Viewer)

Punice

Registered User.
Local time
Today, 06:30
Joined
May 10, 2010
Messages
135
I tried several suggestions that I was able to find on the 'net, but none of them
cleared the clipboard. I am aware of the manual method, but would like to know if anyone has be successful using VBA, like using a module that can be called when forms load, for example.

Thanks in advance.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:30
Joined
Oct 29, 2018
Messages
21,357
Hi. I agree with June7. Did you try opening the clipboard first before emptying it? Then, don't forget to close it after you're done.
 

isladogs

MVP / VIP
Local time
Today, 10:30
Joined
Jan 14, 2017
Messages
18,186
Although its unusual to do this, I do have code in my Sql to vba and back again utility to clear the clipboard

Add the following code to a standard module
Code:
Option Compare Database
Option Explicit

'#####################################
'API declarations
#If VBA7 Then 'A2010 and later (32/64-bit)
    Public Declare PtrSafe Function CloseClipboard Lib "user32" () As Long
    Public Declare PtrSafe Function EmptyClipboard Lib "user32" () As Long
    Public Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As Long
    Public Declare PtrSafe Function CountClipboardFormats Lib "user32" () As Long
    
#Else 'A2007and earlier
    Public Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
    Public Declare Function EmptyClipboard Lib "user32" () As Long
    Public Declare Function CloseClipboard Lib "user32" () As Long
    Public Declare Function CountClipboardFormats Lib "user32" () As Long
#End If
'#####################################


Public Sub ClearClipboard()
    OpenClipboard 0&
    EmptyClipboard
    CloseClipboard
End Sub

Then just add the line ClearClipboard where needed

I've not tested this in A2007, but if that's the only version you need this for, you would only need the API declaration part in the #Else section. The #If VBA7 is for A2010 & later
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 10:30
Joined
Sep 21, 2011
Messages
14,038
How about pasting an empty string?, that works for me?
 

Punice

Registered User.
Local time
Today, 06:30
Joined
May 10, 2010
Messages
135
To all of you who responded to my post - firstly, thanks. Secondly, the reason for my asking for 'VBA' way to empty the clip board is that I want to do that, a when I open a form that will bring forward data from the previous form to reduce the entering of data that doesn't change from form-to-form during legs of a trucking company's trip data for semi-trailer trucks (aka: lories?).

In the current db, after forwarding and, sometimes, manually entering data on a form, that nasty 'paste error' message appears. I want to prevent that from happening by incorporating the 'emptying' code in an 'on-load' sub-routine.

Manually emptying the clip board, in Access 2007, using the provision in Access 2007's banner does the job.

I found and tested code that the June7's link reference. It didn't work. I think it pertained to Access 2003 and earlier (?). It doesn't have the fourth line of code that the example that Isladog posted.


I will test Isladog's code, next, and get back to you.
 
Last edited:

Micron

AWF VIP
Local time
Today, 06:30
Joined
Oct 20, 2018
Messages
3,476
Well, maybe this db is just for you and you're ok with this approach, but I would be upset as a user whose clipboard just got emptied if I went to retrieve something and it was gone. Then again, maybe users don't know that you can retrieve one of several entries stored there so it won't matter. But if you open form A then want to pass data to form B, there are far easier ways to do this without deleting everything on a clipboard. One is to pass values with OpenArgs. Another, pass a recordset clone. Another, hide form A, retrieve values from B, when B closes, either reveal or close A. Etc.
 

isladogs

MVP / VIP
Local time
Today, 10:30
Joined
Jan 14, 2017
Messages
18,186
Punice
I hadn't previously checked the SO link in June's post and therefore wasn't aware that the code is almost identical..

The extra API line CountClipboardFormats in my code sample is used in my app to check the clipboard..not to clear it . I know the code works as I've used it for many years with different versions of Access though not recently with A2007.

If you still can't get it to work, can I suggest you download my example app and test the code in that app.

Micron,
In my app, the clipboard is cleared on a 'Clear Windows' button click.
If you are opposed to it being done without 'warning' I would agree with you.
 

Punice

Registered User.
Local time
Today, 06:30
Joined
May 10, 2010
Messages
135
Isladogs, my version of A2007 doesn't contain the "Microsoft Forms 2.0 Object Library". Is what it does contain in another library or what?

Using you your code (above), when I insert 'ClearClipboard' in a control's VBA and run that code, I get a message saying "Expected variable or procedure, not a module". I get that using 'Call Clearclipboard', clearclipboard() and Clearclipboard(08).

Using this - DoCmd.OpenModule "ClearClipboard", "ClearClipboard" - opens the module and places the cursor before 'OpenClipboard (0&) or 'OpenClipboard() or 'OpbeClipboard', whichever of those that I try).

Do I need a 'run command' statement or how do I get what I want to happen?

Also, while the A2007 clipboard window is open and contains 'items' in it, are the items erased from the window, like when I clear the clipboard manually, and, if they are cleared by the VBA, is the clipboard actually cleared, anyway & how can I see that it is clear. (F8ing in the module to the sub's end, to 'run' the sub, then, closing and reopening the clipboard window still shows the items in it.??
 

isladogs

MVP / VIP
Local time
Today, 10:30
Joined
Jan 14, 2017
Messages
18,186
You don't need the MS Forms reference for this code.
If you want it for other purposes, you need to browse for fm20.dll - probably in the windows\system32 folder BUT its not required here.

Make sure the module containing that code isn't called ClearClipboard as that will cause Access to get confused. If you haven't yet looked at my example app I recommend you do so to see how I use it.

Not sure what the clipboard window is that you are referring to. Possibly the 'stackable' Office clipboard which I don't use The easiest way to see if the clipboard has been cleared is to try pasting into an empty Notepad window.

Hope that helps slightly but I'm not sure I fully understood your post
Just going out for a few hours so unlikely to be able to reply till later if you do need more help
 

Punice

Registered User.
Local time
Today, 06:30
Joined
May 10, 2010
Messages
135
I used your sample that you referred to in my module, with a name as you suggested. I know that the call from my sub 'runs' the module, because I can halt it using breakpoint setting. Whether it clears the clipboad, is unknown, because one of those nasty 'paste error' messages appeared while using the
db, today, to open a previous form identical in format and containing similar data in the fields as the other one.

I am not familiar with 'stackable office clipboard' or how to get the db to use any other one.

To respond to Micron, I use code to carry data forward from one form to other ones. Also, the 'paste error' appears, sometimes, when I
the previous form, for example, which I guess uses the clip board during that
transfer process.

That's my story. My users can still empty the clipboard, manually, when they encounter the error. They won't be happy, but I can handle the bitching.

Thanks for all of your help.
 

Micron

AWF VIP
Local time
Today, 06:30
Joined
Oct 20, 2018
Messages
3,476
If all you're trying to do here is carry forward info from one form to another, there are (IMHO) ways to do this that are far less "buggy" and easier to boot. Not in any particular order of preference, they are:
- pass data via open args property
- leave 1st form open but invisible until data is migrated, then close it if that makes sense
- create custom properties for form2 and pass them from form1 or a function in a standard module.
 

Punice

Registered User.
Local time
Today, 06:30
Joined
May 10, 2010
Messages
135
I call this from a sub using this: ClearClipboard. The module's name is: MyClearClipboard. This is it:

UG Added Code Tags
Code:
Option Compare Database
Option Explicit


'#####################################
'API declarations
#If VBA7 Then 'A2010 and later (32/64-bit)
    Public Declare PtrSafe Function CloseClipboard Lib "user32" () As Long
    Public Declare PtrSafe Function EmptyClipboard Lib "user32" () As Long
    Public Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As Long
    Public Declare PtrSafe Function CountClipboardFormats Lib "user32" () As Long
    
#Else 'A2007and earlier
    Public Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
    Public Declare Function EmptyClipboard Lib "user32" () As Long
    Public Declare Function CloseClipboard Lib "user32" () As Long
    Public Declare Function CountClipboardFormats Lib "user32" () As Long
#End If
'#####################################


Public Sub ClearClipboard()
    OpenClipboard (0&)
    EmptyClipboard
    CloseClipboard
End Sub
 
Last edited by a moderator:

June7

AWF VIP
Local time
Today, 02:30
Joined
Mar 9, 2014
Messages
5,423
I agree with Micro, better ways to pass info between forms than messing with clipboard.

I will add global variables and TempVars to the list.
 

Punice

Registered User.
Local time
Today, 06:30
Joined
May 10, 2010
Messages
135
I don't know what this method is called, but it is how I'm bring forward what I want from the previous form to the new form(s) us a control labeled "Cont" to call a sub-routine with this code in the body of it:

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdRecordsGoToNew
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdPaste

'If you want a field carried forward, insert an apostrophe before the
'Me.field name'.
'If you DO NOT want a field carried forward & cleared on the new form,
'do the following:
' Like: Me.T_TracDrvr = "" to clear text fields
' Like: Me.T_RcdDate = Null to clear date or currency fields

Me.[T_XingCity] = "" 'do not carry XingCity forward
Me.[T_Route] = Null 'do not carry routes forward
'Me.[T_TrlrStatus] 'do carry TrlrStatus forward

I've used this method in earlier A2007 databases and never encountered the 'paste error clipboard' problem.
 

Punice

Registered User.
Local time
Today, 06:30
Joined
May 10, 2010
Messages
135
isladog, have you seen the code that I am using to clear the clipboard? It is what I copied from your example. Am I applying it wrong or what?
 

Punice

Registered User.
Local time
Today, 06:30
Joined
May 10, 2010
Messages
135
Gasma, I don't know what you mean with 'posting an empty string'. What, specifically, to you want me to post?
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:30
Joined
Sep 21, 2011
Messages
14,038
Gasma, I don't know what you mean with 'posting an empty string'. What, specifically, to you want me to post?

I said 'paste'. I just checked, as my typing is getting bad these days. :(

If you paste "" to the clipboard, that appears to clear everything?
 

isladogs

MVP / VIP
Local time
Today, 10:30
Joined
Jan 14, 2017
Messages
18,186
isladog, have you seen the code that I am using to clear the clipboard? It is what I copied from your example. Am I applying it wrong or what?

I've not kept up with this thread.
The code in post #14 is from my example which I know works in A2007.

If that doesn't work for you then you must be either applying or testing it differently somehow.
Did you check how I used it in the form used in my example?
 
Last edited:

Users who are viewing this thread

Top Bottom