Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

A Debug Challenge...

Discussion in 'Information Technology' started by Sharkbyte, Jul 28, 2006.

  1. Sharkbyte

    Sharkbyte
    Expand Collapse
    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
     
  2. Loading...


  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    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)

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Sharkbyte" <sharkbyte1@hotmail.com-> wrote in message
    news:F8A4D6EB-0264-4F07-9318-6037EF84E0D4@microsoft.com...
    > 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
     
  4. Tony Toews

    Tony Toews
    Expand Collapse
    Guest

    Sharkbyte <sharkbyte1@hotmail.com-> wrote:

    >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
     
  5. Sharkbyte

    Sharkbyte
    Expand Collapse
    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



    "Douglas J Steele" wrote:

    > 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)
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Sharkbyte" <sharkbyte1@hotmail.com-> wrote in message
    > news:F8A4D6EB-0264-4F07-9318-6037EF84E0D4@microsoft.com...
    > > 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

    >
    >
    >
     
  6. Sharkbyte

    Sharkbyte
    Expand Collapse
    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




    "Tony Toews" wrote:

    > Sharkbyte <sharkbyte1@hotmail.com-> wrote:
    >
    > >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
    >
     

Share This Page