Using subtotal pulled from subform in calculations on main form

[etmanage]

I have a subform that totals a subtotal in the form footer. It works fine. I then pull that value onto the main form into a textbox called Subtotal2 by setting the control source =OrderSubform.Form!Subtotal and it seems to work fine.

When I try to reference the Subtotal2 text box to perform calculations on it it doesn't work. For instance when in vba I set Me.tax = Me.Subtotal2 * TaxRate I get zero. I tried just saying Me.tax = Me.Subtotal2 to see if thats where the problem is and it is still zero. If I change it to Me.tax = 50 it comes out as 50 so I know the code is executing. Any ideas? The Subtotal2 box displays the correct subtotal but the vba code seems to think it is zero.

[etmanage]

Does anyone have any ideas? I'm at an impass until I figure this out and I've tried everything I can think of. I tried requeries, refreshes, afterupdates etc but nothing seems to work.

[NeoPa]

Nothing obvious springs to mind. May I suggest posting your database as an attachment. I rarely suggest this, but your problem is intriguing and I can only imagine determining the problem with direct, hands-on, access to the project.

When attaching your work please follow the following steps first :

1. Remove anything not relevant to the problem. This is not necessary in all circumstances but some databases can be very bulky and some things do not effect the actual problem at all.
2. Likewise, not entirely necessary in all cases, but consider saving your database in a version not later than 2003 as many of our experts don't use Access 2007. Largely they don't want to, but some also don't have access to it. Personally I will wait until I'm forced to before using it.
3. If you've done anything in steps 1 or 2 then make sure that the problem you're experiencing is still evident in the updated version.
4. Compact the database.
5. Compress the database into a ZIP file.
6. When posting, scroll down the page and select Manage Attachments (Pressing on that leads you to a page where you can add or remove your attachments. It also lists the maximum file sizes for each of the allowed file types.) and add this new ZIP file.

While I'm here I will just mention that bumping a thread before at least 24 hours has passed is considered impatient and not too well appreciated. I can see you're new so I won't worry about it now, but please remember if you intend to continue to use the service. A full list of our rules can be found under the Help link on every page.

Welcome to Bytes!

[etmanage]

Thank you for the reply. I deleted everything that wasn't relevant. The form in question is OrderForm. When you open it, at the top are some dropdowns for dept, item, size etc. Choose something to buy and press add item, it then is added to the transactions table and appears in the subform below. The subtotal appears under the subform in a textbox called Subtotal2 and updates correctly. In the VBA code, the relevant code is in the Private Sub AddButton_Click() section. After the addbutton is pressed, the subform is requeried and then I try setting Me.Total = Me.Subtotal2 which doesn't work. I simplified this by taking out the tax line, but the root problem is the same.
I also tried adding a Private Sub Subtotal2_AfterUpdate() section with the Me.Total = Me.Subtotal2 code but that doesn't work either.

I am using Access 2003

[NeoPa]

It looks like you've made a fine job of this. I'm impressed.

I can't start now to look at this though as time is getting on (01:27) and I need to wind up for the night. I'll keep it linked to look at again tomorrow though.

[etmanage]

Thank you for taking the time and for the compliment.

[NeoPa]

It complained of a Missing reference first time, but it was obviously 2007 and the reference to the Outlook library hadn't been updated. I worked it out though so I have it working now. Just looking at it so I should post again soon.

[NeoPa]

Ah. The control is never updated ;) The event is never triggered as the contents of the cell doesn't change as such. The value does, but the formula doesn't. Check out the Help system for the AfterUpdate event (from the help for the property click on the link to the event).

It explains that changes made by VBA do not trigger the event. It should really explain it such that only manual updates by the operator trigger the event.

I hope this clarifies the issue for you.

[etmanage]

I suspected the Subtotal2_AfterUpdate() wasn't triggering, but the AddButton_Click() event definately is and the Me.Total = Me.Subtotal2 code is there as well. It definately executes because when I substitute a value, such as 50 so that it reads Me.Total = 50 the Total textbox changes to 50. But when it is set to Me.Total = Me.Subtotal2 and the Subtotal2 textbox is 50 the Total textbox remains zero.


Edited typo please reread as it made no sense before I fixed it.

[etmanage]

Is it possible that the code is executing the Me.Total = Me.Subtotal2 before the subform has completed updating on the previous line? If so, is there a way to delay that line from executing a few milliseconds? I think this may be happening because when I add a second button that executes VBA code that sets Me.Total = Me.Subtotal2 and press that button after pressing the AddButton, it updates correctly, presumably after the subform has had enough time to requery. This is a combersome fix though, requiring the user to click two buttons to perform one task. I would much rather have a way to delay the execution of the Me.Total = Me.Subtotal2 line in the AddButton_Click() event code.

[etmanage]

I found how to delay the code from executing and delayed it .1 seconds and that fixed it. Thanks for helping out.

[NeoPa]

@etmanage
That all sounds very weird and unexpected. I overlooked the rest of your post earlier I'm afraid. I'll look more deeply into that. It may not be as strange as a delay that's needed.

[NeoPa]

It looks as you say like the .Requery method from the previous line returns control before all the associated items have been updated.

I would think waiting for a second would be too long though. I've played with this quite a bit and checked the Help system (can be quite useful although can also be frustrating at times) but it doesn't seem this is included anywhere. I wanted to find a way to check that the data was available before proceeding, but nothing I tried worked.

Eventually I thought the best way (if it doesn't have problems for you, unrelated to what we've discussed) would be to set the .ControlSource of [Total] to =[Subtotal2]. I hope this is some help. I admit to being stumped on finding a logical way for the assignment to work in VBA.