A Debug Challenge...

[Guest]

Okay. This one has got me. Maybe someone, here, can offer another
suggestion...

Here's the code:

Dim Visit1 as String
Dim Visit2 as String

Visit1 = me.txtVisitDescription
Visit2 = Replace(Visit1, "'", "''")

DoCmd.RunSQL ("update tblsitevisits set visitdesc = '" & Visit2 & "' where
servicecall = txtservicecall and sitevisitnumber = txtlastsitevisit;")


The result is that the Update does not take.

I have stepped through, with warnings...run debug...and substituted values.
No warnings; empty debug window; and any configuration of substituted values
works.

I have even found that, after the first time stepping through, with
substituted values in a series of 6 SQL statements, each SQL statement works,
and the offending SQL will begin to work. Run without stepping through the
statements, or remove the substituted value statements, and the original SQL
again doesn't populate the field.

Anyone want to take a shot at this?

Thanks.

Sharkbyte

 

[Douglas J Steele]

The problem is that the variables txtservicecall and txtlastsitevisit are
inside the quotes, so Access sees the text, not the value contained in the
variables.

DoCmd.RunSQL "update tblsitevisits set visitdesc = '" & Visit2 & _
"' where servicecall = " & txtservicecall & " and " & _
"sitevisitnumber = " & txtlastsitevisit

That assumes that servicecall and sitevistnumber are both numeric fields. If
they're text, you'll need quotes:

DoCmd.RunSQL "update tblsitevisits set visitdesc = '" & Visit2 & _
"' where servicecall = " & Chr$(34) & txtservicecall & Chr$(34) & _
" and sitevisitnumber = " & Chr$(34) & txtlastsitevisit & Chr$(34)

 

[Tony Toews]

DoCmd.RunSQL ("update tblsitevisits set visitdesc = '" & Visit2 & "' where
servicecall = txtservicecall and sitevisitnumber = txtlastsitevisit;")
The result is that the Update does not take.

I have stepped through, with warnings...run debug...and substituted values.
No warnings; empty debug window; and any configuration of substituted values
works.

I prefer, if DAO, to use Currentdb.Execute strSQL,dbfailonerror
command instead of docmd.runsql. For ADO use
CurrentProject.Connection.Execute strCommand, lngRecordsAffected,
adCmdText

If you're going to use docmd.setwarnings make very sure you put the
True statement in any error handling code as well. Otherwise weird
things may happen later on especially while you are working on the
app. For example you will no longer get the "Do you wish to save your
changes" message if you close an object. This may mean that unwanted
changes, deletions or additions will be saved to your MDB.

Also performance can be significantly different between the two
methods. One posting stated currentdb.execute took two seconds while
docmd.runsql took eight seconds. As always YMMV.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Guest]

Doug:

Actually, txtServiceCall and txtLastSiteVisit reference TextBox controls, on
the form. In this case, txtServiceCall is blank or "variant" and
txtLastSiteVisit is GeneralNumber.

Thank you for your suggestions.

Sharkbyte

 

[Guest]

Okay. Stupid question time...

I simply open the code window, and write. I have never paid much attention
to DAO or ADO. (Not that I don't intend on expanding my Access knowledge, at
a later time.)

How can I tell which I am working with? Is it ADO, by default? Access2003,
is what I use.

Thanks.

Sharkbyte