Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Location
    Cambridge, Ontario
    Posts
    21

    Cool Unanswered: Help with error in ADO code

    Hi all,
    I am having trouble with some code due to the fact (I think) that I am not sure how to use a variable from within the SQL statement of my ADO code. I keep getting the following error:

    "No value given for 1 or more required parameters"

    The code is as follows:
    Code:
    Sub SaleID_AfterUpdate()
    'Update the Tallie's saleID once the corresponding Inventory Record is Updated
    
    Dim sale As String
    Dim lnumber As String
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    
    sale = Me.SaleID
    lnumber = Me.log
    
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenKeyset
    rst.LockType = adLockOptimistic
    rst.Open ("Select SaleID from tbltallies WHERE log = lnumber")
    
    Do Until rst.EOF
        rst("SaleID") = sale
        rst.Update
        rst.MoveNext
    Loop
    
    rst.Close
    Set rst = Nothing
    
    End Sub
    The line that is giving me trouble is this one:
    Code:
    rst.Open ("Select SaleID from tbltallies WHERE log = lnumber")
    If someone could help me out here I would greatly appreciate it.
    Thanks for taking the time to view my thread
    ---
    Phil J.
    Cherry Forest Veneers Ltd.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by spiderweb
    Hi all,
    I am having trouble with some code due to the fact (I think) that I am not sure how to use a variable from within the SQL statement of my ADO code. I keep getting the following error:

    "No value given for 1 or more required parameters"

    The code is as follows:
    Code:
    Sub SaleID_AfterUpdate()
    'Update the Tallie's saleID once the corresponding Inventory Record is Updated
    
    Dim sale As String
    Dim lnumber As String
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    
    sale = Me.SaleID
    lnumber = Me.log
    
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenKeyset
    rst.LockType = adLockOptimistic
    rst.Open ("Select SaleID from tbltallies WHERE log = lnumber")
    
    Do Until rst.EOF
        rst("SaleID") = sale
        rst.Update
        rst.MoveNext
    Loop
    
    rst.Close
    Set rst = Nothing
    
    End Sub
    The line that is giving me trouble is this one:
    Code:
    rst.Open ("Select SaleID from tbltallies WHERE log = lnumber")
    If someone could help me out here I would greatly appreciate it.
    Thanks for taking the time to view my thread
    Try:
    rst.Open ("Select SaleID from tbltallies WHERE log = " & lnumber)
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Mar 2004
    Location
    Cambridge, Ontario
    Posts
    21
    Thanks for the quick reply

    That seems to bring me closer to the goal, however I am no seeing the following error:

    Syntax error (missing operator) in query expression 'log = numberIpicked'

    The good news is that the number does seem to be stored (and read) properly in the variable, but it still needs some work. Thanks again
    ---
    Phil J.
    Cherry Forest Veneers Ltd.

  4. #4
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    And if its not a number but text (since you declared it as string), use:

    ("Select SaleID from tbltallies WHERE log = '" & lnumber & "'")

    That is: log = " ' " & lnumber & " ' "

    A string needs to be in single quotes in SQL, a number can be without them.

    --- edit: I saw somewhere in a tutorial its nice to put msgbox "Select SaleID from tbltallies WHERE log = '" & lnumber & "'" before you actually do the command, that way you get the actual SQL-search-string in a msgBox so you can check if the syntax is correct (f.i. if the messagebox says [Select SaleID from tbltallies WHERE log = 'yesterday] you know you're missing a single quote at the end)

    gl
    Last edited by avlan; 02-03-05 at 10:41.

  5. #5
    Join Date
    Mar 2004
    Location
    Cambridge, Ontario
    Posts
    21
    Bingo! That did the job. That's also a great tip there Avlan, I will be sure to keep that in mind and use it next time this comes up.

    Thanks alot to both of you for the help, it is greatly appreciated
    ---
    Phil J.
    Cherry Forest Veneers Ltd.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    another solution is to build the sql statement in a varaible prior to trying to execute it

    eg
    Code:
    strSQL="Select SaleID from tbltallies WHERE log = lnumber"
    rst.Open (strSQL)
    So when the debug window pops up you can examine the sql statement as passed to the query, and easily tweak the query unitll such tim as it clears. There's no advantage in either approach, its a matter of personal preferences.

    if you find the VBA editor is adding superfluous quote, or double quote marks you can replace the quotes with a call to chr$(<ANSICHAR>) to generate the correct character, and improve legibility.

Posting Permissions

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