Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2004
    Posts
    4

    Unanswered: Insert Into Help!

    I am trying to insert data into a table that has two columns. I have the following code.

    for count = 1 to 800
    intNumbOfPasses = count
    txtCondition = Dlookup(......)
    docmd.RunSQL "INSERT INTO tblViewCond (Passes,Condition) VALUES (intNumbOfPasses, txtCondition)
    Next

    For some reason whenever I run this code, my form always prompts me to enter a Paramater value for txtCondition. The value for txtCondition should come from the Dlookup command.

    I have used this EXACT method to insert values into other tables and they worked great. I can't figure out why this one won't work? Please help! Thanks in advance.

    Jason

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Um ...

    Try:

    ... VALUES (" & intNumbOfPasses & ",'" & txtCondition & "');"
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Sep 2004
    Posts
    4

    Talking Thanks!

    Thanks! That worked. Quick question though... I am pretty new to this stuff. What is the purpose of the "&" before and after the Values? Whats the difference between the way I did it, and the way you did it?

    Thanks again for your help!

  4. #4
    Join Date
    Nov 2003
    Location
    Sussex, England
    Posts
    404
    Think of the SQL instruction

    INSERT INTO tblViewCond Passes,Condition) VALUES (3, "Not Null")

    and

    i=3
    notNull="Not Null"
    INSERT INTO tblViewCond Passes,Condition) VALUES (i, notNull)

    Notice anything missing from the second SQL instruction.

    In fact if anything, it's a surprise that Mike's solution worked as there are not quote marks in it. When a DoCmd.RunSQL command encounters a variable it passes the value of that variable to an SQL instruction. That's fine if it's a number and the field is expecting a number. Strings and dates are a bit more complicated.

    I would have tried

    VALUES (" & intNumbOfPasses & ",''" & txtCondition & "'');"

    Note the single ' marks after the comma and before the ).

    However I don't usually use the doCmd.RunSQL instruction so maybe I'm wrong, and the grand poobah is usually right anyway so ....


Posting Permissions

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