Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2002
    Location
    CA
    Posts
    8

    Unanswered: Help on db.Execute

    Tried using DoCmd for the same task
    DoCmd.RunSQL "update REASSIGNDAILY set STATUS = -1 where (((BATCHNO)= [rs4!batchno]));"

    This time - no run time error, however prompting to enter a value for
    rs4!batchno. Usage of '&' is giving an error. What modification is required on the SQL statement so that the value for rs4!batchno is passed automatically into the statement.

    -------
    Hello,
    Encountering Run time error 3061 (too few parameters, expected 1) on
    db.Execute ("UPDATE REASSIGNDAILY set STATUS = TRUE WHERE REASSIGNDAILY!BATCHNO = " & rs4!BATCHNO)

    Am generating TABLE REASSIGNDAILY using a query. STATUS field is a new field added via the query with values TRUE OR FALSE. The field type however gets defined as 'Number'.

    CODE Extract below:
    Private Sub Reassign_Click()
    Dim rs4 As Recordset
    Set db = CurrentDb
    Set Exobj = StartExtra("c:\Extra.edp")

    DoCmd.OpenQuery ("DLYREASSIGN")
    Set rs4 = db.OpenRecordset("SELECT * from REASSIGNDAILY")

    With Exobj.Screen
    .SendKeys ("<CLEAR>")
    Exec_log Exobj
    .SendKeys ("/FOR RF3412 <ENTER>")
    Exec_log Exobj

    If .GetString(1, 72, 6) = "RFP412" Then
    Do While rs4.EOF = False
    .PutString rs4!BATCHNO, 11, 2
    .PutString "R", 4, 23
    .SendKeys ("<ENTER>")
    Exec_log Exobj

    If (.GetString(11, 64, 80 - 64)) = "BATCH REASSIGNED" Then
    db.Execute ("UPDATE REASSIGNDAILY set STATUS = TRUE WHERE REASSIGNDAILY!BATCHNO = " & rs4!BATCHNO)

    Else

    db.Execute ("UPDATE REASSIGNDAILY set STATUS = FALSE WHERE REASSIGNDAILY!BATCHNO = " & rs4!BATCHNO)


    End If

    rs4.MoveNext

    Loop

    End If

    rs4.Close
    db.Close

    End With

    ***************

    p.s. I have used this db.Execute statement is other applications developed w/o any issue. What am I missing here.

    New at VB, Appreciate help !

    Bin
    Last edited by Bindu; 11-26-02 at 14:02.

  2. #2
    Join Date
    Nov 2002
    Posts
    150

    Re: Help on db.Execute

    DoCmd.RunSQL "update REASSIGNDAILY set STATUS = -1 where BATCHNO = " & rs4!batchno

  3. #3
    Join Date
    Nov 2002
    Location
    CA
    Posts
    8
    Thanks..

    Continues to prompt me to enter a value for BN221 (which is the value for rs4!batchno.)

    Any ideas.

  4. #4
    Join Date
    Nov 2002
    Posts
    150
    Originally posted by Bindu
    Thanks..

    Continues to prompt me to enter a value for BN221 (which is the value for rs4!batchno.)

    Any ideas.
    Put a break on the line of code to make sure rs4!batchno exists. The syntax I send you should work.

  5. #5
    Join Date
    Nov 2002
    Location
    CA
    Posts
    8
    Yes it exists. know the syntax is ok but there is something else ...
    As suggested, debugged code ----------

    MsgBox (rs4!batchno)
    If (.GetString(11, 64, 80 - 64)) = "BATCH REASSIGNED" Then

    DoCmd.RunSQL "update REASSIGNDAILY set STATUS = -1 where BATCHNO = " & rs4!batchno

    Else
    'db.Execute ("update REASSIGNDAILY set STATUS = 0 where BATCHNO = " & rs4!batchno)
    End If
    ------------------
    MsgBox (rs4!batchno) returned value BN221. Upon executing Do Cmd is prompting me to enter a value for BN221.

    I have been trying quite a few combinations... will do a full recompile..

  6. #6
    Join Date
    Nov 2002
    Location
    CA
    Posts
    8
    Recompiled, compacted... and tested No luck.. same result as before..

  7. #7
    Join Date
    Nov 2002
    Posts
    150
    Doh!!! BATCHNO is a text field?

    Try: DoCmd.RunSQL "update REASSIGNDAILY set STATUS = -1 where BATCHNO = '" & rs4!batchno & "'"

  8. #8
    Join Date
    Nov 2002
    Location
    CA
    Posts
    8
    Ja Ja..worked like a charm.

    Thanks a bunch

Posting Permissions

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