Results 1 to 7 of 7
  1. #1
    Theof is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2019
    Posts
    8

    Question SendKeys Macro Action: Strange Behaviour

    Hi all. I'm trying to edit the criteria of a simple select query on the fly by amending the query's SQL statement. I'm trying to do this exclusively by macros and NOT by VBA. Basically, I want to append the WHERE portion to the query's SQL statement. The contents of the WHERE portion is handled by a form. Using a number of SendKeys actions, I am able to copy the content of the unbound text box that contains the WHERE statement by Ctrl + C. The SendKeys code for this is : ^(c)

    At this stage, I manually test to see if the WHERE statement was copied by pasting into notepad and it correctly pastes the WHERE statement. But for some reason, when I open the query in design view (via the same macro) and then use SendKeys to paste, the wrong thing is pasted. It's the whole SQL statement of the query that is pasted instead of just appending the WHERE portion at the end of the statement.

    The MS Access file is attached. Please help fix this. Thanks.

    I've tried both MS Access 2013 and 2019 (not on the same PC).
    Attached Files Attached Files

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    I get the below when I click on the first button?
    Not going to work anyway as the sql has a ; at the end?, so the where clause is going to come after the sql terminator?

    Learn VBA, much easier, believe me.

    Click image for larger version. 

Name:	txtfullcriteria.PNG 
Views:	21 
Size:	16.5 KB 
ID:	47335
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    You cannot requery an unbound form, what is there to query?

    Anyway, removing the requery from each macro, gives me
    Code:
    SELECT tblCustomers.rwID, tblCustomers.CustName, tblCustomers.AccountBalance
    FROM tblCustomers;
    txtDebtThreshold	txtCriteriaPredicate	txtFullCriteria
    10	WHERE (tblCustomers.AccountBalance)	WHERE (tblCustomers.AccountBalance)<=10;
    I entered 10 for the amount?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    OK, I got the macros to work. PITA TBH

    I saved the Copy Macro as a macro to be called by the Paste macro.
    I used RunCommand Copy and Paste instead of SendKeys.

    I get the code below. Still the issue with the original sql terminator.

    [code]
    SELECT tblCustomers.rwID, tblCustomers.CustName, tblCustomers.AccountBalance
    FROM tblCustomers;
    WHERE (tblCustomers.AccountBalance)<=10;
    [code]
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    Theof is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2019
    Posts
    8
    @Welshgasman

    Thanks for your efforts and replies. I should clarify a few things.

    1. The purpose of this exercise is to amend pass-through queries by appending the criteria (the WHERE portion) via the methods I am attempting.

    2. I created the sample database that I attached earlier for the purpose of help forums such as AccessForums.net

    3. Concerning the SQL statement terminator at the end of the FROM portion, I can successfully remove that also by using the SendKeys action: {BACKSPACE}

    4. Yes, the entire form is unbound but you'll observe that the Requery action specifically applies only to the [txtFullCriteria] control. This is to ensure that the value of the [txtFullCriteria] has the most recent number entered in the debt threshold control [txtDebtThreshold]. Sure there are better and more elegant ways of doing this such as using the AfterUpdate event of the [txtDebtThreshold]. I didn't bother with that at this stage because the main purpose of the exercise is to see if an existing query's SQL can be amended
    in the way I am attempting.

    5. The first command button is included because of help forums such as this one. Its purpose is to show that the macro works properly as it's the content of the [txtFullCriteria] that is copied.

    6. The second command button contains the includes further actions after the copy actions but for some reason, the wrong thing is being copied.

    7. I don't know how you got the error in your screenshot when the first button is clicked. I just tested it here and it works.

    8. Concerning using RunCommand Copy and Paste instead of SendKeys, I stopped using RunMenuCommand a few year ago when Access would complain that the action is not allowed because the database is not trusted. Specifically, I used to use it to save a record. Now I just set the form's dirty property to No. I sometimes use SendKeys to mimick: Ctrl + s

    I know how to make a database trusted and my dev folder is set as trusted in the Trust Center. The problem is I write commercial apps and I cannot always ensure that the location of the Access file will be set as trusted on client's PCs. Using SendKeys avoids the error.

    9. Please upload the access db file as amended by you so that I (and others) can study it in detail.

    10. Concerning VBA vs Macros, I hate writing code. Too many lines to do what a Macro GUI allows me to do easily. We each have what we prefer.

    Thanks.

  6. #6
    madpiet is online now Competent Performer
    Windows 10 Office 365
    Join Date
    Feb 2023
    Posts
    122
    I get it that you don't really like VBA, but doing this in VBA makes it pretty trivial, especially if you store the query SQL without a WHERE clause or ORDER BY clause. Then, since you only have a query that looks like this: (Grossly simplified)

    SELECT <field list>
    FROM <table1> JOIN <table2> ON t1.Field = t2.Field;

    you can just stuff the SQL string into a string variable, remove the trailing semi-colon, and tack on the filter you create in code (with a function or whatever). Then you just set the querydef's .SQL property to the new string. Then just save that as a function, and call it from your macro. There are just some things that are easier or only possible by using VBA and not macros. That's just the way Access is.

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    @madpiet, you're on a roll with necro posting!
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Using the SendKeys action in a Macro
    By john134 in forum Programming
    Replies: 5
    Last Post: 05-27-2014, 01:54 PM
  2. Access 2013 FE and BE strange behaviour
    By LGrey in forum Access
    Replies: 4
    Last Post: 01-28-2014, 03:00 PM
  3. Strange behaviour of MS Access form
    By selvakumar.arc in forum Forms
    Replies: 2
    Last Post: 08-15-2013, 12:07 AM
  4. Strange TableDef behaviour
    By tym in forum Access
    Replies: 12
    Last Post: 11-24-2011, 03:16 AM
  5. Qurey on two ODBC Tables Strange behaviour
    By tingletangle in forum Queries
    Replies: 1
    Last Post: 01-14-2010, 03:40 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