Update query giving runtime error 3417 action query cannot be used

  • Thread starter Thread starter C0rrinn3
  • Start date Start date
C

C0rrinn3

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 said:
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
Click to expand...


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
 
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 & "#"
 
This code is a mess.
Click to expand...

Agreed. But ...
Why are you trying to update a query? Queries do not *contain* data,
tables do.
Click to expand...

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.
 
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...
 
Thanks

Dirk Goldgar said:
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, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top