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

    Multiple Go To Controls in SendKeys Macro does not appear to work. MS Access Office 365, 2020


    In this simple macro, exploring the behaviour of Go To Control and SendKeys in a Cat Record Database, the execution of all or part of the macro appears to be inhibited after saving and executing the macro. Does this mean only one control can be included in the macro?

    CatName is the name of a field on the form of (say Record 1) which holds the cat's name. (CatName is a column in the database spreadsheet)
    ClinicalNotes is the name of a field on the form Record 1 where the cat's name (CatName) can be pasted. (ClinicalNotes is a column in the same database spreadsheet as CatName)

    The macro is set out like this:-

    Go To Control CatName Targets the CatName Field

    SendKeys (^c) Copies the contents of the field to the MS Access and Windows clipboards.

    Go To Control
    ClinicalNotes Targets the ClinicalNotes Field

    SendKeys (^v) Pastes the contents into the ClinicalNotes field.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You can copy the contents of a field via:
    ClinicalNotes=Catname

    No control C needed. No jumping around.

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

    Where is that statement put and what is the syntax? Sounds interesting but...... wouldn't it be that if I were to put "ClinicalNotes=Catname" as-is in the macro to replace the (^c) under Go To Control CatName, wouldn't that just paste "ClinicalNotes=CatName" into the CatName field?

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    I'm not a macro expert and there may be better ways to do it, but here's one way:

    Click image for larger version. 

Name:	cat.png 
Views:	25 
Size:	11.5 KB 
ID:	42927

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

    Interesting idea there. I tried that and it pasted [TempVars]![tvCatName] (as shown) in to the ClinicalNames field.

  6. #6
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    I tried that and it pasted [TempVars]![tvCatName] (as shown) in to the ClinicalNames field.
    I can't tell from that if it worked for you or it literally copied "[Tempvars[![tvCatName]" into ClinicalNames.
    In my test, I had "Felix" in catname and it copied "Felix" into ClinicalNames.

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

    Sorry, "(as shown)" was meant to imply that it literally pasted [Tempvars[![tvCatName] not Felix, if I had put Felix as the CatName. So that is interesting. Did you have that under a Go To Control ?

  8. #8
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Did you have that under a Go To Control ?
    No. If you examine the image in post #4, you will see that is is the On_click event of a command button.
    Last edited by davegri; 09-06-2020 at 09:20 PM. Reason: clarif

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

    The imbedded macro on click, see attached, produces the literal text [Tempvars[![tvCatName] in the ClinicalNotes field for me.


    Click image for larger version. 

Name:	Snappy-Cmd182.jpg 
Views:	19 
Size:	49.6 KB 
ID:	42928

  10. #10
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    You're missing the "="
    Value =[tempvars]![tvCatName]

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

    That fixes the paste issue (great observation, not sure - I many need new glasses) but there is a drawback.

    If that is called after text has already been laid in the field (a sequence of date & time stamps with clinical notes, it erases the whole of the ClinicalNotes field except the pasted CatName. Thus this button cannot stay on the form the way it is because the user could erase all the clinical notes accidentally if it's pressed.

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Suggest you use an event procedure instead
    This will add the cat name to the existing clinical notes with a line return added first.

    Code:
    Private Sub CatName_AfterUpdate()
    
    Me.ClinicalNotes = Me.ClinicalNotes & vbNewLine & Me.CatName
    
    End Sub
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

    Can you elaborate on how I can include that please.

  14. #14
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    This is one way of creating an event procedure. It takes just a few seconds (less time than needed to explain it!):
    Open the form in design view & click the CatName textbox to select it. The Properties sheet should open ...if not, click that in the Design ribbon...
    Select the AfterUpdate event and click the small ... button at the right then select Code Builder & click OK

    Click image for larger version. 

Name:	Capture.PNG 
Views:	18 
Size:	9.1 KB 
ID:	42930

    The Visual Basic Editor will open to that procedure with first & last code lines already entered.
    Paste the the rest of the code supplied between those lines

    Close the VBE & save the form.Open the form normally & test the code works by entering or editing a cat name in the textbox
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  15. #15
    Cookie is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    45
    Thank you, that was very helpful. At least the CatName can be added to the ClinicalNotes textbox without the contents of the ClinicalNotes textbox being deleted. It's still a little messy though because it requires an update to the CatName box to occur.

    Ideally, I would like to lay down the cat's name next to the datetime stamp each time to indicate the clinical not written underneath was definitely for that cat. Like this:-

    07-Sep-20_@_9:04:22 PM Sylvester

    BP Systolic Low

    07-Sep-20_@_9:04:21 PM Sylvester

    Appears to have swollen liver, jaundiced.

    07-Sep-20_@_9:04:20 PM Sylvester

    5ml glucose

    07-Sep-20_@_9:04:19 PM Sylvester

    CREA 160 mmol/L


    The date and time stamp is currently being sent through SendKeys. An alternative style would be to put the cat's name under the date and time stamp, on the next line.

    The current method puts it back at the start of the chronology (earliest clinical date).

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 09-06-2020, 09:07 PM
  2. Access from Office 10 will not work in Office 365
    By Majestic Eagle in forum Access
    Replies: 3
    Last Post: 01-03-2017, 03:49 AM
  3. Replies: 6
    Last Post: 12-09-2015, 01:17 PM
  4. Using the SendKeys action in a Macro
    By john134 in forum Programming
    Replies: 5
    Last Post: 05-27-2014, 01:54 PM
  5. Replies: 20
    Last Post: 10-25-2013, 06:16 AM

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