Link to home
Start Free TrialLog in
Avatar of bfuchs
bfuchsFlag 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

Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

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
Avatar of 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
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 ??
Try changing this segment
from
 (Nz([Housing].[Column](2), 0) * [WeeksOfContract])

to

 ( Nz([Housing].[Column](2), 0) * Nz([WeeksOfContract],0) )
Avatar of 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
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 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
<<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.
Avatar of 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
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.
Avatar of 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
Avatar of 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
"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.
Avatar of bfuchs

ASKER

At least its working now..Thank you my experts!
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.