dan_l
Active Member
Here's the project:
I've got a form.. The form is bad. The process is bad. There's a ton of redundancy. I've replaced the form with spreadsheet that let's a user fill out the particulars and send it out to the appropriate people.
Once the people processing the form get it, there's quite a bit of simple streamlining. There's also a button at the end which creates and fills out an email for them to send. This part works. Additionally, that same button runs an insert query on a database that's stored on a shared drive. This part works mostly.
The code for the query is like:
INSERT INTO table (field1,field2,field3)
"VALUES('" & f1 & "' &"f2"'&'"f3"');"
That's basically what's going on. I can post the actual code if need be, but I'm not sure that it will be needed for this question.
So the query works. It adds records with test data just fine. But still, it throws an error if somebody puts an apostrophe or quote somewhere in the form. I'm guessing this is because the string gets interpreted incorrectly when one of those character is present as the required code is really dependant upon the quotes being perfect.
So I have to make this a non issue. I'm curious as to what the best practices may be. Off the top of my head,
-I wonder if it's possible to qualify the text somehow so that the presence of these characters doesn't throw off the query.
-Maybe it should just be part of the preprocessing to check the string for these characters and delete them?
Updating my thought: This isn't so much a VBA problem. VBA is doing exactly what I want it to. The trouble is how the sql is being interpreted. Hmmmrmph.
I've got a form.. The form is bad. The process is bad. There's a ton of redundancy. I've replaced the form with spreadsheet that let's a user fill out the particulars and send it out to the appropriate people.
Once the people processing the form get it, there's quite a bit of simple streamlining. There's also a button at the end which creates and fills out an email for them to send. This part works. Additionally, that same button runs an insert query on a database that's stored on a shared drive. This part works mostly.
The code for the query is like:
INSERT INTO table (field1,field2,field3)
"VALUES('" & f1 & "' &"f2"'&'"f3"');"
That's basically what's going on. I can post the actual code if need be, but I'm not sure that it will be needed for this question.
So the query works. It adds records with test data just fine. But still, it throws an error if somebody puts an apostrophe or quote somewhere in the form. I'm guessing this is because the string gets interpreted incorrectly when one of those character is present as the required code is really dependant upon the quotes being perfect.
So I have to make this a non issue. I'm curious as to what the best practices may be. Off the top of my head,
-I wonder if it's possible to qualify the text somehow so that the presence of these characters doesn't throw off the query.
-Maybe it should just be part of the preprocessing to check the string for these characters and delete them?
Updating my thought: This isn't so much a VBA problem. VBA is doing exactly what I want it to. The trouble is how the sql is being interpreted. Hmmmrmph.