Subform SUM problem

[shenty]

Please could someone help.

I am trying to get a main form field to match a calculated field if a certain tick box is unticked but i can't seem to do it.

Database is 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.

P.S. The reason there is a calculated field and a field that is written back to the table is so i can override the amount left with the 'empty' tickbox - this bit works a treat.

Any help would be great.

 

[boblarson]

Here you go. Check out the total text box I put on your main form. You will notice that it works even if you filter the subform (will return the filtered amount).

 

[shenty]

Am i missing something but the box you added is the same as the 2nd box down out the 4 on mine - its the 4th box that should be the same as the 3rd if the tickbox isn't ticked

 

[shenty]

A mate of mine had a look this afternoon and he too is stumped !

 

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

 

[boblarson]

I can see what you mean and I'm working on it, but not having too much luck either. There is a way, I'm just not getting there yet.

 

[shenty]

Fantastic - many thanks - i've tried quite a few things - i'm fairly newbie and not familiar with a lot of commands so maybe i'm missing something.

I still don't understand error trapping or on err routines yet either.

 

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

 

[Rabbie]

I think this is a timing problem. When the first quantity is entered the Event code updates box 4 before the other calculations have been done. So we need to find a different event to put the code on. The code is correct. It is just not being triggered at the right time. I will continue to look for the best place for it but I am a lttle busy at the mo. Just posted this in case if gives someone the right idea

 

[shenty]

Timing is 1 thing that did keep passing my mind but i disregarded it after a bit of investigation. I forget why now though. The strange thing is that when you go back in then out of the amount box it recalcs correctly.

 

[boblarson]

Timing is 1 thing that did keep passing my mind but i disregarded it after a bit of investigation. I forget why now though. The strange thing is that when you go back in then out of the amount box it recalcs correctly.

The strange thing also is that if you set a breakpoint and step through the code it will update it too. But using the same code without the breakpoint and it doesn't work. This one has me baffled and irritated too.

 

[shenty]

I'm glad in a way it's not just me that is baffled by this - if anyone else has an idea then feel free to offer your suggestions !

 

[shenty]

Bob, i think i know how to set a breakpoint but how do ya step through the code.

I have set a breakpoint on the line of code in question and notice the value of AmountLeft is wrong - if i knew how to step through, would it show me that value change to correct itself ?

 

[boblarson]

Stepping through after a breakpoint is F8

 

[Rabbie]

The strange thing also is that if you set a breakpoint and step through the code it will update it too. But using the same code without the breakpoint and it doesn't work. This one has me baffled and irritated too.

This what leads me to believe it is all to do with timing. Stopping at the breakpoint give the qty_left box time to update to Qty_used is updated correctly. I think it take time for the subform change to update the main form.

Having run some realtime diagnostics it seems that qty_left has defintely not been updated when this event is triggered the first time so thats why the box s not in sync. Now we just need to find the right place to insert the code

 

[Rabbie]

At last I have a solution of sorts! A bit of a bodge but it seems to do the job.

In the lostfocus event for the Amount field on the subform I put the following code

Private Sub Amount_LostFocus()
'Save the record to update the calculated qtyLeft field in main form
    DoCmd.RunCommand acCmdSaveRecord
   If Forms!frmMedicineAdministered!qtyLeft = 0 Then
   End If
    'Set the actual stock level to the calculated amount left
    
    If Forms!frmMedicineAdministered!Empty = False Then
    
    Forms!frmMedicineAdministered!AmountLeft = Forms!frmMedicineAdministered!qtyLeft
    
    End If
    Me.TimerInterval = 1000
    
End Sub

and in the timer event for the subform I put this code

Private Sub Form_Timer()
'Save the record to update the calculated qtyLeft field in main form
    DoCmd.RunCommand acCmdSaveRecord
   If Forms!frmMedicineAdministered!qtyLeft = 0 Then
   End If
    'Set the actual stock level to the calculated amount left
    
    If Forms!frmMedicineAdministered!Empty = False Then
    
        Forms!frmMedicineAdministered!AmountLeft = Forms!frmMedicineAdministered!qtyLeft
    
    End If
    Me.TimerInterval = 0
End Sub

Hope this helps even if it isnt the most elegant solution

 

[shenty]

That may actually sound logical. I wonder whether its doing my calculation whilst the command is running to save the record .!!!!

Could that happen ?

Is there a way to put a slight pause between the 2 commands ?

 

[Rabbie]

Is there a way to put a slight pause between the 2 commands ?

Thats what I was doing by changing Timer event. The one in the lost focus event asks for a timer event in 1 seconds time. The one in the form timer event turns off the timer and recalculates the field.

As I said -Not the most elegant but it seems to work!

 

[shenty]

Thanks - i will incorporate into db and let you know - before i do (it is 1st thing monday morning) but what is this doing :

If Forms!frmMedicineAdministered!qtyLeft = 0 Then
End If

??

 

[Rabbie]

Thanks - i will incorporate into db and let you know - before i do (it is 1st thing monday morning) but what is this doing :

If Forms!frmMedicineAdministered!qtyLeft = 0 Then
End If

??

It's a remenant of something else I was trying and you can safely remove it.

It's the price of taking advice from an old man