group by query and adding an updatable checkbox fiels to update a table

[grain_r]

Hello Guys

I would like to ask for a little help because I have got a little stuck. I have a invoice system I have created in Access and it did used to work perfectly and then maybe an update Who knows stopped the system working.

I have an customer order screen that has customer details and then a subform within the same form this takes a total of items & costs for this order and then it entered it into the customers table from the order_Details table using me.Order_total = Order_Total.


I know this is bad design but I store it because once the order is places I need the total to be static because the invoice has been sent and so if someone changes the order then the total owed doesn't change.

I then have a reconcile form which is on a datasheet form straight from the tab;e so it is editable which has a checkbox that then once ticked copies the total from the Order_Total in the table to the reconciled field in the same table and then I use a report to show who owes me still.

So I have made a Select query from the Orders table & Order_Details table and used a Group by Order number (Which is the link between the 2 tables) this shows correctly but now not editable because of the rules So I am trying to add an editable checkbox on the same form.

I tried to use a dlookup makes all of the boxes either ticked or not. I've been looking at Recordsets but I'm unsure if this is what I need or not really

So really I just need a little help to see if I should be putting the Total from the Subform into the mainform and then entering it into the table (Like previously) using a calculated unbound field and then using the Afterupdate event to insert the data into the table. but for some reason it just wont work.
I can get the OnClick to work but then as soon as i go to the next record for some reason it then resorts to £0.00 but then the table shows correctly which ever record the form is working on.

Or should I be using the new query based idea to create the reconcile form and if this is the best way I just don't now where to start on how to get a multiple table query that I can then add a reconciled checkbox which then copies the Order_Total from the table to the Paymet_Recieved field.

I am very sorry to rabbit on. I have just got so confused which way is the best and what functions of ms access i should be looking at. I am using Access 2003 if this makes a difference.

Thank you so very much for even reading this let alone any help that will be very much appreciated.
I only really has simple programming knowledge of access and so dont really know what I need to learn to fix my issue.

I thought it might be best to include a copy of the database if anyone gets a little time to look at it.

Thanks

Rob

 

[JHB]

Hello Guys

I would like to ask for a little help because I have got a little stuck. I have a invoice system I have created in Access and it did used to work perfectly and then maybe an update Who knows stopped the system working.

Could you define "work perfectly"?
Because when I compile your project, I got a lot of bad code, (controls which doesn't exist ex. Me.Total etc. like MeParent.Subform_Total)

I have an customer order screen that has customer details and then a subform within the same form this takes a total of items & costs for this order and then it entered it into the customers table from the order_Details table using me.Order_total = Order_Total.

Are you sure, I can't see any total in the customers table, (and if it isn't correct to store any total in a customers table).

I know this is bad design but I store it because once the order is places I need the total to be static because the invoice has been sent and so if someone changes the order then the total owed doesn't change.

In my would an order is locked when the invoice/order has been sent and can't be changed.
If I should follow you "route" then I would mark each item included in the sent invoice/order and not storing any total but calculate it each time I need it!

I can get the OnClick to work but then as soon as i go to the next record for some reason it then resorts to £0.00 but then the table shows correctly which ever record the form is working on.

I'm not sure what/where you are talking about it here, but if it is in the "Orders" form, then you are setting it to 0, in the OnCurrent event.

 

[grain_r]

Hello JHB

First of All thank you for looking at my post it is very appreciated.

Yes I'm sorry the works perfectly seems a long way from where I am. this is the copy that I'm playing with to try and get it working so I don't mess my live data up. which is why the Summary totals are showing up every in desperation I have been putting code everywhere to try and get it working again. about 6 months ago the reconcile form just stopped showing the order total and so then when I clicked the payment recieved checkbox it then just copies the total it gets from the order_Total amount so although I have been ticking the box to show the payment has been recieved it doesn't show the amounts on the reconciled form.


So my aim:
is to enter a customers details and then choose what the customer is ordering and then total it up which then saves the value to the main forms table "orders" which then when i go to the reconciled form

it should show customer name, Invoice number, total order amount, payment recieved checkbox, and Amount paid.

when I click the checkbox the amount is then copied from the order total to the amount received.

So when I go through my list I can see quickly who still owes me. I have a check box because I don't get part payments so it will always be the full amount or nothing.

The problem:
I just cant get the Order total of the orders subform to show my my reconciled form it just shows £0.00 and then when I click the payment recieved checkbox it then just copies the £0.00 over to the payment recieved

so although i can still see who owes me by looking at the checkbox recieved. it doesn't mark off how much.

So now I'm stuck with my very much lacking knowledge

I tried 2 ways

1st was to copy the order total to the orders table but I just cant get it to save the calculated value to the table.

2nd I tried a different way of making a query and show every thing I need which does show in the "Reconciled New" form how I would like it to show but It wont then let me click onto the checkbox and say its paid.

I have included a couple of screen shots to hopefully show my wafflings.

once again thanks very much



rob.

 

[JHB]

For understanding it right and get it in the right fields, could you please show some printscreen of the following scenario, (I think (know) you've written it but a picture is better as a lot of words )?
I want to see how you put it in the order table.

1. A total new order.
2. An order which is just paid.
3. An order (paid) in which some new item is added.
4. The order in #3 fully paid.

And ofcause also how you want to see which is paid and which not, in the "Reconciled New" form, (especially where a part is paid and the other part which is not paid yet)!

Only for info, some of the problem you've just now is because some controls are unbound, and some are reset in the form load event and OnCurrent event.

 

[grain_r]

Hey JHB

Great thanks I will get the screen prints and pop them up.


Thank you so much.

Rob.

 

[grain_r]

Hey JHB

I hope this is the best way to show you I have put them all in one image on in a line for comparison.

I have manually edited how its suppose to work in mspaint.

Thank you very much

Rob

 

[JHB]

You've forgotten to show #3, (An order (paid) in which some new item is added.)!

 

[JHB]

Never mind, do some test now - create new orders, add item to new and old orders. For balance use "Reconcile-new" form.

 

[grain_r]

Good Morning JHB

I'm Sorry about that. although I never add to an order once it has been created although my system doesn't lock the order because its only me that uses it I didn't put any error checking to stop adding items so although the system would allow it to happen I would create a new order.

The reason for that is that I always get a written order placed to me and then a signature to confirm the order. so if they want anything I would then create a new order for the customer.
Because the customers have an account they don't pay until the end of the month (Well that's the plan anyway. )

Thank you very much

Rob.

 

[grain_r]

Good Morning again JHB sorry I didn't see your next reply.

Great thank you very much indeed I will look through it today and let you know later if that's OK.

I really do appreciate the time you spending on this.

Rob

 

[JHB]

I'm Sorry about that. although I never add to an order once it has been created although my system doesn't lock the order because its only me that uses it I didn't put any error checking to stop adding items so although the system would allow it to happen I would create a new order.

I thought it was an requirement because of the below, then I must have misunderstood you! .

I know this is bad design but I store it because once the order is places I need the total to be static because the invoice has been sent and so if someone changes the order then the total owed doesn't change.

 

[grain_r]

Hello JHB

I have just got back home and tried the database and I certainly now confirm that you are an absolute genius. I cant thank you enough for your help.

So it looks like I would have been starting to be on the right lines on using recordsets but I don't think I would have got there on it without some very serious amount of learning. I will go through the code that you so kindly have written for me and I will try and understand it all.

With people like you in the world willing to help and share your knowledge it makes the world a much better place.

Thank you

Rob

 

[JHB]

You're welcome, luck with it.
By the way, did you read my signature.

 

[grain_r]

Good Evening JHB

Just to let you know that yes I certainly put a Thumbs up on the reply that you sent the file through on. Which is hopefully correct. I'm a little new on the forum scene.

Many thanks again, I'm so happy with the system again.

Thanks rob