Access 2000 - 'Operation must use an updatable query'

[Bill Murphy]

When I run the following update query I get: 'Operation must use an
updatable query. (Error 3073)':

UPDATE DISTINCTROW tblProducerAssignments LEFT JOIN qryVolumeSummary ON
tblProducerAssignments.LenderID = qryVolumeSummary.LenderID SET
tblProducerAssignments.CurrentVolume = [Volume];

Here's the code for the sub-query qryVolumeSummary:

SELECT Loan.LenderID, Sum(nz([LoanAmount])) AS Volume
FROM Loan
WHERE Loan.AccountingDate Between [forms]![frmMain].[form]![txtBeginDate]
And [forms]![frmMain].[form]![txtEndDate]
GROUP BY Loan.LenderID;

I don't have permission problems on the database, and the sub-query is a
summary query and does not have multiple records per LenderID, so there is
not a one-to-many relationship. Any ideas on how to remedy this?

Bill

 

[John Spencer (MVP)]

Unfortunate as it may be, anytime you use an aggregate function (SUM being one)
in a query you can NOT update any value in the query.

You can however use DSUM

So something like the following may work. Watch out for the line wrap.

UPDATE DISTINCTROW tblProducerAssignments
SET
tblProducerAssignments.CurrentVolume =
DSUM("LoanAmount","Loan",
"LenderID = '" & LenderID &
"' AND AccountingDate Between #" & [forms]![frmMain].[form]![txtBeginDate] &
"# AND # " & [forms]![frmMain].[form]![txtEndDate] & "#")


--- You might be able to add the following criteria line, but it may fail
WHERE LenderID IN
(Select LenderID
FROM Loan
WHERE Loan.AccountingDate Between [forms]![frmMain].[form]![txtBeginDate]
And [forms]![frmMain].[form]![txtEndDate])


If LenderID is a number field then remove the apostrophes next to the quote marks.

I just noted that you are referring to controls on a subform. This may or may
not work. Does you current subquery work?

 

[Bill Murphy]

John,

Thanks, I'll give this a try.

Bill

Unfortunate as it may be, anytime you use an aggregate function (SUM being one)
in a query you can NOT update any value in the query.

You can however use DSUM

So something like the following may work. Watch out for the line wrap.

UPDATE DISTINCTROW tblProducerAssignments
SET
tblProducerAssignments.CurrentVolume =
DSUM("LoanAmount","Loan",
"LenderID = '" & LenderID &
"' AND AccountingDate Between #" &

[forms]![frmMain].[form]![txtBeginDate] &

"# AND # " & [forms]![frmMain].[form]![txtEndDate] & "#")


--- You might be able to add the following criteria line, but it may fail
WHERE LenderID IN
(Select LenderID
FROM Loan
WHERE Loan.AccountingDate Between [forms]![frmMain].[form]![txtBeginDate]
And [forms]![frmMain].[form]![txtEndDate])


If LenderID is a number field then remove the apostrophes next to the quote marks.

I just noted that you are referring to controls on a subform. This may or may
not work. Does you current subquery work?

When I run the following update query I get: 'Operation must use an
updatable query. (Error 3073)':

UPDATE DISTINCTROW tblProducerAssignments LEFT JOIN qryVolumeSummary ON
tblProducerAssignments.LenderID = qryVolumeSummary.LenderID SET
tblProducerAssignments.CurrentVolume = [Volume];

Here's the code for the sub-query qryVolumeSummary:

SELECT Loan.LenderID, Sum(nz([LoanAmount])) AS Volume
FROM Loan
WHERE Loan.AccountingDate Between [forms]![frmMain].[form]![txtBeginDate]
And [forms]![frmMain].[form]![txtEndDate]
GROUP BY Loan.LenderID;

I don't have permission problems on the database, and the sub-query is a
summary query and does not have multiple records per LenderID, so there is
not a one-to-many relationship. Any ideas on how to remedy this?

Bill