Problem - can create an updatable query

[Guest]

I have a database to manage inventory, invoices and receipts.
I have two tables for the receipts, Receipts and ReceiptsDetails.
The table Receipts has the fields ReceiptN, Date and Clients. The table
ReceiptsDetails has the fields ReceiptN, Invoice and Value.
The two tables are related by the field ReceiptN.

I am building the form to create new receipts.
This form has a main form and a subform.
I would like the form to have datasheet view with the fields:
1. Invoice – Document to be paid;
2. Open Value – Invoice amount not yet paid;
3. Value – Amount being paid;
4. Remaining Value – open value after current payment.

So, I have as datasource for the subform this query

SELECT Invoice.NIF AS Client, Invoice.DocID AS Invoice,
(nz([Qtt]*[price]*(1+[tax]),0)-(nz([value],0))) AS Open,
Receipts.Value, Receipts.ReceiptN
FROM (Invoice LEFT JOIN REceipts ON Invoice.DocID =
REceipts.Invoice) INNER JOIN Sales ON Invoice.DocID =
Sales.DocID
WHERE (((Left([Invoice].[DocID],2))="IN")) OR
(((Left([Invoice].[DocID],1))="N"));

PROBLEMS:
1. This query is not updatable. How is it possible to have it updatable?

2. I would like to have invoice field showing only the invoices for the
client on the main form. I am thinking of changing it to a combo box, with
the line source being a query. Something like:
SELECT …
Where Invoice.NIF = mainform.Client

How do I correctly state this Expression (mainform.Client)?

 

[Guest]

This is a very complicated subject. I suggest that you open up Access Help,
go to the Find tab, type in "updatable" (lower case u) or "About updating
data", and then scroll down to "When can I update data from a query?" or "Why
can't I edit data in my form?". There you will find a lot of, possibly too
much, information on the subject. In a nutshell, if the query is based on one
table or tables with a one-to-one relationship, you will be able to edit or
delete records. If it is based on two or more tables with a one-to-many
relationship, you 'should' be able to edit or delete records. If you have
three or more tables based on many-to-one-to-many relationships, you will not
be able to edit or delete records. This is just the highlights. Help has much
more information.

Looking at your SQL is see a Left Join and some calculated controls plus
usage of functions. About anyone one of those three items could stop a query
from being updateable. Also go to the Relationships Window and see if there
is a relationship defined between those two tables. If so see if you can get
Referiential Integrity to work on the relationship. If not it's doubtfull if
you can make an updateable query.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a database to manage inventory, invoices and receipts.
I have two tables for the receipts, Receipts and ReceiptsDetails.
The table Receipts has the fields ReceiptN, Date and Clients. The table
ReceiptsDetails has the fields ReceiptN, Invoice and Value.
The two tables are related by the field ReceiptN.

I am building the form to create new receipts.
This form has a main form and a subform.
I would like the form to have datasheet view with the fields:
1. Invoice – Document to be paid;
2. Open Value – Invoice amount not yet paid;
3. Value – Amount being paid;
4. Remaining Value – open value after current payment.

So, I have as datasource for the subform this query

SELECT Invoice.NIF AS Client, Invoice.DocID AS Invoice,
(nz([Qtt]*[price]*(1+[tax]),0)-(nz([value],0))) AS Open,
Receipts.Value, Receipts.ReceiptN
FROM (Invoice LEFT JOIN REceipts ON Invoice.DocID =
REceipts.Invoice) INNER JOIN Sales ON Invoice.DocID =
Sales.DocID
WHERE (((Left([Invoice].[DocID],2))="IN")) OR
(((Left([Invoice].[DocID],1))="N"));

PROBLEMS:
1. This query is not updatable. How is it possible to have it updatable?

2. I would like to have invoice field showing only the invoices for the
client on the main form. I am thinking of changing it to a combo box, with
the line source being a query. Something like:
SELECT …
Where Invoice.NIF = mainform.Client

How do I correctly state this Expression (mainform.Client)?

 

[Guest]

Thank you very much Jerry.
I understand that “this†query will not do what I need.

Do you see any way I can do it?

Any suggestion, anything is most welcome, because I can’t figure other way
of doing this.


"Jerry Whittle" escreveu:

This is a very complicated subject. I suggest that you open up Access Help,
go to the Find tab, type in "updatable" (lower case u) or "About updating
data", and then scroll down to "When can I update data from a query?" or "Why
can't I edit data in my form?". There you will find a lot of, possibly too
much, information on the subject. In a nutshell, if the query is based on one
table or tables with a one-to-one relationship, you will be able to edit or
delete records. If it is based on two or more tables with a one-to-many
relationship, you 'should' be able to edit or delete records. If you have
three or more tables based on many-to-one-to-many relationships, you will not
be able to edit or delete records. This is just the highlights. Help has much
more information.

Looking at your SQL is see a Left Join and some calculated controls plus
usage of functions. About anyone one of those three items could stop a query
from being updateable. Also go to the Relationships Window and see if there
is a relationship defined between those two tables. If so see if you can get
Referiential Integrity to work on the relationship. If not it's doubtfull if
you can make an updateable query.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a database to manage inventory, invoices and receipts.
I have two tables for the receipts, Receipts and ReceiptsDetails.
The table Receipts has the fields ReceiptN, Date and Clients. The table
ReceiptsDetails has the fields ReceiptN, Invoice and Value.
The two tables are related by the field ReceiptN.

I am building the form to create new receipts.
This form has a main form and a subform.
I would like the form to have datasheet view with the fields:
1. Invoice – Document to be paid;
2. Open Value – Invoice amount not yet paid;
3. Value – Amount being paid;
4. Remaining Value – open value after current payment.

So, I have as datasource for the subform this query

SELECT Invoice.NIF AS Client, Invoice.DocID AS Invoice,
(nz([Qtt]*[price]*(1+[tax]),0)-(nz([value],0))) AS Open,
Receipts.Value, Receipts.ReceiptN
FROM (Invoice LEFT JOIN REceipts ON Invoice.DocID =
REceipts.Invoice) INNER JOIN Sales ON Invoice.DocID =
Sales.DocID
WHERE (((Left([Invoice].[DocID],2))="IN")) OR
(((Left([Invoice].[DocID],1))="N"));

PROBLEMS:
1. This query is not updatable. How is it possible to have it updatable?

2. I would like to have invoice field showing only the invoices for the
client on the main form. I am thinking of changing it to a combo box, with
the line source being a query. Something like:
SELECT …
Where Invoice.NIF = mainform.Client

How do I correctly state this Expression (mainform.Client)?