Cant Update Record (1 Viewer)

[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