Subform "SUM" problem HHHEEELLLLPPPP !!!

[shenty]

Ok i am pulling my hair out here i am desperate for some help.

I am writing a database at the moment (hence my post), i am struggling with something that is doing my nut so i will try to explain the basics here:

I have a main form and a subform, the main form consists of a list of different medicines via a combo box and some other related fields to that medicine. Then in the main form i have a subform which consists of i list of medicine administration dates and other related fields.

1 of the fields in the subform is an 'amount' field where i enter the amount of medicine that is administered on an animal. In the footer i have a text field that sums the total with =sum([Amount])

that works OK.....

Back in the main form i have a field that shows how much medicine was purchased (control source is AmountPurchased from table), then under that is a text box thats control source is the 'sum' from the subform.....that works OK too.

Under that is another text box which is qtyLeft. This also works OK and displays the calculated amount of medicine that is left. Now...going back to the subform, when i entered or changed the quantity in the amount field it initially would not update the qtyLeft until i finished entering the rest of the record. So what i had to do was, in the event onExit, add DoCmd.RunCommand acCmdSaveRecord which worked fine. When i exited the amount field it would save the record and update the calculated fields up in the main form.

Now here is where my problem starts....

In the main form i have got another field under the qtyLeft which is AmountLeft, and also a tick box next to it that is called "Empty". This AmountLeft field is not calculated but SHOULD get its value from the qtyLeft calculated field IF the Empty tick box is not ticked.

Now i'm trying not to confuse the reader, and the reader might ask why this is so, so i feel i should explain why. OK....lets say the medicine is a bottle of say 100ml of medicine, the subform is a list of dates that the medicine was used. At some point there may only be 5ml or so left or the expiry date of the medicine maybe past so i have set an OnClick event for the Empty tick box which when clicked sets the AmountLeft to Zero (for the reports). This works fine too.....even though the qtyLeft field still shows 5ml this is fine because it is just a calculated text box that saves no where. The AmountLeft is what writes back to the main table via the query.

Hope ya with me so far.

In the main form i have an OnCurrent event that reads something like this:

If Me.Empty = True Then
Me.AmountLeft = 0
End If

So when i scroll through the main form medicine it knows to keep the AmountLeft field as Zero if the Empty tick box is ticked.

BUT when i change it to:

If Me.Empty = True Then
Me.AmountLeft = 0
Else
Me.AmountLeft = Me.qtyLeft
End If

IT DOESN'T WORK............this is my problem. The AmountLeft field ALWAYS shows the original AmountPurchased.

I have tried adding an OnExit event to the Amount field in the subform like this:

DoCmd.RunCommand acCmdSaveRecord (this forces an update of the qtyLeft calculated textbox)

If forms!frmMedicineAdministered!Empty = False Then
Forms!frmMedicineAdministered!AmountLeft = forms!frmMedicineAdministered!qtyLeft
End If

But this only seems to work if i exit the Amount field in the subform THEN go back in and back out of it.

I have also tried doing it via a macro with the same problem, i have also tried many other things but whilst the qtyLeft field updates OK i just can't get it to update the AmountLeft field to match if the Empty tick box is not ticked.

Sorry if this problem took so long to explain but it is seriously annoying me now and maybe the solution is staring me in the face.

I can upload my db or some screen shots if this would be helpful.

Many thanks in advance for any help - i have learnt so much from this forum already and really do appreciate everyones help.

 

[CEH]

It's late...I'm tired... but.....
Try a refresh.......

Else
Me.Refresh
Me.AmountLeft = Me.qtyLeft
End If

See if that helps you.

 

[shenty]

Nope still no joy.

Any more ideas - this is frustrating. I thought i'd tackle it fresh again today but i'm getting back to how i was yesterday now.

I just want to set the value of a field the same as another box if a certain tickbox is not ticked, why is it giving me such a headache.

I will upload my db shortly.

 

[shenty]

Database attached.

If you change one of the amounts in the subform, you will see the calculated qtyLeft field is updated (due to the DoCmd.RunCommand acCmdSaveRecord) but the AmountLeft field is not updated.

In the 'OnCurrent' event for the main form i have remarked out what i thought would have worked but it didn't.

Any help would be great.

 

[CEH]

At first glance....a question... Is there a reason to have the field "AmountLeft"? It's a calculation....why store it?

 

[shenty]

AmountLeft is not actually a calculation, qtyLeft is a calculation.

AmountLeft wants to be equal to the calculation qtyLeft if the Empty tick box is not ticked.

The reason is: say the medicine is a 100ml bottle, and 97ml has been used on animals or the expiry date is past. I need to be able to say waste the remaining 3ml so it doesn't show up on the reports i create. As i don't want small remaining amounts to keep showing up on reports.

By using the AmountLeft field (that writes back to the table) combined with the Empty tick box i can control what will and won't show up on these reports.

The use of the qtyLeft calculated field is so that i can see how much medicine is truely left whilst the bottle is being 'used up'. Say i was entering 4 records of the same medicine type i can see that i'm not using more than what is actually in the bottle. And if i just increased 1 record by the 3ml then effectively i'd be overdosing an animal !!! Which wouldn't look good on the reports.

Medicine such as syringes or cans or tablets wouldn't have this problem so when they get down to zero i simply tick the Empty tick box which forces the AmountLeft to zero and it stays that way.

Hope you follow.

 

[shenty]

OK i've added the report i'm on about. If you pick a medicine that is nearly empty then tick the empty tick box it will force the empty status to zero and not show up in the report.

This is what i'm after. But when you tab out of the amount box in the subform you have to go back in then back out before the Actual Stock Left (AmountLeft) field is corrected.

 

[CEH]

Well, if no one else has looked at this I'll do it when I get home... But... The calculated field still holds... As a general rule (with few exceptions...and some say none) Calculated fields are not stored. And in this DB you do not need to store it to get the same results. Simply add the field in your query. FieldBla: =Field1-Field2
But as I said if no one else gives you the answer, I'll play with it when I get home.. try a few things. Alot of good coders here who could probably answer without needing to look more into it... but not me

 

[shenty]

I get what you're saying but i'm not trying to store the calculated field.

I want to copy the value from the calculated field, to a field which is stored. Then at some point when i click the tick box i want to make the stored field zero regardless of the value of the calculated field.

IE when the calculated field gets down low but still a positive number OR when the expiry date is past. So if there is still a few millilitres of medicine left but its expired i want to override the Actual Stock Left field to set it to zero so the medicine can be thrown away, but up until this point still store the amount left (if you get me!). Then when i create the report for medicine in stock it does not show that particular medicine.

I have also corrected a minor error and attached the current db.

Thanks again for spending time looking at this i do appreciate it.

 

[CEH]

In the meantime...lots of problems here... take a look at some example DB's on Inventory.... note the 3 table relationship.

 

[shenty]

Where are the DB inventory things you mention

 

[shenty]

I have uploaded here the updated database - it still has the problem i am trying to get over with the AmountLeft & qtyLeft boxes not being in sync but i may give you more idea what the 'bigger' database is doing.

I am using Master/Child links between the Form & Subform instead of relationships. Is there anything wrong with doing this ?

I have also changed the Primary Key to be the MedicineID instead of the Medicine Name in tblMedicine, i don't know why this was wrong before really but it doesn't seem to have done any harm changing it.

 

[CEH]

Access has a sample inventory DB.... I just ran through the wizard to set this one up..... take a look.... three tables to look at closely... Inventory Transaction, Purchase Orders and Product. This should show you the setup...