Query not updatable.....

[graeme34 via AccessMonster.com]

Hi I have the following SELECT query that has turned out to be unupdatable....
..I think it may have too many table joins ???
I have tried it without the DISTINCT and the equation but it is still not
letting me update
here is the query:

SELECT DISTINCT SI.SalesInvoiceNumber, SI.InvoiceDate, SI.TotalNett,
V.VATValue, SI.SalesInvoicePaid, [TotalNett]*([VatValue]/100+1) AS
InvoiceTotal
FROM tblVAT AS V INNER JOIN ((tblDespatch D INNER JOIN tblSalesOrderLine AS
SOL ON D.SalesOrderNumber = SOL.SalesOrderNumber) INNER JOIN tblSalesInvoice
AS SI ON D.DespatchNumber = SI.[Despatch Number]) ON V.VATRate = SOL.VATRate
WHERE SI.SalesInvoicePaid = False
ORDER BY SI.SalesInvoiceNumber;

What I am trying to do is create a recordset for a subform, the subform shows
details of previous unpaid invoices, the SalesInvoicePaid field is a yes/no
box. On the subforms master form is a data entry form for payments received...
..I want the user to tick the yes/no box on the subform if that invoice is
part of this payment....
Alas the query turned out to be unupdatable......I cant make the yes/no
unbound as the sub form has multiple records and selecting one yes/no box
affects all of them....any suggestions from the experts ???
Thank you in advance
Graeme

 

[Ken Snell \(MVP\)]

A query with DISTINCT is not updatable. Also, to be updatable, the query
must include the primary key field of the table that is being updated.

 

[fredg]

Hi I have the following SELECT query that has turned out to be unupdatable....
.I think it may have too many table joins ???
I have tried it without the DISTINCT and the equation but it is still not
letting me update
here is the query:

SELECT DISTINCT SI.SalesInvoiceNumber, SI.InvoiceDate, SI.TotalNett,
V.VATValue, SI.SalesInvoicePaid, [TotalNett]*([VatValue]/100+1) AS
InvoiceTotal
FROM tblVAT AS V INNER JOIN ((tblDespatch D INNER JOIN tblSalesOrderLine AS
SOL ON D.SalesOrderNumber = SOL.SalesOrderNumber) INNER JOIN tblSalesInvoice
AS SI ON D.DespatchNumber = SI.[Despatch Number]) ON V.VATRate = SOL.VATRate
WHERE SI.SalesInvoicePaid = False
ORDER BY SI.SalesInvoiceNumber;

What I am trying to do is create a recordset for a subform, the subform shows
details of previous unpaid invoices, the SalesInvoicePaid field is a yes/no
box. On the subforms master form is a data entry form for payments received...
.I want the user to tick the yes/no box on the subform if that invoice is
part of this payment....
Alas the query turned out to be unupdatable......I cant make the yes/no
unbound as the sub form has multiple records and selecting one yes/no box
affects all of them....any suggestions from the experts ???
Thank you in advance
Graeme

Some queries are not updateable.
See Access help:
Query + Troubleshoot queries + Select Query + I can't update data
from a query + Data can't be updated

 

[graeme34 via AccessMonster.com]

Hi Ken
I had tried running the query without the DISTINCT yet it still wouldnt
update also the yes/no box belongs to the SalesInvoice table which has the
primary key SalesInvoiceNumber.......??
The only alternative I can think of is to make a new table based on the
SELECT query append the result of the query to this, use the table as the
control source for the subform, then delete the table when the user closes
the form.

A query with DISTINCT is not updatable. Also, to be updatable, the query
must include the primary key field of the table that is being updated.

Hi I have the following SELECT query that has turned out to be
unupdatable....

[quoted text clipped - 28 lines]

Thank you in advance
Graeme

 

[Ken Snell \(MVP\)]

Did you see my comment about needing to include the table's primary key
field in your query?

--

Ken Snell
<MS ACCESS MVP>

Hi Ken
I had tried running the query without the DISTINCT yet it still wouldnt
update also the yes/no box belongs to the SalesInvoice table which has the
primary key SalesInvoiceNumber.......??
The only alternative I can think of is to make a new table based on the
SELECT query append the result of the query to this, use the table as the
control source for the subform, then delete the table when the user closes
the form.

A query with DISTINCT is not updatable. Also, to be updatable, the query
must include the primary key field of the table that is being updated.

Hi I have the following SELECT query that has turned out to be
unupdatable....

[quoted text clipped - 28 lines]

Thank you in advance
Graeme