Cant Update Record

[ChrisLeicester]

Hi All

I have an unpaid invoice screen as shown below

My idea was that once approved, the user can tick the yes/no box and then a payment pop up form will open up and list all the ticked invoices to then be paid by the one payment record, a one to many, one payment can pay many purchases.
However, because my subform data comprises of a totals query, to calculate the purchase totals on each line, I am unable to tick the yes/no box as it says the record is is not updatable.

Is there a work around for this?, i did think about a command button to select to pay that may be able to change the tick box in the record and then a requery to redo the record line?

How would you more experienced people go around doing this?

Thanks

I

 

[Gasman]

Can't you just link back to your totals query?

 

[ChrisLeicester]

Im not sure.
The recordset for this subform comes from the totals query (Which just gives me totals) and then the purchases table which contains the order date, delivery date, fields etc. Maybe I have designed the totals query wrong. I just have 3 fields in the totals query, purchaseorderID, sum of purchasedCost and sum of casespurchased.

 

[arnelgp]

i did think about a command button to select to pay that may be able to change the tick box in the record and then a requery to redo the record line?

this will do fine.

 

[ChrisLeicester]

Thank you, will have a go with the button

 

[ChrisLeicester]

Hi
Yes they are separate things, and as I can pay many invoices with one payment I needed a way to tick to select which invoices I am paying.

I found on YouTube a way to tick the yes/no by putting a transparent button on the form over the tick box and then running an update query in the table when clicked

Will be trying that today.

Not sure if I will get the action query warning which I would like to turn off if I do so that might be my next task

Thanks

 

[ChrisLeicester]

Hi Guys

I have copied Richard Rost's youtube example but am getting the following error

"Syntax error (missing operator) in query expression 'PurchaseOrderID='

here is my code

CurrentDb.Execute "Update PurchaseOrderTB " & _
" Set InvoiceStatus = 4 " & _
" Where PurchaseOrderID=" & PurchaseOrderID


I have used the same formatting as Richard and cant see my error.

The aim is to update the field InvoiceStatus to value 4

Thanks

 

[arnelgp]

do you have the Field/variable PurchaseOrderID on your form?

 

[ChrisLeicester]

Thank you,
no I didnt, was in the recordset but not on the subform, added it now and working as wanted
thanks again

 

[Gasman]

Put it into a string variable and debug.print it, before even trying to use it.

Sounds like you purchaseID is empty? I would always use Me.PurchaseID if it comes from form/report.

 

[gemma-the-husky]

First, I would definitely store an invoice total on the invoice record, then you would avoid the problem of the totals query. You also need a paid so far, or an outstanding amount which takes into account part payments.

If the payment doesn't completely pay an invoice though, you have to do something different. I would have a payment allocation table that matches the payment record to the matched/partly matched invoices.

Then you can identify fully paid invoices etc, and avoid matching them more than once, which may happen.

You need to think carefully about how you need the matching process to work.

(From an accounting/user experience perspective)

 

[gemma-the-husky]

Because of rounding issues, and for the avoidance of doubt I would definitely store the invoice and sales tax total declared in your accounts within the invoice table. I appreciate it can be calculated, but the rounding effect introduces an ambiguity that you don't want in legal accounting records, in my view.

Besides which, an invoice won't change after it's been issued, so storing the calculated value hardly introduces risks to your system.

 

[ChrisLeicester]

Hi Pat and Gemma,
I have just come back and read your comments and will rethink my methods.

I cant do a calculated sum on the invoice form because due to the one to many items in each invoice. I have a purchase order main table and then a purchase order items table. To get the totals I query the items in each purchase and then total.

I also was of the impression never to put totals in tables and always calculate on the go, which is how I have been designing this database. I will consider the merits of at least adding invoice totals to the purchase order table for future ease.

Thankfully, due to volume of records, I dont envisage multiple people needing to use the same purchases at the same time, so I dont think there will be conflict issues down the line.

Thanks again for the advice

 

[ChrisLeicester]

Hi
I think I maybe didnt explain myself properly,

I have an invoice form which has as you suggest, a main form showing invoice details such as supplier, date, status etc and then a subform which links the invoice items, qty and item cost. This is easily totalled with text boxes as you suggested.

The form in this thread is a list of now 'unpaid' invoices, so the main form is the supplier, and the subform is the individual invoices in continuous form, so one line/record is one invoice.

Should this be a report, then i would easily group by invoice, show no detail and have a group footer with calculated fields for the invoice total. Of course, forms dont work like that, so I use a total query to get the invoice total, then use another query to join these totals to the main invoice details which is used in my subform on the 'invoice list' form, but also being not updatable.

Unless I am missing something, and being a novice, that is highly likely, i cant think of using text boxes to calculate invoice values any other way.

The thought process/work flow of the form was that I had a list of invoices to be paid, I tick and select which (not all) of the ones I want to pay and then by pressing a button, a payment record (from invoice payment table) will be created and then each of the selected invoices will have that payment table id placed in their foreign key field.

I do have the form working, but would prefer it to be as robust as possible, and learn best practice for next time and I am considering Gemma's advice to store an invoice total in the main invoice table, you are right that once raised, it wont change, and should ever the need for the value to be adjusted, would be done by credit note anyway, and not changing the invoice.

Gemma is also right about the rounding up issue, very few of my purchases are VAT items, but a few are and I did see that the actual suppliers invoice VAT totals can be a few pence off the calculated vat element, so that is one good enough reason for rethinking my method

Thanks for the advice