We help IT Professionals succeed at work.

Setting that's causing Access to crash

bfuchs
bfuchs asked
on
218 Views
Last Modified: 2017-03-10
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

Comment
Watch Question

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
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
CERTIFIED EXPERT

Author

Commented:
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 - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
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 ??
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

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

to

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

Author

Commented:
that doesn't work either.

see attached what crashes means..

I will log off now, will resume tom.

Thanks,
Ben
untitled.png
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
CERTIFIED EXPERT

Author

Commented:
@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
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
CERTIFIED EXPERT

Author

Commented:
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)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<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.
CERTIFIED EXPERT

Author

Commented:
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
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
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.
CERTIFIED EXPERT

Author

Commented:
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
CERTIFIED EXPERT

Author

Commented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
"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.
CERTIFIED EXPERT

Author

Commented:
At least its working now..Thank you my experts!
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
You are welcome :-)
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
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.