Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Store sql string (A2k3 SP2)

    Hi All,
    I've got a public string variable loaded with a sql string. I'm trying to save the string itself in a table as memo field type. The problem is the sql string wants to execute and I end up with the results of the sql string or #Name? displayed in the textbox. What do I do to stop executing the sql string and just store it?
    Thank you.
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Store sql string (A2k3 SP2)

    Hi Gary

    Off the top of my head here and without testing the idea. How about prefixing it with -- to comment it out. Using string manipulation you could remoce the -- later....an idea!!!!
    Jerry

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Store sql string (A2k3 SP2)

    How are you storing the string in the text box? Something like this ought to work:

    Dim strSQL As String
    strSQL = "SELECT Count(*) FROM tblEmployees"
    Me.TextBox1 = strSQL

    You should *not* set the Control Source of the text box to the SQL string.

  4. #4
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Store sql string (A2k3 SP2)

    Hi Jerry,
    I can't tell if that will work or not as the I think the basis of my problem comes from the referred to posts in my reply to Hans.
    Thanks you.
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  5. #5
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Store sql string (A2k3 SP2)

    Hi Hans,
    I'm trying to store the SQL string like a piece of data. I've found out this, Me!txtTextBox = strSQL, does work, but only if I place it in the OnLoad. But, events referred to in <post:=665,782>post 665,782</post:> (setting the value of a textbox in the OnLoad makes the form Dirty and I don't want it saved at that time) return. So I've tried the OnOpen, setting the DefaultValue as I did at the end of that thread (<post:=666,174>post 666,174</post:> ). No matter what I've tried there hasn't worked;
    Me!txtTextBox.DefaultValue = strSQL
    Me!txtTextBox.DefaultValue = Chr(34) & strSQL & Chr(34)
    Me!txtTextBox.DefaultValue = Chr(34) & Chr(34) & strSQL & Chr(34) & Chr(34)

    Me!txtTextBox = strSQL
    Me!txtTextBox = Chr(34) & strSQL & Chr(34)
    Me!txtTextBox = Chr(34) & Chr(34) & strSQL & Chr(34) & Chr(34)

    Can I "force" Access to see the SQL string as just a string of data?

    Thank you.
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Store sql string (A2k3 SP2)

    If your SQL string contains double quotes, they will cause a problem. For example, if strSQL contains the string

    UPDATE tblEmployees SET strLastName = "Rezek" WHERE pkeyEmployeeID = 37

    the default value of the text box would become

    "UPDATE tblEmployees SET strLastName = "Rezek" WHERE pkeyEmployeeID = 37"

    but this confuses Access. You must replace double quotes within the string with two double quotes or with single quotes. The following works for me:

    Me!txtTextBox.DefaultValue = Chr(34) & Replace(strSQL, Chr(34), Chr(34) & Chr(34)) & Chr(34)

    as does

    Me!txtTextBox.DefaultValue = Chr(34) & Replace(strSQL, Chr(34), Chr(39)) & Chr(34)

  7. #7
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Store sql string (A2k3 SP2)

    Yup, that's where it was at.
    I had tried using Replace earlier, but I was using " and "", not Chr(34).
    Thank you
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Store sql string (A2k3 SP2)

    Quotes are confusing, aren't they? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    If you want to specify a string consisting of one double quote in code, you have to use <code>""""</code> or Chr(34).
    If you want to specify a string consisting of two doubles quote in code, you have to use <code>""""""</code> or Chr(34) & Chr(34).
    You'll understand why I prefer to use the Chr function.

  9. #9
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Store sql string (A2k3 SP2)

    I think I've finally become a believer - <img src=/S/angel.gif border=0 alt=angel width=15 height=21>
    Up until now, for my needs anyway, it had seemed pretty equivalent using either. Well, I can change. <img src=/S/bow.gif border=0 alt=bow width=15 height=15>
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •