I thought I could manually change the discount amounts in the tblCustomerDiscount, but maybe not.?
I thought I could manually change the discount amounts in the tblCustomerDiscount, but maybe not.?
You could but any older data would reflect the current discount which will mess up your accounting. So, if you do not want to keep a history of the discounts over time, you will have to store the discount at the time of the order (just like you currently store the price of the item). If that is how you want to proceed then I will add the code to do that in the database you posted the other day. Just let me know.
yes please that would be most helpful.
I've added the discount field to the order detail table and added the code in the after update event of the combo box in the subform. I also added the categoryID to the product combo box in the subform since the code needs that to find the discount. I also noticed that you used a query that included both the order detail table and the product table as the record source for the subform. This is incorrect. The subform should be based soley on the order detail table; I made the necessary corrections. The revised DB is attached.
many thanks again , I will study the Database hard now and hopefully not have to bother you any more.
You're welcome. Please do not hesitate to ask if you have further questions.
just one question , should the price and discount fields in the subform be auto populating, or has that changed due to the subform now being based on the orderDetails table?, if so what's my best route to achieve this.
also I noticed that the CustomerCatergoryDiscount is not present in the relationships. is that correct ?
The price and discount fields should populate after you make a selection from the product combo box in the subform. For records that were already present (before my change), you will have to reselect the applicable product in the combo box.
For the CustomerCatergoryDiscount table, I changed the name to CustomerCategoryDiscount (you had an extra r in the name). If it is not in the relationship diagram, go ahead and add it back in. (I did not go back and check that)
yes your right after a few clicks its working,Prices are popping up now one thing the prices are rounding up or down too the nearest 1.00 or 2.00 and so on im guessing this would be something to do with the format or data type of the Price box.
Yes, you need to change the datatype of the price field in the order detail table to currency
Wow its really coming together now, feels like a working thing, Thank you so much for your help.
Should I Work out the discount by way of a sum in the form or later when invoicing?
I think I should work it all into a report
You can actually do both. You could add a control in your subform, to calculate the total for each line item incorporating the discount (you would not store the total). You could also display the overall total for the order (see this site)
Question to all now. JZWP11 has been a fantastic help and deserves a mention, Anyway I manged to work out something that works but dont know if im allowed to do it this way. It involves my calculation for working out line total for [Quantity] x [Price] x [Discount]/100 this formula gives me a line total which is the for example
unit price £10 with a discount of 20% the calculation gave me a line total of £2 ? SO i changed the discount amount in the discount field to the remainder out of 100 I.e 80% instead of 20% and HEY PRESTO it returns the correct value in the line total field
am i cheating .
That is the typical way of calculating a discount 1-(20/100)=0.80 or (100-20)/100=0.80 or 80% of the the original price
Hi JZW , I was wondering , what's the best way of using the Invoice table, in my DB as at the moment it is empty, I thought it might populate as I entered orders but no. I have been searching on sites and some people use a report to populate fields in the Invoice table, im not sure ? maybe a query first.