Operation must use an updatable query

[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

 

[Michel Walsh]

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

 

[Alick [MSFT]]

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
|
|
|

 

[A.M]

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

 

[Rick Brandt]

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.

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.

 

[Alick [MSFT]]

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
| > |
| > |
| > |
| >
|
|
|