locked
disable a key with VBA RRS feed

  • Question

  • Hi all,

    Is it possible to disable the apostrophe (" ' ") key when entering data into a field on a form?

    Users enter the apostrophe when entering data, like the words " 1000kg IBC's ". This prevents the SQL statement to execute and generates an error, resulting in the record not being written to the table.

    Thanks

    Deon

    Thursday, April 21, 2016 12:55 PM

Answers

  • If you're using an unbound form and then generating some dynamic SQL at the end to save your record you're eventually going to run into some single or double quote issue that you will find extremely difficult to handle with a generic SQL generation scheme.  You can try to filter these out in the KeyDown event, e.g.

        

    Private Sub Text0_KeyDown(KeyCode As Integer, Shift As Integer)

        If KeyCode = 222 Then KeyCode = 0

    End Sub

    Note this will filter single and double quotes though unless you want to check for the value of Shift as well.  Also it doesn't prevent someone from cutting and pasting values with quotes into a textbox.

    I would consider either using a bound form or using a DAO recordset to do the final insert or update of your record.  If you really need the form to be unbound using DAO to update/insert is the way to go.  A recordset won't care about quotes.

    -Bruce

    Thursday, April 21, 2016 6:26 PM

All replies

  • Hi Deon. Yes, it's possible to prevent the apostrophe but what happens if users enter a double quote? It's best to fix the SQL statement instead. For example, you can use the Replace() function to double up the apostrophes. Just my 2 cents...
    Thursday, April 21, 2016 1:20 PM
  • Hi,

    If the double quote is used, the SQL statement executes and the record is saved. It does not seem to have any influence if the double quote is used. It is only the apostrophe.

    Thanks

    Thursday, April 21, 2016 1:27 PM
  • Hi,

    If the double quote is used, the SQL statement executes and the record is saved. It does not seem to have any influence if the double quote is used. It is only the apostrophe.

    Thanks

    Hi. Like I said, you just need to use the Replace() function to double up the single quotes. For example:

    Replace([UserInputHere],"'","''")

    Hope that helps...

    Thursday, April 21, 2016 4:08 PM
  • If you're using an unbound form and then generating some dynamic SQL at the end to save your record you're eventually going to run into some single or double quote issue that you will find extremely difficult to handle with a generic SQL generation scheme.  You can try to filter these out in the KeyDown event, e.g.

        

    Private Sub Text0_KeyDown(KeyCode As Integer, Shift As Integer)

        If KeyCode = 222 Then KeyCode = 0

    End Sub

    Note this will filter single and double quotes though unless you want to check for the value of Shift as well.  Also it doesn't prevent someone from cutting and pasting values with quotes into a textbox.

    I would consider either using a bound form or using a DAO recordset to do the final insert or update of your record.  If you really need the form to be unbound using DAO to update/insert is the way to go.  A recordset won't care about quotes.

    -Bruce

    Thursday, April 21, 2016 6:26 PM
  • Hi Deon SA,

    I think Bruce Hulsey 's suggestion can solve your issue.

    please check it that it can solve your issue or not.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, April 22, 2016 4:22 AM
  • I disagree Deepak. The innocent user would not understand why single-quote is being "eaten". I feel it is MUCH better to allow any data entry, and then escape the single-quote with two of them, like "the DB Guy" suggested. This is not just my opinion, but standard operating procedure when using special characters.

    -Tom. Microsoft Access MVP

    Friday, April 22, 2016 4:42 AM
  • Hi Tom van Stiphout ,

     I am not forcefully saying to apply Bruce Hulsey'ssuggestion. I just suggest him to check whether it can solve his issue or not. Here op want to disable the apostrophe (" ' ") key. op did not asked anything about standard operating procedure to handle the special characters. its totally depends upon op that which suggestion he want to use to solve his issue.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, April 22, 2016 4:52 AM
  • Hi Deepak,

    We're on the same team trying to be of help. The OP wrote:
    Users enter the apostrophe when entering data, like the words " 1000kg IBC's ". This prevents the SQL statement to execute and generates an error, resulting in the record not being written to the table.

    To me that sounds not like a cry for suppressing single-quote, but for a cry to how to deal with it. The famous problem of handling the Irish last name of O'Brien comes to mind. Would we really want to suppress the single-quote and forever send Patty invoices addressed to OBrien? I think not. We can disagree about that, and maybe the OP can chime in, but until such time I believe escaping the single-quote is the better course of action.

    select * from Customers where LastName = Replace("O'Brien", "'", "''")


    -Tom. Microsoft Access MVP

    Friday, April 22, 2016 5:00 AM
  • Hi Guys,

    In this specific environment, Bruce's suggestion works the best.

    Thanks for your assistance

    Regards

    Deon

    Friday, April 22, 2016 5:41 AM
  • Just to be clear I do not advocate the solution of suppressing single quote entry in this case.  I thought it educational to explain how that would be done but you are correct in that it would be an unusual situation in which it might be appropriate to do so.  Perhaps in a high volume data entry situation in which business rules are clearly established and the use of an input mask or beforeupdate trap with resulting error dialogs would result in more wasted keystrokes or mouse clicks but even then, it's difficult to say whether it would be justified.  In this particular case, I don't believe doing so simply to allow an unbound form to use dynamically generated SQL to save the final record is the best solution.  Again, if one must use an unbound form the better solution IMO would be to use a recordset to set the value of individual fields and dispense with worrying about the quotes altogether.

    -Bruce

    Friday, April 22, 2016 2:36 PM