Updating data retrieved by a query

  • Thread starter Thread starter Sam
  • Start date Start date
S

Sam

I'm looking for any suggestions or advice on how to handle
this procedure.

I'm identifying records based on a date criteria (using
the MAX function in a query), and once the records are
retrieved, I want to give users the option to update a
particular field on all the retrieved records.

The query I have written gets the records fine, and I've
created a new form to display the results. However, once
the records are retrieved it tells me the recordset is not
updateable - because it's based on a non-updateable
query.

Is there a way to select these records and put them into
an update query so I can change them all at once? I
understand that I can't use Totals (Max) in an update
query as criteria, otherwise I would just do that.

Here's the SQL from my query which pulls the records:

SELECT Max(Orders.OrderDate) AS MaxOfOrderDate,
Customers.AutoKey, Customers.CustomerID,
Customers.ContactType, Customers.CompanyName,
Customers.Flag
FROM Customers INNER JOIN Orders ON Customers.AutoKey =
Orders.AutoKey
GROUP BY Customers.AutoKey, Customers.CustomerID,
Customers.ContactType, Customers.CompanyName,
Customers.Flag
HAVING (((Max(Orders.OrderDate))<=[Forms]![Select
Customers by Last Order Date]![DateCriteria]) AND
((Customers.ContactType)="1"))
ORDER BY Max(Orders.OrderDate) DESC;

I want to update [ContactType] from 1 to 5. [AutoKey] is
the primary key in the Customers table. I have even tried
flagging the relevant records, but they only update one at
a time on my form, if I can get them to update at all...

Any help or suggestions appreciated,
Sam
 
You can try something like:
SELECT DMax("OrderDate","Orders","[Autokey]=" & [AutoKey]) AS
MaxOfOrderDate,
Customers.AutoKey, Customers.CustomerID,
Customers.ContactType, Customers.CompanyName,
Customers.Flag
FROM Customers
Where DMax("OrderDate","Orders","[Autokey]=" & [AutoKey])<=[Forms]![Select
Customers by Last Order Date]![DateCriteria]) AND
((Customers.ContactType)="1"))
ORDER BY DMax("OrderDate","Orders","[Autokey]=" & [AutoKey]) DESC;
 
Back
Top