Run time Error 91 2

[merlynsdad]

I'm taking data from a form and putting it into a table. ID is an autonumber in the table and Description is memo.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strUpdate As String
Dim varID As Variant
Dim strSQL As String

varID = Forms!frmEditTicket!ID
strUpdate = Forms!frmEditTicket!txtUpdate

strSQL = "UPDATE tblMaster " _
    & "SET Description =  ('" & strUpdate & "') " _
    & "WHERE ID = ('" & varID & "');"
debug.print strSQL
    db.Execute strSQL

The SQL is ok, and varID and strUpdate have the correct values in the watch window. I'm still getting an object variable or block variable not set error.

If the square peg won't fit in the round hole, sand off the corners.

 

[PHV]

You missed this:
Set db = CurrentDb

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

Plus, if "ID is an autonumber", why do you push it as a string?

Consider:

strSQL = "UPDATE tblMaster " _
    & "SET Description = ('" & Replace(strUpdate, "'", "''") & "') " _
    & "WHERE ID = (" & varID & ");"

The Replace part is in case you would have any single quotes in strUpdate.

Have fun.

---- Andy

 

[merlynsdad]

Can you actually explain exactly what the Replace is doing? I know Find/Replace, and I've seen how that works in a SQL string, but I'm confused about what's getting replaced in your example above. I think I understand it's so if a word like "can't" appears in the string, the single apostrophe in the word won't throw off the code. Is that correct, or am I completely missing the point?

If the square peg won't fit in the round hole, sand off the corners.

 

[Andrzejek]

Yes, that’s exactly what the Replace is for.

Single quote will end your Update statement ‘pre-maturely’

You may want to try the Update without the Replace part and make sure you have some single quote(s) in the strUpdate and see what will happen. Then try the same with Replace in your SQL.


Have fun.

---- Andy

 

[merlynsdad]

That actually happened this morning, so your Tuesday post was perfectly timed. Now I understand it. Thanks.

If the square peg won't fit in the round hole, sand off the corners.

 

[Andrzejek]

I use the Replace anywhere where there is a possibility of a quote, especially where user can type anything.

Have fun.

---- Andy

 

[merlynsdad]

I thought I had this down, but I'm having trouble with Replace in the following phrase:

"Ticket " & intTixNum & " has been entered into Ticket Tracker by " & strSEID & " concerning " & strIssue & ". '" & Replace(strDescription, "'", "''") & "' "

strDescription is the employee's description of the problem. Somewhere I know there's an extra apostrophe lurking there, but I can't find it!

If the square peg won't fit in the round hole, sand off the corners.

 

[vbajock]

Create another string variable

Dim sSomeStr as string

sSomeStr=Replace(strDescription, "'", "''")

Put a STOP in your code after that and then look at sSomeStr in your immediate window



"Ticket " & intTixNum & " has been entered into Ticket Tracker by " & strSEID & " concerning " & strIssue & ". '" & sSomeStr& "' "

Put a STOP in your code after that and then look at it in your immediate window

 

[vbajock]

Also, one other troubleshooting technique is to use the immediate window to create a string you can execute in the query interface SQL window. Just do a

?strSQL

in the immediate window, end your program, and then cut and paste the results into a blank Query sql window, and then either try to execute it or display it in Design view, and you will get much more detailed information on what your actual error is and you will see how the query processor is interpreting what you are trying to pass to it.

 

[merlynsdad]

Good ideas, but my apostrophes are now showing up as quote marks.

If the square peg won't fit in the round hole, sand off the corners.

 

[vbajock]

Well, that is what you told it to do....

 

[vbajock]

Well, that is what you told it to do....

 

[vbajock]

Myself, I would deal with a situation like this by coding it in ADO instead of trying to do a SQL UPDATE. That would take me like, ten minutes, instead of spending days on a forum trying to figure out how to parse it out.

 

[merlynsdad]

VBAJock, I know the code "thinks" I wanted it to show me apostrophes as quotation marks. The reason I asked the question is, I'm trying to figure out what I did that makes it think that. If you have an answer, I'd appreciate the help. Thanks.

If the square peg won't fit in the round hole, sand off the corners.