Update query giving runtime error 3417 action query cannot be used

Discussion in 'Microsoft Access VBA Modules' started by C0rrinn3, Aug 12, 2008.

  1. C0rrinn3

    C0rrinn3 Guest

    I am very new to Access and VB and I am trying update production dates in a
    table and keep getting an error saying that an action query cannot be used as
    a row source. Here is the code i used. Debug always stops on the Do.Cmd

    Option Compare Database
    Dim strSQL As String
    Dim recs As Long
    Dim NewDate As Variant
    Dim CurrDate As Variant
    Dim db As DAO.Database

    Private Sub cmbImpl_Click()
    cmbImpl.Requery
    End Sub
    Private Sub cmbImpl_Enter()
    cmbImpl.Requery
    End Sub
    Private Sub cmbImpl_GotFocus()
    Me.Requery
    End Sub
    Private Sub cmdCancel_Click()
    DoCmd.Close
    End Sub
    Private Sub Command60_Click()
    'On Error GoTo Err_Command60_Click

    Set db = CurrentDb
    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset

    txtCurrDate.Requery
    txtReqDate.Requery
    NewDate = txtReqDate
    CurrDate = txtCurrDate

    strSQL = "UPDATE qry_Rel_Date_Change SET qry_Rel_Date_Change.PROD_DATE =
    NewDate WHERE qry_Rel_Date_Change.PROD_DATE = txtCurrDate"

    DoCmd.SetWarnings (WarningsOff)
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings (warningson)



    MsgBox Prompt:="Updates applied ", Buttons:=vbOKOnly + vbExclamation



    Exit_Command60_Click:
    Exit Sub

    Err_Command60_Click:
    MsgBox Err.Description
    Resume Exit_Command60_Click

    End Sub
     
    C0rrinn3, Aug 12, 2008
    #1
    1. Advertisements

  2. C0rrinn3

    Dirk Goldgar Guest


    What is the SQL of qry_Rel_Date_Change? The error message implies that
    qry_Rel_Date_Change is itself an action query, which is not permitted and
    makes no sense. An action query does not return any records, so you run an
    update query against one.

    Incidentally, you have a *lot* of totally unnecessay code in your
    Command60_Click() procedure. Are you planning to do anything with db, cnn,
    and rst? In the procedure as written, they serve no purpose.

    And have you defined "WarningsOff:" and "warningson" somewhere? Normally,
    your calls to DoCmd.SetWarnings would just be

    DoCmd.SetWarnings False

    and

    DoCmd.SetWarnings True
     
    Dirk Goldgar, Aug 12, 2008
    #2
    1. Advertisements

  3. C0rrinn3

    Guest

    This code is a mess. You clearly have no idea what you're doing.
    Otherwise, why would you use both ADO and DAO in the same routine?

    'This is using DAO...
    Set db = CurrentDb

    'This is using ADO
    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset

    you have stored query... why are you using RunSQL?

    What's up with this?
    strSQL = "UPDATE qry_Rel_Date_Change SET qry_Rel_Date_Change.PROD_DATE
    =
    NewDate WHERE qry_Rel_Date_Change.PROD_DATE = txtCurrDate"

    Why are you trying to update a query? Queries do not *contain* data,
    tables do.
    Further,

    WHERE qry_Rel_Date_Change.PROD_DATE = txtCurrDate"

    Okay, I give up. There are so many things wrong with this it's just
    insane. the way to delimit a literal date is with #'s.
    WHERE qry_Rel_Date_Change.Prod_Date=#" & me.txtCurrDate & "#"
     
    , Aug 12, 2008
    #3
  4. C0rrinn3

    Dirk Goldgar Guest

    Agreed. But ...
    It's perfectly permissible to run an update query against an (updatable)
    SELECT query. For example, the target query may filter the base table to
    just the set of records to be updated. True, the reported error message
    suggests that this is not what the original poster is doing, but there's
    nothing wrong with updating a query in principle.
     
    Dirk Goldgar, Aug 12, 2008
    #4
  5. C0rrinn3

    C0rrinn3 Guest

    Yes, I understand this is a mess that is why I am asking and that is why I
    said it in my post. I was thrown into this and I am doing the best I can. I
    have never done this before. Thanks for the support and I will answer all
    your questions as soon I know what the hell your asking. I'll get back to you
    soon...
     
    C0rrinn3, Aug 13, 2008
    #5
  6. C0rrinn3

    C0rrinn3 Guest

    Thanks

     
    C0rrinn3, Aug 13, 2008
    #6
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.