Turn off Auto Calculation

[Alan]

Hi, I have a form (Access 2007) with a lot of calculations. Everytime I load
the form it starts to calculate all the values.

Is there a way to turn of this feature so taht I can calculate the values
manually with F9.

TIA

Alan

 

[Allen Browne]

There's not a setting for this.

You would need to save the text boxes as unbound (i.e. delete everything in
the Control Source property), and then assign the expressions
programmatically at the point you want them to start calculating, e.g.:
Me.Text0.ControlSource = "=DLookup(""MyField"", ""MyTable"")"

 

[Alan]

Hi Allen, thx for the quick response.

I hope that you can be patient with me cos I am just learning how to use
access.

Here is a typical entry from the ControlSource of one of my text boxes
(there are about 40 on the form).

=IIf(DLookUp("[NEWNET]","04a Summary","[DIVISION] = 'S' And [REP]='PH'") Is
Null,0,(DLookUp("[NEWNET]","04a Summary","[DIVISION] = 'S' And
[REP]='PH'")))

I am not sure how I would modify it with "Me.Text0.ControlSource ="

Any help would be appreciated

Alan

 

[Allen Browne]

It's exactly the same, except that the whole expression has to go inside
quotes.

Since the expression contains quote marks, you have to double those up.
Here's an explanation:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

Hopefully that makes sense of the example I posted originally.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen, thx for the quick response.

I hope that you can be patient with me cos I am just learning how to use
access.

Here is a typical entry from the ControlSource of one of my text boxes
(there are about 40 on the form).

=IIf(DLookUp("[NEWNET]","04a Summary","[DIVISION] = 'S' And [REP]='PH'")
Is Null,0,(DLookUp("[NEWNET]","04a Summary","[DIVISION] = 'S' And
[REP]='PH'")))

I am not sure how I would modify it with "Me.Text0.ControlSource ="

There's not a setting for this.

You would need to save the text boxes as unbound (i.e. delete everything
in the Control Source property), and then assign the expressions
programmatically at the point you want them to start calculating, e.g.:
Me.Text0.ControlSource = "=DLookup(""MyField"", ""MyTable"")"

 

[Alan]

Hi Again

I think I am getting in a muddle with my quotation marks

This is what I have done with my ControlSource:

Me.Text0.ControlSource = "=IIf(DLookUp(""[NEWNET]"",""04a
Summary"",""[DIVISION] = 'N' And [REP]='PH'"") Is
Null,0,(DLookUp(""[NEWNET]"",""04a Summary"",""[DIVISION] = 'N' And
[REP]='PH'"")))"

But I get an #Name? error when I run it.

It's exactly the same, except that the whole expression has to go inside
quotes.

Since the expression contains quote marks, you have to double those up.
Here's an explanation:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

Hopefully that makes sense of the example I posted originally.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen, thx for the quick response.

I hope that you can be patient with me cos I am just learning how to use
access.

Here is a typical entry from the ControlSource of one of my text boxes
(there are about 40 on the form).

=IIf(DLookUp("[NEWNET]","04a Summary","[DIVISION] = 'S' And [REP]='PH'")
Is Null,0,(DLookUp("[NEWNET]","04a Summary","[DIVISION] = 'S' And
[REP]='PH'")))

I am not sure how I would modify it with "Me.Text0.ControlSource ="

There's not a setting for this.

You would need to save the text boxes as unbound (i.e. delete everything
in the Control Source property), and then assign the expressions
programmatically at the point you want them to start calculating, e.g.:
Me.Text0.ControlSource = "=DLookup(""MyField"", ""MyTable"")"

Hi, I have a form (Access 2007) with a lot of calculations. Everytime I
load the form it starts to calculate all the values.

Is there a way to turn of this feature so taht I can calculate the
values manually with F9.

 

[Allen Browne]

To help you debug this, break it down into several lines, so you can ask
Access to tell you want's going on.

This kind of thing:
Dim strSource As String
strSource = "=Nz(DLookup(""[NewNet]"", ""[04a Summary]"", ""([Division]
= 'N') AND ([Rep] = 'PH')""), 0)"
Debug.Print strSource
Me.[SomeTextBoxNameHere].ControlSource = strSource

Now when it fails, open the Immediate Window (Ctrl+G), and look at the
expression that came out there. Replace the = with a question mark, and see
if it works in the immediate window.

Once you get the expression coming out correctly, the next task is to assign
the string to the ControlSource of your text box. Also, make sure the Name
of your text box is not the same as the name of any bound field.

If you have lots of these bound text boxes performing DLookup's, a more
efficient solution might be to use a bound form instead. That way, Access
can look up all the values in one go: much better than multiple DLookups
into the same data.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Again

I think I am getting in a muddle with my quotation marks

This is what I have done with my ControlSource:

Me.Text0.ControlSource = "=IIf(DLookUp(""[NEWNET]"",""04a
Summary"",""[DIVISION] = 'N' And [REP]='PH'"") Is
Null,0,(DLookUp(""[NEWNET]"",""04a Summary"",""[DIVISION] = 'N' And
[REP]='PH'"")))"

But I get an #Name? error when I run it.

It's exactly the same, except that the whole expression has to go inside
quotes.

Since the expression contains quote marks, you have to double those up.
Here's an explanation:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

Hopefully that makes sense of the example I posted originally.

Hi Allen, thx for the quick response.

I hope that you can be patient with me cos I am just learning how to use
access.

Here is a typical entry from the ControlSource of one of my text boxes
(there are about 40 on the form).

=IIf(DLookUp("[NEWNET]","04a Summary","[DIVISION] = 'S' And [REP]='PH'")
Is Null,0,(DLookUp("[NEWNET]","04a Summary","[DIVISION] = 'S' And
[REP]='PH'")))

I am not sure how I would modify it with "Me.Text0.ControlSource ="

There's not a setting for this.

You would need to save the text boxes as unbound (i.e. delete
everything in the Control Source property), and then assign the
expressions programmatically at the point you want them to start
calculating, e.g.:
Me.Text0.ControlSource = "=DLookup(""MyField"", ""MyTable"")"

Hi, I have a form (Access 2007) with a lot of calculations. Everytime
I load the form it starts to calculate all the values.

Is there a way to turn of this feature so taht I can calculate the
values manually with F9.

 

[Alan]

It has been sugested that I should attach all my formula's to their
respective fields via VB with an onClick button. Which I think is similar to
what you suggested.

I am working on that idea at the minute but I am still getting code errors.
Does the doubling up of quotation makes apply in VB too or should I just be
able to drop my ControlSource code strait into VB with a prefix of
me.Field1 (after renaming my field to Field1).

Alan

To help you debug this, break it down into several lines, so you can ask
Access to tell you want's going on.

This kind of thing:
Dim strSource As String
strSource = "=Nz(DLookup(""[NewNet]"", ""[04a Summary]"", ""([Division]
= 'N') AND ([Rep] = 'PH')""), 0)"
Debug.Print strSource
Me.[SomeTextBoxNameHere].ControlSource = strSource

Now when it fails, open the Immediate Window (Ctrl+G), and look at the
expression that came out there. Replace the = with a question mark, and
see if it works in the immediate window.

Once you get the expression coming out correctly, the next task is to
assign the string to the ControlSource of your text box. Also, make sure
the Name of your text box is not the same as the name of any bound field.

If you have lots of these bound text boxes performing DLookup's, a more
efficient solution might be to use a bound form instead. That way, Access
can look up all the values in one go: much better than multiple DLookups
into the same data.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Again

I think I am getting in a muddle with my quotation marks

This is what I have done with my ControlSource:

Me.Text0.ControlSource = "=IIf(DLookUp(""[NEWNET]"",""04a
Summary"",""[DIVISION] = 'N' And [REP]='PH'"") Is
Null,0,(DLookUp(""[NEWNET]"",""04a Summary"",""[DIVISION] = 'N' And
[REP]='PH'"")))"

But I get an #Name? error when I run it.

It's exactly the same, except that the whole expression has to go inside
quotes.

Since the expression contains quote marks, you have to double those up.
Here's an explanation:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

Hopefully that makes sense of the example I posted originally.

Hi Allen, thx for the quick response.

I hope that you can be patient with me cos I am just learning how to
use access.

Here is a typical entry from the ControlSource of one of my text boxes
(there are about 40 on the form).

=IIf(DLookUp("[NEWNET]","04a Summary","[DIVISION] = 'S' And
[REP]='PH'")
Is Null,0,(DLookUp("[NEWNET]","04a Summary","[DIVISION] = 'S' And
[REP]='PH'")))

I am not sure how I would modify it with "Me.Text0.ControlSource ="

There's not a setting for this.

You would need to save the text boxes as unbound (i.e. delete
everything in the Control Source property), and then assign the
expressions programmatically at the point you want them to start
calculating, e.g.:
Me.Text0.ControlSource = "=DLookup(""MyField"", ""MyTable"")"

Hi, I have a form (Access 2007) with a lot of calculations. Everytime
I load the form it starts to calculate all the values.

Is there a way to turn of this feature so taht I can calculate the
values manually with F9.

 

[Allen Browne]

The posted suggestion was for VBA

 

[Alan]

I am sorted now. Thx for your help

The reason that the code I had used in the form did not work when attached
to the button was because the 'Is Null' in the IIF statement had to be moved
to the begining instead on being,and changed to 'IsNull', at the end. Hope
that makes sense.

Thanks again

Alan