iif statement after update problem

[motleyjew]

I am working on a bill of materials database. Once a record is entered, the current total appears. After the record has been updated, I would like to use a iif statement that determines what the tax should be. I already have my iif statement that is working. My problem is that the calculation takes a small amount of time to actually calculate. By the time the calculation is made, the iif statement has already run giving me false data. Just to test the iif statement out, i put it in a command button and it works every time i click it. Any advice on how i can run this iif statement after the calculation is made? I have also tried to put it in the On change event of the field that is calulating and it still won't work.

Thanks
Gregg

 

[boblarson]

Put it in the Form's BeforeUpdate event.

 

[motleyjew]

Sorry for the delayed responce. Thanks for responding so quickly. I have indeed tried the before update which gave me the same results. I have also tried running the calculation in the after update before my iif statement and that also did not work. As of now, my only solution is to leave the rule on a command button and have the user click on it to get the tax. I was wondering if anyone knew how to maybe delay the event somehow. Thanks again for your suggestion Bob. You have helped me plety of time on other posts.

Gregg

 

[boblarson]

Gregg:

It should work so if you can compact your database (Tools > Database Tools > Compact and Repair) and the zip it up and post it here (has to be under 393Kb) I can take a look as it's likely you are doing something slightly off which is causing it to not work.

And if you can't get it small enough, I can PM you with my email address so you could email it to me.

 

[motleyjew]

Here is an example of what I am working with. I put the rule that I am using on a label at the bottom of the page. The scenario in this case is that the operating unit is always going to be 1410. So, anything that is below $5000 should be 0.065% tax with 0% flat tax. Anything that is greater than $5000 is 0.06% tax with a $25 flat tax. Again, the problem i am running into is the totals take a split second to calculate.For example, if currently the total is $500 and I now entering in $6000 and hit enter to calculate, the rule is only seeing $500 in the total because it has not finished calculating. Thanks to Bob and anyone else who may be able to help out.

 

[boblarson]

I'm working on it but I haven't had as much time to get to it as I was hoping. If you solve it before I can get there then that is good, but I'll keep working on it.

First thing, though - I would HIGHLY suggest you name your controls something MEANINGFUL instead of going with Text13 and Text15 as it makes it harder to view and know which field it is. So, Text13 should really be something like txtTaxRate.

I'll have more for you later.

 

[boblarson]

Actually, when I renamed everything, it seemed to fix it. See what you think.

Edit:
I forgot to rename the code in the Form's AfterUpdate event (got the Open event but forgot the other), so you'll need to do that as you'll get an error if you don't.

 

[motleyjew]

Hey Bob,

I probably should have mentioned that I am pretty new to VBA, so please excuse my rookie mistakes. My boss pointed out the same naming convention to me, and I am starting to use it. Most of what you are seeing now is older code.

I tried the revised version and it appears to be doing the same thing. I also made your changes in the after update and still have the same problem.
I did come up with a quick work around to the problem. I created a pop up form that says "calculating tax..." and i am triggering it to open on the after update of the bill of materials. I then put my iif statement in the On close event of the pop up form and i am having it close on a time interval of 800 milliseconds. This works however I am still curious as to how to do this without creating another form. I may still work on it a bit and if I get the answer, I will share it with the board. Thanks for taking the time to look at my problem. If you happen to come across the answer, I'd love to hear it.

Gregg