Unable to update data in simple query

[patrickd123]

Hi,

I just created another query, but with this one I cannot change any of the data. What could be the reason?

Thanks

 

[RuralGuy]

Maybe this link will help explain the problem.

 

[patrickd123]

Bingo!!! Thanks

 

[RuralGuy]

Great! Thanks for posting back with your success.

 

[patrickd123]

Half a success. Now I know why it was read only. But here is what I am trying to do unsuccessfully:

I have invoices and of course, invoice lines. I need a form(tabular) on which each line is an invoice and one of the fields is the total amount. To have that total, I need to base my form on a query(invoice and invoice items group by invoice ID and sumed up on the line amount to get the total amount of all lines). Also on that form I need one editable field which is the invoice credit card confirmation number that the clerk can fill in when is cashing the credit cards of the customers. But that field is read-only and with your help, I now know why. But I really can't see how I can work around this problem.....

 

[RuralGuy]

It is difficult to offer suggestions without seeing your db. Having said that, do you have your invoices in one table and the invoice "lines" in another related by maybe Invoice Number? Do you have your form set up bound to the Invoice query/table and a SubForm on it bound to the invoice "lines" query/table?

 

[neileg]

Queries based on an aggregate function, e.g. a totals query, will end up being non-updateable. It may solve the problem if you use DSum() to retrieve the invoice total instead of the aggregate query. May be a performance hit, mind you. If it is, you may get away with simply displaying the result of the totals query, rather than joining it with your invoice header data.

Then again, you could ignore the advice I always give, and store the result of the calculation in the header data. The justification is that this represents the value of the CC transaction.

 

[patrickd123]

It is difficult to offer suggestions without seeing your db. Having said that, do you have your invoices in one table and the invoice "lines" in another related by maybe Invoice Number? Do you have your form set up bound to the Invoice query/table and a SubForm on it bound to the invoice "lines" query/table?

I have one invoice table and one invoice item table. It is on those two table that I did a query. The query is group by invoice number and sumed up on the item amout(to get the total amout). It is on that quey that I based my tabular form.

Each line on the tabular looks like:

Invoice# ; Invoice date ; total amount ; Confirmation amount

The first 3 fileds are read-only. It is the last field that I need to be editable so that we can enter the credit card confirmation number after cashing the amount from mastercard.

Thanks

 

[RuralGuy]

Hi Patrick,
I think Neil has explained why you are having problems with your approach. Do you understand what he is suggesting?