New #Error!

[dmyoungsal]

I needed to add a new calculation to my form that would break down a per equipment amount (after I got the total Load amount). The need comes from the fact that if a piece of equipment is shipped to a different Zone (to a different customer), that charge would not be simply a 1/4, 13 or 1/2 of the total load amount.

I added a new text box and entered the following formula:
=if([txtQty=0,"",Round((([txtmiles1]/[txtAvgMph1]+[txtCount1],2))*[txtTruckHrRate])*[txtProfit%])

The formula is saved and when I open the form, the DLOOKUP fields in the upper part of the form are flashing and I see "Calculating" in the lower left of the form. Nothing has changed in any other part of the form.

The problem stops when I delete the new textbox.

Why is this happening?

 

[RuralGuy]

Put your calculation in the OnCurrent event of the form instead.
YourTextBox = (your formula)

 

[dmyoungsal]

I am not sure how this would be of benefit. In the end, I will need 4 of these calculations performed in separate text boxes to handle all the possibilities of equipment being delivered to 4 different zones off the same load.

This text box is just the first that I need. I need to figure out the cause.

 

[dmyoungsal]

I re-entered the formula:
=iif(([txtQty]=0,"",Round((([txtMiles1]/[txtAvgMPH1])+[txtCount1],2))*[txtTruckHrRate])*[txtProfit%])

Saved it.
Opened the form (no errors)
Entered 1 in [txtCount1] and entered 1 is Zone1 and as soon as I clicked away from the field, the error "MS Access has stopped working" (this is the same error a that has been showing each time I try running this new textbox.

I close Access and re-open the form and all calculation textboxes are showing #Error (the DLOOKUPs) are all flashing. When I delete the textbox and reopen the form, there are no errors and the form acts normally.

++++++

 

[dmyoungsal]

OK, I tried the suggestion of putting the formula into an Event (I chose - gotFocus so I would have to click in the box to make it run)

and after entering the QTY and Zone, all my calculations worked like they should. I then clicked into the new text box and all of a sudden the error "MS Access has stopped working".

What would be causing this?

 

[RuralGuy]

What version of Access?

 

[dmyoungsal]

Access 2013

 

[RuralGuy]

I don't know what would cause your issue but if you want to Compact and Repair and zip up your db and post it, we could look at it. Remove any sensitive data of course but leave enough to demonstrate the issue. Test the db for failure before posting.

 

[dmyoungsal]

I uploaded the sample DB that I exported my stuff to.

I is a sample DB from MS, but if you expand the forms section and select "frmTranCalcTruck". Enter a number into the first QTY field and select 1 from the Zone (Miles). This should be enough to cause the program to fail.

 

[RuralGuy]

I'm looking at it but I have to admit you have more confidence in MS Access sequencing than I. You have referenced other controls that have referenced yet other controls in the hopes that MS Access will resolve all of these references in the right order so your calculations come out right. It could be that Excel might get these right but I'm not so sure about MS Access.

 

[dmyoungsal]

i figured out the cause of the problem.

It is "*[txtTruckHrRate])*[txtProfit%])".

I removed these two and the textbox displayed correctly. I added the first multiplier in and Access crashed. I removed the Hr Rate and left the Profit% in and it crashed.

I have an idea.....

 

[dmyoungsal]

that did not work.....

 

[RuralGuy]

It looks like all of your controls are either unbound or contain Domain functions. Nothing is taking advantage of the fact that it is in a RDBMS. Is your backgroung Excel?

 

[dmyoungsal]

no training in Access....

I have build databases, but work in the Wizard world.

I know it doesn't scratch the surface of the power of Access, but I have a better comfort level of that. So, yes, I have a stronger comfort level working with excel formulas

 

[RuralGuy]

Because of the way spreadsheets work, a lot of effort went into checking for circular references and dependencies. A different team worked on Access and it is not as thorough in that area. What you want to achieve can be done but you may need to drop into VBA code to get it to work right.