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

 

[Pat Hartman]

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

purchase and payment are separate tasks and are also separated by time. when you receive a check, the subform can use a separate query to select from. the parent form needs to keep a running total of the amounts selected to ensure they don't exceed the check amount.

 

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

 

[Pat Hartman]

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 a good solution. I would use a collection to collect the selected ID's or use a temp table.

When you update the main table, you prevent others from performing a similar action at the same time and this becomes a problem in a production environment. You won't be able to have two people processing checks at the same time if you do this. Although, if you tag the records with the userID and/or CheckID, you might be able to support multiple users concurrently.

You still don't need to use the same non-updateable query and that would end up being the simplest solution.

 

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

 

[Pat Hartman]

Typical solution to the sum you have is to not sum the values in the query. Add a subtotal to the footer of the subform. Then in the afterupdate event of the subform, you can copy the subtotal from the subform footer into the main form. That avoids the need to violate 2nd and doesn't cause your subform to be not updateable.

 

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

 

[Pat Hartman]

If the data actually NEVER changes, then storing sums does not cause any danger. If there is the least possibility that you can change the details, then it is simply too dangerous to violate second normal form.

 

[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

 

[Pat Hartman]

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.

Your explanation makes no sense to me. Gemma explained one reason why you might choose to violate second normal form by storing a calculated value. But I can't figure out what constraint you see that you think is forcing you to save the calculation.

Your invoice form should be a main form with the invoice header and a subform with the items. I explained earlier how you can get a running total on the form's header and not have to store a calculated value. Your invoice report is a main report with a subreport. There is no problem with totalling the subform/subreport and showing the total on the main form/report.

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.

That isn't where the conflict arises. The issue with storing a calculated value is that if you go back later for example and change a quantity, you need to add code to recalcuate the total and update the parent record but there is a time gap between the change to the detail item which perforce must be saved after being changed and when you update the parent record and save it. It's a tight window but the two updates are disconnected so there is always a possibility of a power outage or LAN drop between the two. That leaves your data in an inconsistent state.

There are ways around this inconsistent state but they involve unbound forms and transactions which are used to corral all related updates so that the updates either all succeed or all fail.

Even Gemma's rational reason for storing the calculated value leads to a vulnerability unless it is done using unbound forms.

 

[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

 

[Pat Hartman]

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.

Making a group first just complicates the process. I don't know your business rules so I don't know if you commonly have partial payments or payments that cover multiple invoices and if those payments cover multiple invoices, must they all be paid in full or can there be partial payments.

I also don't know how you receive payments. Do you get checks in the mail? Do the customers use electronic payments? Do they call you with a CC or bank check number?

You haven't described the method you are using to calculate an invoice. Does an invoice cover multiple orders?

When you create an order, are you storing the total in the order header? When do you calculate the order total? Do you have guardrails in place that prevent you from updating the details of the order once the total is calculated and posted to the header?

You also seem to be using the same form for multiple purposes. Maybe you need to focus in a little.

There are so many moving parts to this type of a process that it is hard to give concrete instructions.

So, let me offer some ideas.
I don't like storing the order total when the order is placed. However, if you put the right procedures in place, you can probably get away with doing it. I would use a complete flag on the order. That way, you don't even think about updating the order total until the order is complete. This is the way most web pages work. They will frequently keep a running total of what is in your cart but no taxes or shipping or final total can be calculated until the order is complete. Once an order is marked complete and the tax/shipping and total are calculated and posted to the order header, NO CHANGES are allowed. If a customer wants to make a change, the order has to be "pending" or whatever you want to call the stage prior to shipping. Then you would change CompleteYN to no and set the calculated values to null. Then the user can add/change/delete whatever and when finished, mark the order complete again and calculate and post the calculated values.

I would make a payments form and not reuse an existing form. This form would have a listbox with all the shipped but unpaid orders. You can use a multi-select listbox for this process. You can enter the payment amount and method. Then select one or more of the unpaid invoices. As each invoice is selected, you check your accumulator to see if the payment amount has been reached or exceeded. If three items are selected and the total = the payment amount, you should prevent selecting additional items. You can then push a button and your code can loop through the selected items and update each record and enter the PaymentID, payment amount, and date.

If the payment amount doesn't equal the oldest x items and you have a remainder, you can update the fully paid records and apply the overage to the next order in line.

If the payment amount exceeds the total for the unpaid items, you have to put the payment amount somewhere. You can't just ignore the discrepancy so you need to update the payment record with a suspense amount.

You really need to get with your accountant to have him tell you how he wants to track payments and suspense amounts.

And finally to tie all this up, you need to run queries frequently to find partially paid invoices so you can bill again. You also need to find suspense amounts so you can refund the over payment as appropriate.