Update Subquery

[Stacey]

I have a report built on a query that has a date prompt parameter box. I would like to update a separate date field (date completed) each time the query is run through the report option.

So, I still want the results of the main query to appear in report form, but I want the subquery to update the date completed field at the same time. Can this be done?

I tried this in the criteria field of the date completed field on the query

(UPDATE Refund Letters
SET date completed = Date()
WHERE = ALL)

I keep getting a syntax error message.

I'm stuck.

Thanks
Stacey

 

[Jon K]

I don't think you can run an Update Query from the criteria field.

A subquery is a Select Query in brackets that returns only one field. It cannot be an Update Query in brackets.

You must run the Update Query independently.


Besides, your Update Query itself contains syntax errors. Table name and field name with spaces must be put inside square brackets. After the word WHERE, there must be an expression (or expressions linked by AND or OR) that can evaluate to True or False. You cannot use =ALL in the Where Clause as it cannot evaluate to True or False.

 

[Stacey]

Then is there a way to do what I want to do....update the date completed field when the report is run but still get my records displayed for printing as a report?

Thanks
Stacey

 

[ColinEssex]

You could use a macro to run the update query first then run the select query afterwards

Col

 

[Stacey]

When I tried doing the update query on a macro, it brought back an error stating that there are no records displayed on an update.

I want to be able to run the report and have the data field updated automatically without the user having to do anything else to have that happen. Is there a way to do that?

Thanks

 

[Jon K]

Set up the Report and the Update Query. Make sure they run without errors.

Place a command button on a form. Type/paste the following four lines of code between the Private Sub... and End Sub statements in the On Click Event of the command button (replacing with the correct query name and report name):-

-------------------------------
Private Sub Command0_Click()

DoCmd.OpenReport "Name of Report", acViewPreview

DoCmd.SetWarnings False
DoCmd.OpenQuery "Name of Update Query"
DoCmd.SetWarnings True

End Sub
-----------------------------
Save the form.

Whenever the command button on the form is clicked, the report is run followed by the update query.