bound forms

[Guest]

Is there any straightforward way of getting between a bound textbox and the
table entry. What I would like to do is use a pair of methods to replace
single quotes with another character, eg. Chr(255) so that the user can type
"Joe's Hotdogs" and what is stored is "Joe" & Chr(255) & "s Hotdogs" I have
been using this technique with unbound controls and it would simplify my life
on my current project if there is some way to do this transparently with
bound textboxes.

 

[Douglas J. Steele]

Why (or, more specifically, why not store it properly)?

If your concern is how to handle the single quotes in SQL statements, you
simply double them up, so that you'd use:

strSQL = "SELECT Field1, Field2 FROM MyTable " & _
"WHERE Field1 = 'Joe''s Hotdogs'"

Assuming you're using a variable or form field to provide the criteria,the
easiest way (assuming you're using Access 2000 or newer) is to use the
Replace function:

strSQL = "SELECT Field1, Field2 FROM MyTable " & _
"WHERE Field1 = '" & Replace(MyValue, "'", "''") & "'"

Exagerated for clarity, that's

strSQL = "SELECT Field1, Field2 FROM MyTable " & _
"WHERE Field1 = ' " & Replace(MyValue, " ' ", " ' ' ") & " ' "

If you're absolutely determined to replace the single quote with something
else, you'd use

Replace(MyValue, "'", Chr$(255))

 

[Guest]

Thanks for the reply. The emphasis of my question is more towards editing a
string between a control and the table it is bound to, the single quote
example is not the only situation in which I would like to modify the text.
Using Replace() requires coding on a case by case basis and what I would like
is to be able to subclass the textbox control in such a manner that the
replacement happens with all text boxes without case by case coding. As an
aside, most of my work involves SQL Server and my experience is that your
select examples won't work in Transact SQL which is why I replace the single
quote with some other character.

 

[Douglas J. Steele]

If you're using a pass-through query to SQL Server, so that it's running on
the server and using T-SQL, then my sample select statements should work, as
I'm using ' as a text delimiter and doubling up any occurences of the
delimiter character, like SQL Server requires.

As to editing between a control and the table to which it's bound, you put
the code in the BeforeUpdate event, and whatever you typed into the control
will be changed before the update occurs.

I'm not sure I understand your "Using Replace() requires coding on a case by
case basis" comment. If you mean that you'd need to put the logic behind
each individual text box, you're right, but you can create a generic
function that you call in the BeforeUpdate event.

 

[Albert D.Kallal]

You use the controls after update event....

That event does NOT fire if the control is not changed. So, in the controls
after update event, simply run your code that does whatever...

Call myFixText(me.MyContorlname)

And, while a number of good people has suggested things like doubling up the
quotes etc, my solution is always strip out " (double quote) and convert it
to a single quote. That way, the sql always works....and nothing more need
be done to the data. This does mean that users when entering a ", it gets
converted to a '....but that seems to about the best solution with the least
ramifications...