none
How to extract selected text from a textbox RRS feed

  • Question

  • I have a textbox that will contain SQL which will be run when a button is clicked. I'd like the user to be able to select part of the text in the textbox, click the button and then have only the selected text run. The below image illustrates what I want. In this case the user wants to run the SQL omitting the WHERE clause. Has anyone done something like this or anyone know how to do it? Thanks. 

    Thursday, August 20, 2015 3:11 PM

Answers

  • Although RunningManHD is probably right about the best way to go about this, if you really need to extract the selected text from a text box, be aware that the control's .SelText property contains that text -- while the text box has the focus.  There are other handy properties related to the selection, such as .SelStart and .SelLength .  You can only access these properties when the control has the focus, which means that, if you want the user to click a button to do your processing, then either that button must be in the ribbon, commandbar, or right-click menu, or else you need to use the control's LostFocus or Exit event to capture the .SelText before the focus is lost.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, August 20, 2015 4:23 PM

All replies

  • Though your request might seem simple enough, it is destined for problems.  Have you considered what to do if the user selects less or more than what is required to complete a valid SQL statement?

    Capturing the selected text is easy enough, but you will have to validate what is captured before you actually try to execute the SQL.

    Would it not be more beneficial to have a set of text boxes that the user can enter criteria and build a WHERE clause dynamically?  That being the case, if no criteria is entered, the WHERE clause would not be added.


    If you decide to continue on this path, you can use the SelText property of the control.
    Thursday, August 20, 2015 3:17 PM
  • Hi ATGNWT,

    RunningManHD has already given the right direction to go.

    In all my forms I have a standard utility that splits any RecordSource in three parts: the Select part, the Where part, and the Order part. Users have the possibility to modify the Where part based on an input form with all the available fields, and to choose on which field can be ordered by placing that field in fromt of the Order part. Then a new sql-string is constructed, and assigned to the RecordSource of the form.

    Imb.

    Thursday, August 20, 2015 3:55 PM
  • Although RunningManHD is probably right about the best way to go about this, if you really need to extract the selected text from a text box, be aware that the control's .SelText property contains that text -- while the text box has the focus.  There are other handy properties related to the selection, such as .SelStart and .SelLength .  You can only access these properties when the control has the focus, which means that, if you want the user to click a button to do your processing, then either that button must be in the ribbon, commandbar, or right-click menu, or else you need to use the control's LostFocus or Exit event to capture the .SelText before the focus is lost.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, August 20, 2015 4:23 PM
  • Although RunningManHD is probably right about the best way to go about this, if you really need to extract the selected text from a text box, be aware that the control's .SelText property contains that text -- while the text box has the focus.  There are other handy properties related to the selection, such as .SelStart and .SelLength .  You can only access these properties when the control has the focus, which means that, if you want the user to click a button to do your processing, then either that button must be in the ribbon, commandbar, or right-click menu, or else you need to use the control's LostFocus or Exit event to capture the .SelText before the focus is lost.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html


    Adding to Dirk's comments... You can also use a KeyCode combination in the control's KeyDown event, which allows you to keep focus on the control while executing your action.
    Thursday, August 20, 2015 7:23 PM
  • Adding to Dirk's comments... You can also use a KeyCode combination in the control's KeyDown event, which allows you to keep focus on the control while executing your action.

    Hi all,

    The above approach is good for general purpose text, but in my opinion not for the construction of sql-strings, that ask for a very specific syntax. The chance for making typing errors in fieldnames, in the overall syntax with all the comma's, not yet to speak from all the quotes around string-values, demands for almost an sql-expert to do the editing, and most users are not.

    Instead it is safer to let the user choose from the fields, choose from the relevant conditions for that field type, type in the values or choose from predefined values, choose the fields for ordening, etc., to construct the sql-string after a Submit.

    Imb.

    Thursday, August 20, 2015 8:36 PM
  • The above approach is good for general purpose text, but in my opinion not for the construction of sql-strings, that ask for a very specific syntax. The chance for making typing errors in fieldnames, in the overall syntax with all the comma's, not yet to speak from all the quotes around string-values, demands for almost an sql-expert to do the editing, and most users are not.

    Instead it is safer to let the user choose from the fields, choose from the relevant conditions for that field type, type in the values or choose from predefined values, choose the fields for ordening, etc., to construct the sql-string after a Submit.


    This is all true, but it's not uncommon in general-purpose database GUIs (like MySQL Workbench, for example), to allow users to write and execute SQL statements, and even -- as in MySQL Workbench -- to select part of the text in a SQL-entry window and execute just that text.  So for a "power tool" sort of situation, it may be appropriate.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, August 20, 2015 8:42 PM
  • This is all true, but it's not uncommon in general-purpose database GUIs (like MySQL Workbench, for example), to allow users to write and execute SQL statements, and even -- as in MySQL Workbench -- to select part of the text in a SQL-entry window and execute just that text.  So for a "power tool" sort of situation, it may be appropriate.

    Hi Dirk,

    The users of my application must be spoiled...

    Imb.

    Thursday, August 20, 2015 9:02 PM
  • Thanks for all the responses.

    This is not meant to be a tool to build valid SQL.

    I don't care if the user enters junk SQL or selects an inappropriate string of text. The entire text or subset of selected text will be sent to a processor which will validate it and generate an error message if it is invalid.

    I want the user to be able to enter one or more separate SQL statements in the box and execute either one by selecting it. It will operate like Query Analyzer in SQL Server.


    Thursday, August 20, 2015 9:58 PM