Avatar of bfuchs
bfuchs
Flag for United States of America asked on

Setting that's causing Access to crash

Hi Experts,

I'm trying to set a control source of a text box as follows and thats causing Access to crash, what can be the cause/solution?
=nz([TextTotalMoneyAvail],0)-(nz(([Housing].[column](2),0)*[WeeksOfContract])+nz([TravelTo],0)+nz([TravelFrom],0)+nz([OtherMalprPhysical],0)+nz([HealthInsurance],0))/117*100

Open in new window

Microsoft Access

Avatar of undefined
Last Comment
Jim Dettman (EE MVE)

8/22/2022 - Mon
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Try this:
 =(Nz([TextTotalMoneyAvail], 0) - (Nz([Housing].[Column](2), 0) * [WeeksOfContract]) + Nz([TravelTo], 0) + Nz([TravelFrom], 0) + Nz([OtherMalprPhysical], 0) + Nz([HealthInsurance], 0)) / 117 * 100

This compiles in VBA ... your post does not
bfuchs

ASKER
Hi Joe,

Now I get #Name? on that box.
fyi- it was working fine until I added the * [WeeksOfContract], now even when I remove that it crashes.
Thanks,
Ben
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Well #Name means .... Access cannot resolve one or more field names
By crashes now ... do you mean #Name ?
Or what do you mean by crashes ??
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Try changing this segment
from
 (Nz([Housing].[Column](2), 0) * [WeeksOfContract])

to

 ( Nz([Housing].[Column](2), 0) * Nz([WeeksOfContract],0) )
bfuchs

ASKER
that doesn't work either.

see attached what crashes means..

I will log off now, will resume tom.

Thanks,
Ben
untitled.png
SOLUTION
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
bfuchs

ASKER
@Joe,
I tried that and didn't help, not sure why you're suspecting something wrong with the app as everything else works fine, including this form, when I remove that property setting..?

@Jim
I have the latest Win/Office patches installed.
The below is whats showing under event viewer/application, is it of any help?

Faulting application name: MSACCESS.EXE, version: 11.0.8204.0, time stamp: 0x4763166b
Faulting module name: msjtes40.dll, version: 4.0.9756.0, time stamp: 0x49246e49
Exception code: 0xc0000005
Fault offset: 0x0000d4a0
Faulting process id: 0x5bfc
Faulting application start time: 0x01d2989041a0278e
Faulting application path: C:\Program Files (x86)\Microsoft Office\OFFICE11\MSACCESS.EXE
Faulting module path: C:\Windows\SysWOW64\msjtes40.dll
Report Id: 9d024e78-0504-11e7-afac-64006a53b03a

set the value in VBA in code in the OnCurrent event or an AfterUpdate event.  This will let you breakpoint the line and inspect values that are going into the calculation.
This happens to get me somewhere..see attached.

Now my question, in this case where only the first control has a value while the rest are null, can you see what is wrong with my code?

Thanks,
Ben
untitled.png
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
bfuchs

ASKER
Ah...check your data types....
Again you got this..see attached.
Now back to the main issue, what is preventing this from fitting into the ControlSource, as each text box should be a variant, no?

Thanks,
Ben
untitled.png
Jim Dettman (EE MVE)

<<should be a variant, no?>>

 Have no idea what Access does internally in a control.   A "variant" is something in VBA, not in Access.

 I just caught that initially you had dimmed "i" as an integer, so that's where the overflow came in.

 Try taking it as it stands now and pushing it into the control.

Jim.
bfuchs

ASKER
when I do MyTextBox=v it accepts.
However when I assign the whole calculation as ControlSource it gives me the attached error.
See immediate window.

Thanks,
Ben
Untitled1.png
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Jim Dettman (EE MVE)

As I said, I would not set the controlsource but leave it blank and push the value of the calculation into the control.   What I do is write one procedure to update all the calculated controls, then call that from OnCurrent and any AfterUpdates as needed (usually call it RefreshDisplay() ).

 I have always steered clear of letting Access figure out complex expressions.

 Also, that CCur() call was just an example.   I don't know if that's what you actually need on that field.

Jim.
bfuchs

ASKER
The problem is, this is a form with dozens of calculated controls, and I cannot do it just for this one as one depends on the other, its either I find the issue or will have to change all.
I have always steered clear of letting Access figure out complex expressions.
Besides for debugging, do you see other advantages from doing it by code?

Also, that CCur() call was just an example.
They're all defined in SQL as money, so that would not hurt.

Thanks,
Ben
bfuchs

ASKER
Hi Jim,

Just updating..
At the moment I was able to get this below working w/o crashing
=Nz([TextTotalMoneyAvail],0)-(CCur(Nz([Housing].[Column](2),0))*Nz([WeeksOfContract],0))+Nz([TravelTo],0)+Nz([TravelFrom],0)+Nz([OtherMalprPhysical],0)+Nz([HealthInsurance],0)/117*100

Open in new window

Any idea whats doing the trick?

Thanks,
Ben
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

"not sure why you're suspecting something wrong with the app as everything else works fine, including this form, when I remove that property setting..?
"
Well ..I've seen C&R and/or Decompile fix exactly those kinds of issues.
bfuchs

ASKER
At least its working now..Thank you my experts!
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

You are welcome :-)
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Jim Dettman (EE MVE)

Besides for debugging, do you see other advantages from doing it by code?

 Being able to do *anything* to get a value into a control.   But even if it was just the debugging piece, it would be worth doing IMHO.

 With calculations in controls, you can never be sure of what's going on and when you might have bad data.

 Depending on what I need, I may do the calc in a query.   But if it's anything beyond a few factors and basic math, or more than one or two calc's that I need, then I move it all to VBA code.  
 
  Overall, it's pretty rare that I do a calculated control in Access.

Jim.