Movian
06-03-2009, 07:57 AM
Hey,
i have a sub that opens a recordset based on a sql query as a means of checking if a particular record exists in a table.
SQlString = "SELECT Form1, Control, Value FROM tblLookupValues WHERE Form1 =""" & OldLook.Fields("Form1") & """ AND Control = """ & OldLook.Fields("Control") & """ AND Value =""" & OldLook.Fields("Value") & """"
'MsgBox SQlString
Set NewLook = newdb.OpenRecordset(SQlString)
The problem being that i am stuck between a rock and a hard place as the Value of the field value may on ocasions have both " or ' characters in it. (Although the " characters are less likely).
I am not very familiar with checking for errors then acting on them but essentially i need a way to trap the error that will ocur when the value field looks like ""Value", "Value"" or ""Value"". Then provide the text string via an input box.
Inputbox "Correct SQL error",default = Sqlstring.
Then once the correction has been made run through this process again. THis way if the corrected string STILL has an error it will re check it and ask for an additional correction. However if the string works it would simply continue on... Essentially i have a vauge idea of how to do it
~Pseudo code~
Do untill err = no error
if err = error then
inputbox
else
open recordset
end if
loop
But i can't figure out the specifics for VBA
As always any help is apriciated
i have a sub that opens a recordset based on a sql query as a means of checking if a particular record exists in a table.
SQlString = "SELECT Form1, Control, Value FROM tblLookupValues WHERE Form1 =""" & OldLook.Fields("Form1") & """ AND Control = """ & OldLook.Fields("Control") & """ AND Value =""" & OldLook.Fields("Value") & """"
'MsgBox SQlString
Set NewLook = newdb.OpenRecordset(SQlString)
The problem being that i am stuck between a rock and a hard place as the Value of the field value may on ocasions have both " or ' characters in it. (Although the " characters are less likely).
I am not very familiar with checking for errors then acting on them but essentially i need a way to trap the error that will ocur when the value field looks like ""Value", "Value"" or ""Value"". Then provide the text string via an input box.
Inputbox "Correct SQL error",default = Sqlstring.
Then once the correction has been made run through this process again. THis way if the corrected string STILL has an error it will re check it and ask for an additional correction. However if the string works it would simply continue on... Essentially i have a vauge idea of how to do it
~Pseudo code~
Do untill err = no error
if err = error then
inputbox
else
open recordset
end if
loop
But i can't figure out the specifics for VBA
As always any help is apriciated