How do I make an UPDATE query with subquery?

[Seth Schrock]

After searching online for awhile, I have discovered many people with this same question, but no answers. I'm trying to create an update query that sets the value of the field to the result of a subquery. However, when I try to run the query I get an error message that says "Operation must use an updateable query." My research tells me that it is because of the subquery. Here is what I have so far:

UPDATE tblACHFiles INNER JOIN tblInvoices ON tblACHFiles.InvoiceID = tblInvoices.InvoiceID 
SET tblACHFiles.InvoiceID = (SELECT InvoiceID FROM tblInvoices WHERE CustomerID = Forms!frmCustomer!CustomerID
AND BeginDate = Forms!frmCustomer!txtStartDate AND EndDate = Forms!frmCustomer!txtEndDate)
WHERE (((tblACHFiles.[ACHCompanyID])=[Forms]![frmCustomer]![CustomerID]) 
AND ((tblACHFiles.[EffectiveDate])>=[Forms]![frmCustomer]![txtStartDate] 
And (tblACHFiles.[EffectiveDate])<=[Forms]![frmCustomer]![txtEndDate]));

I suppose that I could replace the subquery with a DLookup() function, but I don't know the syntax to have three criteria. As those are the only two options that I know of, I don't know how to procede.

[NeoPa]

Check out Reasons for a Query to be Non-Updatable. This is Jet-specific, so you needn't worry that all SQL engines necessarily behave that way.

If you are unable to make your underlying query updatable then the only recourse is to create the data in a table then run the UPDATE query using that table. Probably best to delete or clear the temporary table after use I would suggest.

[Seth Schrock]

After reading the information in the link, I'm not sure where my query falls. However I did just come up with an idea. Would it be possible to use this as the set value:

SET tblACHFiles.InvoiceID = DLookup("InvoiceID", "qryFindInvoiceID")

qryFindInvoiceI D would be what I did have in the subquery and would return only one record. Does this fall into the temporary table category or would this still be non-updatable? I'm not at work so I can't try it until Monday.

I've never worked with temporary tables so I'm not sure what this entails.

[NeoPa]

I would expect that to work Seth. I'm sure such techniques have been used before successfully. Still best to test it of course, but I'd expect it to work.

[Seth Schrock]

It worked. Thanks for your help.

[NeoPa]

Pleased to hear it Seth :-)