Results 1 to 15 of 15
  1. #1
    Cookie is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    45

    Use form field to write and retain clinical notes against date and time stamp specific to a Record

    (MS Access - Office 365)

    By way of background, I record clinical notes for feline (cats) welfare for our charity cat rescue group. Each cat has a separate Record which is held in an MSAccess database. I use a data entry form with fields that record the specific details of each cat.

    So, one field involves the continuous day to day recording of clinical notes. I created a long text field "ClinicalNotes" to accommodate them. Next to this text box on the form I have a button that runs a small macro. The macro uses SendKeys to perform some basic tasks. (See image attached.) This results in the following:

    newer date, newer time,


    newer clinical note

    older date, older time,
    older clinical note 1

    e.g.


    02-Sep-20@9:19:57 AM

    (user starts typing newer clinical note here)

    02-Sep-20@8:28:51 AM

    Mild renal azotaemia CREA 200



    So, I use SendKeys to manage the date and time stamp which is set up in a macro that is called when a button is pressed on the Record (one of several Records that are created for each feline).



    As SendKeys appears to clear the field which causes all the previous clinical notes to be erased (which I don't want to have happen) , I have had to find a workaround to copy the existing content of the field first, then run SendKeys, and re-paste the copied contents back to the field. Then when the record is closed, the text in the ClinicalNotes field remains intact.

    When I move onto another Record. This is when the problem starts. It has to do with what is still on the clipboard active in Access.

    When the new Record field is blank, there is nothing to copy (my SendKeys routine is trying to copy text), so the clipboard retains the last pasted contents of the previous Record or, for that matter, anything it is holding . The problem is that the Record clinical notes from the previous Record may be inappropriately cloned to the new Record, or the new Record deposited with material that shouldn't be there. The new Record might be an existing record with or without clinical notes.

    I've been looking to find a way of clearing the active clipboard contents when the button next to the ClinicalNotes field on the form is pressed using a VBA or SendKeys entry in the DateTime macro.

    Can I have some help with this one please? Thank you.
    Attached Thumbnails Attached Thumbnails SendKeys DateTime Macro Steps.jpg  
    Last edited by Cookie; 09-01-2020 at 06:35 PM. Reason: Typo: not=note

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I searched and found this:

    https://answers.microsoft.com/en-us/...0-5583842e3209

    I would not store data that way, I'd have a separate record in a related table for each note.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Cookie is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    45
    Thank you.

    I included the script but it did not run as expected. It did not clear the clipboard.

    Here is the way I went about it:-

    I placed the function into the datetime macro right at the end.

    When I created the "emptycipboard" [sic] module, I ran the debugger and it appeared to have no issue with the VBA script.

    After I saved and executed the datetime macro with the suggested script, I found the SendKeys steps became dysfunctional, i.e. no date or time stamp anymore.

    I removed the function from the datetime macro, resaved the macro and closed Access. I then reopened access and behaviour for the SendKeys returned to normal.

    Notwithstanding your suggestion re storage of data, the only impediment to the functionality I require is that the clipboard isn't being cleared. The active clipboard is the Win 10 64 Bit clipboard, which can be cleared manually, of course. If it is cleared manually before running the datetime macro, the functionality is as expected.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I just tested by copy/pasting Daniel's code into a standard module and then putting this behind a button:

    EmptyClipboard

    I just pushed the button and now this is in the clipboard:



    So it worked as advertised.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Cookie is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    45
    Thank you.

    I tried the code in MSWord (Office 365) first, and it worked as advertised too.

    Option Explicit

    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 Function ClearClipboard()
    OpenClipboard (0&)
    EmptyClipboard
    CloseClipboard
    End Function

    Sub TestClipboardClear()
    Call ClearClipboard
    End Sub


    MS Access (Office 365):

    I created the module, and called the function in a new macro. I saved the macro. Then I created a button on the form to press and execute the macro.

    When I ran the macro, I got an automation message alert.

    Click image for larger version. 

Name:	automation alert.jpg 
Views:	28 
Size:	53.8 KB 
ID:	42887Click image for larger version. 

Name:	Automation Error 2766.jpg 
Views:	28 
Size:	37.6 KB 
ID:	42888

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Not sure the purpose of a sub that does nothing but call a function, but I don't think you can call a sub from a macro. Just call the function.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Cookie is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    45
    Thank you.

    I explored right clicking on the button in design view, clicking on Build Event, Code and then using the VBA editor to build the function.

    Option Explicit

    Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
    Private Declare Function EmptyClipboard Lib "user32" () As Long
    Private Declare Function CloseClipboard Lib "user32" () As Long

    Private Function ClearClipboard()
    OpenClipboard (0&)
    EmptyClipboard
    CloseClipboard
    End Function

    Sub TestClipboardClear()
    Call ClearClipboard
    End Sub

    Private Sub Command161_Click()

    End Sub


    It runs without any obvious errors when the button is pushed but it doesn't work in Access. That is, the clipboard is not empty.
    It still works in Word using the same script. Clearing the clipboard from the desktop Clipboard Settings menu (Win 10 64Bit) also works.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Can you attach the db here? Like I said, it worked as expected for me.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    Cookie is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    45
    Thank you for offering to have a look at it for me. See attached Pea Zip file.

    I have left the two buttons in place. The form text field of interest is the Clinical Notes.
    Attached Files Attached Files

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    There is no code behind the clear clipboard button. When I call the function it works as expected:

    Code:
    Private Sub Command161_Click()
      ClearClipboard
    End Sub
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    Cookie is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    45
    See Next...sorry
    Last edited by Cookie; 09-03-2020 at 08:23 PM. Reason: Duplicated Post Accidentally

  12. #12
    Cookie is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    45
    Thank you.

    Did you mean to build the event like this? :-

    Option Explicit

    Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
    Private Declare Function EmptyClipboard Lib "user32" () As Long
    Private Declare Function CloseClipboard Lib "user32" () As Long

    Private Function ClearClipboard()
    OpenClipboard (0&)
    EmptyClipboard
    CloseClipboard
    End Function

    Private Sub Command161_Click()
    ClearClipboard
    End Sub

    I can see that this isn't working for me either.

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, you can't just type the code in or it won't actually be associated with the button. See if this helps:

    http://www.baldyweb.com/FirstVBA.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    Cookie is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    45
    Thank you.

    I made a couple of worthwhile observations during this experience.

    1. A Ctrl C action on text in the field results in what appears to be one copy put in the Windows 10 Clipboard and another in a (virtual?) Access Clipboard. When a Ctrl v is executed straight after the text is copied, the text is repasted.

    2. The windows clipboard copy of the same text can be pasted any time back into the current text field using selective paste from the expanded ribbon clipboard menu of items that can be pasted.

    3. If the Clear Clipboard function is executed by pressing the button, Ctrl v afterwards shows there is nothing to paste. However, you can still paste from the ribbon clipboard if the item is still there.

    So, as per your last suggestion, I created the new VBA which I share below:

    Option Explicit

    Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
    Private Declare Function EmptyClipboard Lib "user32" () As Long
    Private Declare Function CloseClipboard Lib "user32" () As Long

    Private Function ClearClipboard()
    OpenClipboard (0&)
    EmptyClipboard
    CloseClipboard
    End Function
    Private Sub Command161_Click()
    ClearClipboard
    End Sub


    Problem solved: Works as advertised.

  15. #15
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad you got it working.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Date Time stamp for new record
    By dweekley in forum Access
    Replies: 2
    Last Post: 05-11-2017, 08:08 AM
  2. Replies: 2
    Last Post: 01-16-2015, 04:22 PM
  3. Replies: 9
    Last Post: 07-21-2014, 11:57 AM
  4. Date/Time Stamp on barcode input form
    By computersnack in forum Access
    Replies: 3
    Last Post: 04-30-2014, 07:11 PM
  5. Replies: 3
    Last Post: 12-10-2012, 05:14 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums