Operation must use an updatable query

  • Thread starter Thread starter A.M
  • Start date Start date
A

A.M

Hi,
In following query, access gives me error: "Operation must use an updatable
query"
qryLinePerInvoice is read only but i am not writing anything on it.
tblSavedInvoices is regular writable table.


UPDATE tblSavedInvoices
SET tblSavedInvoices.Lines =
(
select [qryLinePerInvoice].[Lines] from qryLinePerInvoice where
tblSavedInvoices.invoice_no = qryLinePerInvoice.invoice_no
);



If i cahnge the query to the following form, still i have same problem:


UPDATE tblSavedInvoices INNER JOIN qryLinePerInvoice ON
[tblSavedInvoices].[invoice_no]=[qryLinePerInvoice].[invoice_no] SET
tblSavedInvoices.Lines = [qryLinePerInvoice].[Lines];



Any help would be appreciated,
Ali
 
Hi,


I would try:

UPDATE tblSavedInvoices SET Lines=DLookup("lines", "qryLinePerInvoice",
"invoice_no=" & invoice_no)


If your query qryLinePerInvoice has an Aggregate in it, it then marks the
query referring to it as not updateable. Using a Dxxx function is a possible
solution, make the query (with the aggregate) a temp table is another one.



Hoping it may help,
Vanderghast, Access MVP
 
If you simply run

UPDATE tblSavedInvoices SET tblSavedInvoices.Lines ='value', does the error
occur?



Sincerely,

Alick Ye, MCSD
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.


--------------------
| From: "A.M" <[email protected]>
| Subject: Operation must use an updatable query
|
| Hi,
| In following query, access gives me error: "Operation must use an
updatable
| query"
| qryLinePerInvoice is read only but i am not writing anything on it.
| tblSavedInvoices is regular writable table.
|
|
| UPDATE tblSavedInvoices
| SET tblSavedInvoices.Lines =
| (
| select [qryLinePerInvoice].[Lines] from qryLinePerInvoice where
| tblSavedInvoices.invoice_no = qryLinePerInvoice.invoice_no
| );
|
|
|
| If i cahnge the query to the following form, still i have same problem:
|
|
| UPDATE tblSavedInvoices INNER JOIN qryLinePerInvoice ON
| [tblSavedInvoices].[invoice_no]=[qryLinePerInvoice].[invoice_no] SET
| tblSavedInvoices.Lines = [qryLinePerInvoice].[Lines];
|
|
|
| Any help would be appreciated,
| Ali
|
|
|
 
The problem doesn't occure in that case.

The problem is in UPDTE query, even i try to "Read" (Not write) from a
readonly query, it gives me error, which obviously is a bug.

Thanks,
Ali
 
A.M said:
The problem doesn't occure in that case.

The problem is in UPDTE query, even i try to "Read" (Not write) from a
readonly query, it gives me error, which obviously is a bug.
Click to expand...

A PITA perhaps, but I'm not sure it can be called a bug. Access requires that *all*
of the tables/queries used in an update query be editable. Always has been that way
AFAIK.
 
Hi Ali,

Michel's method should work, please feel free to reply to the threads if
you have any questions or concerns.



Sincerely,

Alick Ye, MCSD
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

--------------------
| From: "A.M" <[email protected]>
| X-Tomcat-NG: microsoft.public.access.queries
|
|
| The problem doesn't occure in that case.
|
| The problem is in UPDTE query, even i try to "Read" (Not write) from a
| readonly query, it gives me error, which obviously is a bug.
|
| Thanks,
| Ali
|
|
|
| | > If you simply run
| >
| > UPDATE tblSavedInvoices SET tblSavedInvoices.Lines ='value', does the
| error
| > occur?
| >
| >
| >
| > Sincerely,
| >
| > Alick Ye, MCSD
| > Microsoft Online Partner Support
| >
| > Get Secure! - www.microsoft.com/security
| > This posting is provided "as is" with no warranties and confers no
rights.
| >
| >
| > --------------------
| > | From: "A.M" <[email protected]>
| > | Subject: Operation must use an updatable query
| > |
| > | Hi,
| > | In following query, access gives me error: "Operation must use an
| > updatable
| > | query"
| > | qryLinePerInvoice is read only but i am not writing anything on it.
| > | tblSavedInvoices is regular writable table.
| > |
| > |
| > | UPDATE tblSavedInvoices
| > | SET tblSavedInvoices.Lines =
| > | (
| > | select [qryLinePerInvoice].[Lines] from qryLinePerInvoice
where
| > | tblSavedInvoices.invoice_no = qryLinePerInvoice.invoice_no
| > | );
| > |
| > |
| > |
| > | If i cahnge the query to the following form, still i have same
problem:
| > |
| > |
| > | UPDATE tblSavedInvoices INNER JOIN qryLinePerInvoice ON
| > | [tblSavedInvoices].[invoice_no]=[qryLinePerInvoice].[invoice_no] SET
| > | tblSavedInvoices.Lines = [qryLinePerInvoice].[Lines];
| > |
| > |
| > |
| > | Any help would be appreciated,
| > | Ali
| > |
| > |
| > |
| >
|
|
|
 
Back
Top