Updating Saved Calculated Value

[Guest]

I am having trouble updating a date value on a building permit form
(frmBPData) when the number of entries on a related subform (sfrmBPPayments)
is greater than 4. The building permit form has a field that I enter
'BPIssueDate' and calculated value 'DateExpire'. The subform, which permits
buying building permit extensions, has a field "BPMonths" indicating the
incremental months to be added to the building permit. A field on the
subform 'BPTotalMonths' sums up the 'BPMonths'.
There is a field on the main form, which calculates a 'BPExpire' date, using
the DateAdd function and "m" referring to 'BPTotalMosnths' and a base date of
'BPIssueDate'. I use some VBA code to write the 'BPExpire' value to a field
'BPExpire' in the table that underlies the building permit form.

Yes I have heard that calculated values should not be stored, but other
parts of the application are easier to program with the stored 'BPExpires'
field.

Everything works great as long as there are four or fewer entries on the
subform. Both 'BPExpire' & 'BPExpires' calculate & record correctly. As
soon as I enter a fifth entry on the subform, 'BPExpire' calculates OK but
the stored value of 'BPExpires' reverts back to the 'BPIssueDate'. I have
over 1000 building permit forms and only four of them have five or more
subform entries. The only building permit forms that don't work correctly
are these four.

Does anybody have an idea why the program works with four subform entries
and fails with five subform entries?

 

[Jeff Boyce]

You've described all the calculating you are doing, but haven't shown us the
expressions you are using. If you want specific suggestions about how your
calculations are working, provide some specific information.

And if you know that storing a calculated value can cause you issues of
synchronization, why not use a query to do the calculation, rather than your
form?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

You've described all the calculating you are doing, but haven't shown us the
expressions you are using. If you want specific suggestions about how your
calculations are working, provide some specific information.

And if you know that storing a calculated value can cause you issues of
synchronization, why not use a query to do the calculation, rather than your
form?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Jeff

I would be happy to write a field in a query to calculate BPExpire but so
far all my efforts have failed. What I have is:

frmBPData - Record Source: qryBPdata that includes tblBPData
Text Box Name: BPIssueDate
ControlSource: BPIssueDate
Text Box Name: BPExpire
Control Source:
=IIf(IsDate([BPIssueDate]),DateAdd("m",Nz([sfrmBPPayments].[Form]!
[BPTotalMonths],0),[BPIssueDate])," ")

sfrBPPayments - Record Source: tblBPPayments
Text Box Name: BPMonths
ControlSource: BPMonths
Text Box Name: BPTotalMonths
ControlSource: =Sum([BPMonths])

BPExpire calculates OK and shows up in its text box for any and all subform
records. But I can't reference BPExpire in other reports, queries or forms.

I have tied to write a field BPExpires:>>>>>> in qryBPData based on the
control source for BPExpire shown above but ave been unsuccessful. (Yes
Expires instead of Expire) Any criteria I do write that doesn't result in
an Error of Name violation doesn't show in text box BPExpires on frmBPData.

Paddler

 

[Jeff Boyce]

How are you referring to that calculated control?

Do you have the form in which it lives open at the time you are referring to
it?

Is the control bound or unbound?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

You've described all the calculating you are doing, but haven't shown us
the
expressions you are using. If you want specific suggestions about how
your
calculations are working, provide some specific information.

And if you know that storing a calculated value can cause you issues of
synchronization, why not use a query to do the calculation, rather than
your
form?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Jeff

I would be happy to write a field in a query to calculate BPExpire but so
far all my efforts have failed. What I have is:

frmBPData - Record Source: qryBPdata that includes tblBPData
Text Box Name: BPIssueDate
ControlSource: BPIssueDate
Text Box Name: BPExpire
Control Source:
=IIf(IsDate([BPIssueDate]),DateAdd("m",Nz([sfrmBPPayments].[Form]!
[BPTotalMonths],0),[BPIssueDate])," ")

sfrBPPayments - Record Source: tblBPPayments
Text Box Name: BPMonths
ControlSource: BPMonths
Text Box Name: BPTotalMonths
ControlSource: =Sum([BPMonths])

BPExpire calculates OK and shows up in its text box for any and all
subform
records. But I can't reference BPExpire in other reports, queries or
forms.

I have tied to write a field BPExpires:>>>>>> in qryBPData based on the
control source for BPExpire shown above but ave been unsuccessful. (Yes
Expires instead of Expire) Any criteria I do write that doesn't result
in
an Error of Name violation doesn't show in text box BPExpires on
frmBPData.

Paddler

 

[Guest]

Jeff

I have a form frmBPdata open and it has a subform sfrmBPPayments open on the
main form.

My main form has two text boxes
Textbox 1 has as its Name: BPExpire and as its Control Source
=IIf(IsDate([BPIssueDate]),DateAdd("m",Nz([sfrmBPPayments].[Form]![BPTotalMonths],0),[BPIssueDate])," ")
This is a bound control and the equation works and displays correctly every
time I add a record in sfrmBPPayments to add more BPMonths to the building
permit.
This is the equation I discussed in my previous post. Now if I want to
write a report listing expired building permits, I do not know how to
reference my calculated control BPExpire on frmBPData.

I assume the way around this problem is to calculate BPExpire in a field on
the query qryBPData which underlies frmBPData. So I change the name to
BPExpires and write the field equation as:
BPExpires:
IIf(IsDate([BPIssueDate]),DateAdd("m",[Forms].[frmBPData].[sfrmBPPayments].
[Form]![BPTotalMonths],[BPIssueDate])," ")

For developent purposes, I have a second text field on frmBPData. It has a
Name of BPExpires and a Control Source of BPExpires; this is a bound control.
I can observe both BPExpire & BPExpires when I add BPMonths to
sfrmBPPayments.

The open text box BPExpires is now registering #Name? because something is
wrong with the BPExpires: equation probably my Form & Subform reference to
BPTotalMonths. If the equation is corrected and BPExpires is the correct
date I will be OK. Now since BPExpires is in a query, I can reference it
when I write the query for a report listing expired building permits.

I hope you can held me correct the BPExpires: equation. This would
certainly be a better solution than writing the calculated value of BPExpire
into tblBPData as BPExpires.

If you need to return to my original question as to why I can't write a
fifth change in the building permit RBExpire date to tblBPData, I could send
the VBA code. But it is rather long because it involves Allen Browne's
'Return to the same record next time form is opened' VBA code for uloading
and loading a form.

Paddler

How are you referring to that calculated control?

Do you have the form in which it lives open at the time you are referring to
it?

Is the control bound or unbound?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

You've described all the calculating you are doing, but haven't shown us
the
expressions you are using. If you want specific suggestions about how
your
calculations are working, provide some specific information.

And if you know that storing a calculated value can cause you issues of
synchronization, why not use a query to do the calculation, rather than
your
form?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am having trouble updating a date value on a building permit form
(frmBPData) when the number of entries on a related subform
(sfrmBPPayments)
is greater than 4. The building permit form has a field that I enter
'BPIssueDate' and calculated value 'DateExpire'. The subform, which
permits
buying building permit extensions, has a field "BPMonths" indicating
the
incremental months to be added to the building permit. A field on the
subform 'BPTotalMonths' sums up the 'BPMonths'.
There is a field on the main form, which calculates a 'BPExpire' date,
using
the DateAdd function and "m" referring to 'BPTotalMosnths' and a base
date
of
'BPIssueDate'. I use some VBA code to write the 'BPExpire' value to a
field
'BPExpire' in the table that underlies the building permit form.

Yes I have heard that calculated values should not be stored, but other
parts of the application are easier to program with the stored
'BPExpires'
field.

Everything works great as long as there are four or fewer entries on
the
subform. Both 'BPExpire' & 'BPExpires' calculate & record correctly.
As
soon as I enter a fifth entry on the subform, 'BPExpire' calculates OK
but
the stored value of 'BPExpires' reverts back to the 'BPIssueDate'. I
have
over 1000 building permit forms and only four of them have five or
more
subform entries. The only building permit forms that don't work
correctly
are these four.

Does anybody have an idea why the program works with four subform
entries
and fails with five subform entries?




Jeff

I would be happy to write a field in a query to calculate BPExpire but so
far all my efforts have failed. What I have is:

frmBPData - Record Source: qryBPdata that includes tblBPData
Text Box Name: BPIssueDate
ControlSource: BPIssueDate
Text Box Name: BPExpire
Control Source:
=IIf(IsDate([BPIssueDate]),DateAdd("m",Nz([sfrmBPPayments].[Form]!
[BPTotalMonths],0),[BPIssueDate])," ")

sfrBPPayments - Record Source: tblBPPayments
Text Box Name: BPMonths
ControlSource: BPMonths
Text Box Name: BPTotalMonths
ControlSource: =Sum([BPMonths])

BPExpire calculates OK and shows up in its text box for any and all
subform
records. But I can't reference BPExpire in other reports, queries or
forms.

I have tied to write a field BPExpires:>>>>>> in qryBPData based on the
control source for BPExpire shown above but ave been unsuccessful. (Yes
Expires instead of Expire) Any criteria I do write that doesn't result
in
an Error of Name violation doesn't show in text box BPExpires on
frmBPData.

Paddler

 

[Jeff Boyce]

I get the #Name when Access can't find something the way I named it. This
usually happens if I've typed in the control source, rather than selecting
it from a drop-down or copying/pasting.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff

I have a form frmBPdata open and it has a subform sfrmBPPayments open on
the
main form.

My main form has two text boxes
Textbox 1 has as its Name: BPExpire and as its Control Source
=IIf(IsDate([BPIssueDate]),DateAdd("m",Nz([sfrmBPPayments].[Form]![BPTotalMonths],0),[BPIssueDate]),"
")
This is a bound control and the equation works and displays correctly
every
time I add a record in sfrmBPPayments to add more BPMonths to the building
permit.
This is the equation I discussed in my previous post. Now if I want to
write a report listing expired building permits, I do not know how to
reference my calculated control BPExpire on frmBPData.

I assume the way around this problem is to calculate BPExpire in a field
on
the query qryBPData which underlies frmBPData. So I change the name to
BPExpires and write the field equation as:
BPExpires:
IIf(IsDate([BPIssueDate]),DateAdd("m",[Forms].[frmBPData].[sfrmBPPayments].
[Form]![BPTotalMonths],[BPIssueDate])," ")

For developent purposes, I have a second text field on frmBPData. It has
a
Name of BPExpires and a Control Source of BPExpires; this is a bound
control.
I can observe both BPExpire & BPExpires when I add BPMonths to
sfrmBPPayments.

The open text box BPExpires is now registering #Name? because something is
wrong with the BPExpires: equation probably my Form & Subform reference to
BPTotalMonths. If the equation is corrected and BPExpires is the correct
date I will be OK. Now since BPExpires is in a query, I can reference it
when I write the query for a report listing expired building permits.

I hope you can held me correct the BPExpires: equation. This would
certainly be a better solution than writing the calculated value of
BPExpire
into tblBPData as BPExpires.

If you need to return to my original question as to why I can't write a
fifth change in the building permit RBExpire date to tblBPData, I could
send
the VBA code. But it is rather long because it involves Allen Browne's
'Return to the same record next time form is opened' VBA code for uloading
and loading a form.

Paddler

How are you referring to that calculated control?

Do you have the form in which it lives open at the time you are referring
to
it?

Is the control bound or unbound?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

:

You've described all the calculating you are doing, but haven't shown
us
the
expressions you are using. If you want specific suggestions about how
your
calculations are working, provide some specific information.

And if you know that storing a calculated value can cause you issues
of
synchronization, why not use a query to do the calculation, rather
than
your
form?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am having trouble updating a date value on a building permit form
(frmBPData) when the number of entries on a related subform
(sfrmBPPayments)
is greater than 4. The building permit form has a field that I
enter
'BPIssueDate' and calculated value 'DateExpire'. The subform, which
permits
buying building permit extensions, has a field "BPMonths" indicating
the
incremental months to be added to the building permit. A field on
the
subform 'BPTotalMonths' sums up the 'BPMonths'.
There is a field on the main form, which calculates a 'BPExpire'
date,
using
the DateAdd function and "m" referring to 'BPTotalMosnths' and a
base
date
of
'BPIssueDate'. I use some VBA code to write the 'BPExpire' value
to a
field
'BPExpire' in the table that underlies the building permit form.

Yes I have heard that calculated values should not be stored, but
other
parts of the application are easier to program with the stored
'BPExpires'
field.

Everything works great as long as there are four or fewer entries on
the
subform. Both 'BPExpire' & 'BPExpires' calculate & record
correctly.
As
soon as I enter a fifth entry on the subform, 'BPExpire' calculates
OK
but
the stored value of 'BPExpires' reverts back to the 'BPIssueDate'.
I
have
over 1000 building permit forms and only four of them have five or
more
subform entries. The only building permit forms that don't work
correctly
are these four.

Does anybody have an idea why the program works with four subform
entries
and fails with five subform entries?




Jeff

I would be happy to write a field in a query to calculate BPExpire but
so
far all my efforts have failed. What I have is:

frmBPData - Record Source: qryBPdata that includes tblBPData
Text Box Name: BPIssueDate
ControlSource: BPIssueDate
Text Box Name: BPExpire
Control Source:
=IIf(IsDate([BPIssueDate]),DateAdd("m",Nz([sfrmBPPayments].[Form]!
[BPTotalMonths],0),[BPIssueDate])," ")

sfrBPPayments - Record Source: tblBPPayments
Text Box Name: BPMonths
ControlSource: BPMonths
Text Box Name: BPTotalMonths
ControlSource: =Sum([BPMonths])

BPExpire calculates OK and shows up in its text box for any and all
subform
records. But I can't reference BPExpire in other reports, queries or
forms.

I have tied to write a field BPExpires:>>>>>> in qryBPData based on the
control source for BPExpire shown above but ave been unsuccessful. (Yes
Expires instead of Expire) Any criteria I do write that doesn't
result
in
an Error of Name violation doesn't show in text box BPExpires on
frmBPData.

Paddler

 

[Guest]

Jeff
I want to refer to BPExpire (a date) so I can find and print a list of
Expired Building Permits. I have a calculated control on frmBPData that
calculates BPExpire.
=IIf(IsDate([BPIssueDate]),DateAdd("m",Nz([sfrmBPPayments].[Form]!
[BPTotalMonths],0),[BPIssueDate])," ")
How do I reference BPExpire so I can generate this report?

If I can'r refer to the calculated control BPExpire, what are my options to
get an expiration date?
a. Save the calculated value BPExpire in a table tblBPData.
b. Calculate a field BPExpires in a Query so it can be referenced.
c. please define
I am having trouble doing b. above. I copied the calculated control to a
field on qryBPData. I can't open the query in datasheet view because the
query cant find parameter sfrmBPPayments.Form!BPTotalMonths.

The rules say to refer to a field using the following syntax
Forms."Form Name".subform Name.form."controlName"

Apparently I have the last three terms correct because calculated control
BPExpire works and I copied and pasted it into BPExpires:. Therefore it must
be the first two terms that are incorrect. The first term is simply Forms.
That leaves the second term and how could I mistype frmBPData. So why
doesn't the following work? What a I doing wrong? What is the correct
syntax to use?.
BPExpires:
IIf(IsDate([BPIssueDate]),DateAdd("m",[Forms].[frmBPData].[sfrmBPPayments].
[Form]![BPTotalMonths],[BPIssueDate])," ")
What are your syntax rules for writing a calculated field in a query?

If this cant be done, I have no choice but to return to the problem of
saving the Calculated value BPExpire in tblBPData. Do you want the VBA code
for saving calculated value BPExpire to investigate why it stops functioning
when the fifth Building Permint etension is entered on sfrmBPPayments?

Paddler

I get the #Name when Access can't find something the way I named it. This
usually happens if I've typed in the control source, rather than selecting
it from a drop-down or copying/pasting.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff

I have a form frmBPdata open and it has a subform sfrmBPPayments open on
the
main form.

My main form has two text boxes
Textbox 1 has as its Name: BPExpire and as its Control Source
=IIf(IsDate([BPIssueDate]),DateAdd("m",Nz([sfrmBPPayments].[Form]![BPTotalMonths],0),[BPIssueDate]),"
")
This is a bound control and the equation works and displays correctly
every
time I add a record in sfrmBPPayments to add more BPMonths to the building
permit.
This is the equation I discussed in my previous post. Now if I want to
write a report listing expired building permits, I do not know how to
reference my calculated control BPExpire on frmBPData.

I assume the way around this problem is to calculate BPExpire in a field
on
the query qryBPData which underlies frmBPData. So I change the name to
BPExpires and write the field equation as:
BPExpires:
IIf(IsDate([BPIssueDate]),DateAdd("m",[Forms].[frmBPData].[sfrmBPPayments].
[Form]![BPTotalMonths],[BPIssueDate])," ")

For developent purposes, I have a second text field on frmBPData. It has
a
Name of BPExpires and a Control Source of BPExpires; this is a bound
control.
I can observe both BPExpire & BPExpires when I add BPMonths to
sfrmBPPayments.

The open text box BPExpires is now registering #Name? because something is
wrong with the BPExpires: equation probably my Form & Subform reference to
BPTotalMonths. If the equation is corrected and BPExpires is the correct
date I will be OK. Now since BPExpires is in a query, I can reference it
when I write the query for a report listing expired building permits.

I hope you can held me correct the BPExpires: equation. This would
certainly be a better solution than writing the calculated value of
BPExpire
into tblBPData as BPExpires.

If you need to return to my original question as to why I can't write a
fifth change in the building permit RBExpire date to tblBPData, I could
send
the VBA code. But it is rather long because it involves Allen Browne's
'Return to the same record next time form is opened' VBA code for uloading
and loading a form.

Paddler

How are you referring to that calculated control?

Do you have the form in which it lives open at the time you are referring
to
it?

Is the control bound or unbound?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP



:

You've described all the calculating you are doing, but haven't shown
us
the
expressions you are using. If you want specific suggestions about how
your
calculations are working, provide some specific information.

And if you know that storing a calculated value can cause you issues
of
synchronization, why not use a query to do the calculation, rather
than
your
form?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am having trouble updating a date value on a building permit form
(frmBPData) when the number of entries on a related subform
(sfrmBPPayments)
is greater than 4. The building permit form has a field that I
enter
'BPIssueDate' and calculated value 'DateExpire'. The subform, which
permits
buying building permit extensions, has a field "BPMonths" indicating
the
incremental months to be added to the building permit. A field on
the
subform 'BPTotalMonths' sums up the 'BPMonths'.
There is a field on the main form, which calculates a 'BPExpire'
date,
using
the DateAdd function and "m" referring to 'BPTotalMosnths' and a
base
date
of
'BPIssueDate'. I use some VBA code to write the 'BPExpire' value
to a
field
'BPExpire' in the table that underlies the building permit form.

Yes I have heard that calculated values should not be stored, but
other
parts of the application are easier to program with the stored
'BPExpires'
field.

Everything works great as long as there are four or fewer entries on
the
subform. Both 'BPExpire' & 'BPExpires' calculate & record
correctly.
As
soon as I enter a fifth entry on the subform, 'BPExpire' calculates
OK
but
the stored value of 'BPExpires' reverts back to the 'BPIssueDate'.
I
have
over 1000 building permit forms and only four of them have five or
more
subform entries. The only building permit forms that don't work
correctly
are these four.

Does anybody have an idea why the program works with four subform
entries
and fails with five subform entries?




Jeff

I would be happy to write a field in a query to calculate BPExpire but
so
far all my efforts have failed. What I have is:

frmBPData - Record Source: qryBPdata that includes tblBPData
Text Box Name: BPIssueDate
ControlSource: BPIssueDate
Text Box Name: BPExpire
Control Source:
=IIf(IsDate([BPIssueDate]),DateAdd("m",Nz([sfrmBPPayments].[Form]!
[BPTotalMonths],0),[BPIssueDate])," ")

sfrBPPayments - Record Source: tblBPPayments
Text Box Name: BPMonths
ControlSource: BPMonths
Text Box Name: BPTotalMonths
ControlSource: =Sum([BPMonths])

BPExpire calculates OK and shows up in its text box for any and all
subform
records. But I can't reference BPExpire in other reports, queries or
forms.

I have tied to write a field BPExpires:>>>>>> in qryBPData based on the
control source for BPExpire shown above but ave been unsuccessful. (Yes
Expires instead of Expire) Any criteria I do write that doesn't
result
in
an Error of Name violation doesn't show in text box BPExpires on
frmBPData.

Paddler

 

[Jeff Boyce]

=Forms!YourFormName!YourControlName

Use this in your report, as the control source for a control in which you
want to see this date.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff
I want to refer to BPExpire (a date) so I can find and print a list of
Expired Building Permits. I have a calculated control on frmBPData that
calculates BPExpire.
=IIf(IsDate([BPIssueDate]),DateAdd("m",Nz([sfrmBPPayments].[Form]!
[BPTotalMonths],0),[BPIssueDate])," ")
How do I reference BPExpire so I can generate this report?

If I can'r refer to the calculated control BPExpire, what are my options
to
get an expiration date?
a. Save the calculated value BPExpire in a table tblBPData.
b. Calculate a field BPExpires in a Query so it can be referenced.
c. please define
I am having trouble doing b. above. I copied the calculated control to a
field on qryBPData. I can't open the query in datasheet view because the
query cant find parameter sfrmBPPayments.Form!BPTotalMonths.

The rules say to refer to a field using the following syntax
Forms."Form Name".subform Name.form."controlName"

Apparently I have the last three terms correct because calculated control
BPExpire works and I copied and pasted it into BPExpires:. Therefore it
must
be the first two terms that are incorrect. The first term is simply
Forms.
That leaves the second term and how could I mistype frmBPData. So why
doesn't the following work? What a I doing wrong? What is the correct
syntax to use?.
BPExpires:
IIf(IsDate([BPIssueDate]),DateAdd("m",[Forms].[frmBPData].[sfrmBPPayments].
[Form]![BPTotalMonths],[BPIssueDate])," ")
What are your syntax rules for writing a calculated field in a query?

If this cant be done, I have no choice but to return to the problem of
saving the Calculated value BPExpire in tblBPData. Do you want the VBA
code
for saving calculated value BPExpire to investigate why it stops
functioning
when the fifth Building Permint etension is entered on sfrmBPPayments?

Paddler

I get the #Name when Access can't find something the way I named it.
This
usually happens if I've typed in the control source, rather than
selecting
it from a drop-down or copying/pasting.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff

I have a form frmBPdata open and it has a subform sfrmBPPayments open
on
the
main form.

My main form has two text boxes
Textbox 1 has as its Name: BPExpire and as its Control Source
=IIf(IsDate([BPIssueDate]),DateAdd("m",Nz([sfrmBPPayments].[Form]![BPTotalMonths],0),[BPIssueDate]),"
")
This is a bound control and the equation works and displays correctly
every
time I add a record in sfrmBPPayments to add more BPMonths to the
building
permit.
This is the equation I discussed in my previous post. Now if I want to
write a report listing expired building permits, I do not know how to
reference my calculated control BPExpire on frmBPData.

I assume the way around this problem is to calculate BPExpire in a
field
on
the query qryBPData which underlies frmBPData. So I change the name to
BPExpires and write the field equation as:
BPExpires:
IIf(IsDate([BPIssueDate]),DateAdd("m",[Forms].[frmBPData].[sfrmBPPayments].
[Form]![BPTotalMonths],[BPIssueDate])," ")

For developent purposes, I have a second text field on frmBPData. It
has
a
Name of BPExpires and a Control Source of BPExpires; this is a bound
control.
I can observe both BPExpire & BPExpires when I add BPMonths to
sfrmBPPayments.

The open text box BPExpires is now registering #Name? because something
is
wrong with the BPExpires: equation probably my Form & Subform reference
to
BPTotalMonths. If the equation is corrected and BPExpires is the
correct
date I will be OK. Now since BPExpires is in a query, I can reference
it
when I write the query for a report listing expired building permits.

I hope you can held me correct the BPExpires: equation. This would
certainly be a better solution than writing the calculated value of
BPExpire
into tblBPData as BPExpires.

If you need to return to my original question as to why I can't write a
fifth change in the building permit RBExpire date to tblBPData, I could
send
the VBA code. But it is rather long because it involves Allen Browne's
'Return to the same record next time form is opened' VBA code for
uloading
and loading a form.

Paddler




:

How are you referring to that calculated control?

Do you have the form in which it lives open at the time you are
referring
to
it?

Is the control bound or unbound?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP



:

You've described all the calculating you are doing, but haven't
shown
us
the
expressions you are using. If you want specific suggestions about
how
your
calculations are working, provide some specific information.

And if you know that storing a calculated value can cause you
issues
of
synchronization, why not use a query to do the calculation, rather
than
your
form?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am having trouble updating a date value on a building permit
form
(frmBPData) when the number of entries on a related subform
(sfrmBPPayments)
is greater than 4. The building permit form has a field that I
enter
'BPIssueDate' and calculated value 'DateExpire'. The subform,
which
permits
buying building permit extensions, has a field "BPMonths"
indicating
the
incremental months to be added to the building permit. A field
on
the
subform 'BPTotalMonths' sums up the 'BPMonths'.
There is a field on the main form, which calculates a 'BPExpire'
date,
using
the DateAdd function and "m" referring to 'BPTotalMosnths' and a
base
date
of
'BPIssueDate'. I use some VBA code to write the 'BPExpire'
value
to a
field
'BPExpire' in the table that underlies the building permit form.

Yes I have heard that calculated values should not be stored, but
other
parts of the application are easier to program with the stored
'BPExpires'
field.

Everything works great as long as there are four or fewer entries
on
the
subform. Both 'BPExpire' & 'BPExpires' calculate & record
correctly.
As
soon as I enter a fifth entry on the subform, 'BPExpire'
calculates
OK
but
the stored value of 'BPExpires' reverts back to the
'BPIssueDate'.
I
have
over 1000 building permit forms and only four of them have five
or
more
subform entries. The only building permit forms that don't work
correctly
are these four.

Does anybody have an idea why the program works with four subform
entries
and fails with five subform entries?




Jeff

I would be happy to write a field in a query to calculate BPExpire
but
so
far all my efforts have failed. What I have is:

frmBPData - Record Source: qryBPdata that includes tblBPData
Text Box Name: BPIssueDate
ControlSource: BPIssueDate
Text Box Name: BPExpire
Control Source:
=IIf(IsDate([BPIssueDate]),DateAdd("m",Nz([sfrmBPPayments].[Form]!
[BPTotalMonths],0),[BPIssueDate])," ")

sfrBPPayments - Record Source: tblBPPayments
Text Box Name: BPMonths
ControlSource: BPMonths
Text Box Name: BPTotalMonths
ControlSource: =Sum([BPMonths])

BPExpire calculates OK and shows up in its text box for any and all
subform
records. But I can't reference BPExpire in other reports, queries
or
forms.

I have tied to write a field BPExpires:>>>>>> in qryBPData based on
the
control source for BPExpire shown above but ave been unsuccessful.
(Yes
Expires instead of Expire) Any criteria I do write that doesn't
result
in
an Error of Name violation doesn't show in text box BPExpires on
frmBPData.

Paddler

 

[Guest]

Jeff

This is not the answer. When I put Forms![frmBPData].[BPExpire] in as the
control source for the building permit expire date on my report
rptBPermitList-Expired, and search for building permit issue dates starting
1/1/06 and ending 6/1/06, there are 15 expired permits. But the BPExpire
control on the report reads #Error. OK so I open frmBPData and then open the
report. Sure there is the BPExpire date (the one associated with the open
form) fifteen times on the open report but this is not the real BPExpire date
that should be associated with the 15 expired permits. If I open frmBPData
to a different record, that related wrong date shows up as the expire date on
all 15 spaces. My report is supposed to show the BPexpire date associated
with that building permit not some random building permit that happens to be
on open on the screen.

I give up. do you want to return to my original question as to why I can
save the first 4 calculated BPExpire dates but can't save the fifth? If not,
I will cobble up something to limit the subform to four records. There were
on 4 out of 1131 building permits that had more than four extensions.

Paddler

=Forms!YourFormName!YourControlName

Use this in your report, as the control source for a control in which you
want to see this date.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff
I want to refer to BPExpire (a date) so I can find and print a list of
Expired Building Permits. I have a calculated control on frmBPData that
calculates BPExpire.
=IIf(IsDate([BPIssueDate]),DateAdd("m",Nz([sfrmBPPayments].[Form]!
[BPTotalMonths],0),[BPIssueDate])," ")
How do I reference BPExpire so I can generate this report?

If I can'r refer to the calculated control BPExpire, what are my options
to
get an expiration date?
a. Save the calculated value BPExpire in a table tblBPData.
b. Calculate a field BPExpires in a Query so it can be referenced.
c. please define
I am having trouble doing b. above. I copied the calculated control to a
field on qryBPData. I can't open the query in datasheet view because the
query cant find parameter sfrmBPPayments.Form!BPTotalMonths.

The rules say to refer to a field using the following syntax
Forms."Form Name".subform Name.form."controlName"

Apparently I have the last three terms correct because calculated control
BPExpire works and I copied and pasted it into BPExpires:. Therefore it
must
be the first two terms that are incorrect. The first term is simply
Forms.
That leaves the second term and how could I mistype frmBPData. So why
doesn't the following work? What a I doing wrong? What is the correct
syntax to use?.
BPExpires:
IIf(IsDate([BPIssueDate]),DateAdd("m",[Forms].[frmBPData].[sfrmBPPayments].
[Form]![BPTotalMonths],[BPIssueDate])," ")
What are your syntax rules for writing a calculated field in a query?

If this cant be done, I have no choice but to return to the problem of
saving the Calculated value BPExpire in tblBPData. Do you want the VBA
code
for saving calculated value BPExpire to investigate why it stops
functioning
when the fifth Building Permint etension is entered on sfrmBPPayments?

Paddler

I get the #Name when Access can't find something the way I named it.
This
usually happens if I've typed in the control source, rather than
selecting
it from a drop-down or copying/pasting.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff

I have a form frmBPdata open and it has a subform sfrmBPPayments open
on
the
main form.

My main form has two text boxes
Textbox 1 has as its Name: BPExpire and as its Control Source
=IIf(IsDate([BPIssueDate]),DateAdd("m",Nz([sfrmBPPayments].[Form]![BPTotalMonths],0),[BPIssueDate]),"
")
This is a bound control and the equation works and displays correctly
every
time I add a record in sfrmBPPayments to add more BPMonths to the
building
permit.
This is the equation I discussed in my previous post. Now if I want to
write a report listing expired building permits, I do not know how to
reference my calculated control BPExpire on frmBPData.

I assume the way around this problem is to calculate BPExpire in a
field
on
the query qryBPData which underlies frmBPData. So I change the name to
BPExpires and write the field equation as:
BPExpires:
IIf(IsDate([BPIssueDate]),DateAdd("m",[Forms].[frmBPData].[sfrmBPPayments].
[Form]![BPTotalMonths],[BPIssueDate])," ")

For developent purposes, I have a second text field on frmBPData. It
has
a
Name of BPExpires and a Control Source of BPExpires; this is a bound
control.
I can observe both BPExpire & BPExpires when I add BPMonths to
sfrmBPPayments.

The open text box BPExpires is now registering #Name? because something
is
wrong with the BPExpires: equation probably my Form & Subform reference
to
BPTotalMonths. If the equation is corrected and BPExpires is the
correct
date I will be OK. Now since BPExpires is in a query, I can reference
it
when I write the query for a report listing expired building permits.

I hope you can held me correct the BPExpires: equation. This would
certainly be a better solution than writing the calculated value of
BPExpire
into tblBPData as BPExpires.

If you need to return to my original question as to why I can't write a
fifth change in the building permit RBExpire date to tblBPData, I could
send
the VBA code. But it is rather long because it involves Allen Browne's
'Return to the same record next time form is opened' VBA code for
uloading
and loading a form.

Paddler




:

How are you referring to that calculated control?

Do you have the form in which it lives open at the time you are
referring
to
it?

Is the control bound or unbound?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP



:

You've described all the calculating you are doing, but haven't
shown
us
the
expressions you are using. If you want specific suggestions about
how
your
calculations are working, provide some specific information.

And if you know that storing a calculated value can cause you
issues
of
synchronization, why not use a query to do the calculation, rather
than
your
form?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am having trouble updating a date value on a building permit
form
(frmBPData) when the number of entries on a related subform
(sfrmBPPayments)
is greater than 4. The building permit form has a field that I
enter
'BPIssueDate' and calculated value 'DateExpire'. The subform,
which
permits
buying building permit extensions, has a field "BPMonths"
indicating
the
incremental months to be added to the building permit. A field
on
the
subform 'BPTotalMonths' sums up the 'BPMonths'.
There is a field on the main form, which calculates a 'BPExpire'
date,
using
the DateAdd function and "m" referring to 'BPTotalMosnths' and a
base
date
of
'BPIssueDate'. I use some VBA code to write the 'BPExpire'
value
to a
field
'BPExpire' in the table that underlies the building permit form.

Yes I have heard that calculated values should not be stored, but
other
parts of the application are easier to program with the stored
'BPExpires'
field.

Everything works great as long as there are four or fewer entries
on
the
subform. Both 'BPExpire' & 'BPExpires' calculate & record
correctly.
As
soon as I enter a fifth entry on the subform, 'BPExpire'
calculates
OK
but
the stored value of 'BPExpires' reverts back to the
'BPIssueDate'.
I
have
over 1000 building permit forms and only four of them have five
or
more
subform entries. The only building permit forms that don't work
correctly
are these four.

Does anybody have an idea why the program works with four subform
entries
and fails with five subform entries?




Jeff

I would be happy to write a field in a query to calculate BPExpire
but
so
far all my efforts have failed. What I have is:

frmBPData - Record Source: qryBPdata that includes tblBPData
Text Box Name: BPIssueDate
ControlSource: BPIssueDate
Text Box Name: BPExpire
Control Source:
=IIf(IsDate([BPIssueDate]),DateAdd("m",Nz([sfrmBPPayments].[Form]!
[BPTotalMonths],0),[BPIssueDate])," ")

sfrBPPayments - Record Source: tblBPPayments
Text Box Name: BPMonths
ControlSource: BPMonths
Text Box Name: BPTotalMonths
ControlSource: =Sum([BPMonths])

BPExpire calculates OK and shows up in its text box for any and all
subform
records. But I can't reference BPExpire in other reports, queries
or
forms.

I have tied to write a field BPExpires:>>>>>> in qryBPData based on
the
control source for BPExpire shown above but ave been unsuccessful.
(Yes
Expires instead of Expire) Any criteria I do write that doesn't
result
in
an Error of Name violation doesn't show in text box BPExpires on
frmBPData.

Paddler

 

[Jeff Boyce]

I see #Error when Access is having trouble understanding or calculating
something.

Since I'm not there, my questions have been directed at trying to learn
enough to offer specific suggestions.

It sounds like you have a less-than-optimal solution in mind.

It seems unusual that Access would allow 4, but not a fifth. Is there any
chance there is a subtle spelling error?

Best of luck on your project!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff

This is not the answer. When I put Forms![frmBPData].[BPExpire] in as the
control source for the building permit expire date on my report
rptBPermitList-Expired, and search for building permit issue dates
starting
1/1/06 and ending 6/1/06, there are 15 expired permits. But the BPExpire
control on the report reads #Error. OK so I open frmBPData and then open
the
report. Sure there is the BPExpire date (the one associated with the open
form) fifteen times on the open report but this is not the real BPExpire
date
that should be associated with the 15 expired permits. If I open
frmBPData
to a different record, that related wrong date shows up as the expire date
on
all 15 spaces. My report is supposed to show the BPexpire date associated
with that building permit not some random building permit that happens to
be
on open on the screen.

I give up. do you want to return to my original question as to why I can
save the first 4 calculated BPExpire dates but can't save the fifth? If
not,
I will cobble up something to limit the subform to four records. There
were
on 4 out of 1131 building permits that had more than four extensions.

Paddler

=Forms!YourFormName!YourControlName

Use this in your report, as the control source for a control in which you
want to see this date.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff
I want to refer to BPExpire (a date) so I can find and print a list of
Expired Building Permits. I have a calculated control on frmBPData
that
calculates BPExpire.
=IIf(IsDate([BPIssueDate]),DateAdd("m",Nz([sfrmBPPayments].[Form]!
[BPTotalMonths],0),[BPIssueDate])," ")
How do I reference BPExpire so I can generate this report?

If I can'r refer to the calculated control BPExpire, what are my
options
to
get an expiration date?
a. Save the calculated value BPExpire in a table tblBPData.
b. Calculate a field BPExpires in a Query so it can be referenced.
c. please define
I am having trouble doing b. above. I copied the calculated control to
a
field on qryBPData. I can't open the query in datasheet view because
the
query cant find parameter sfrmBPPayments.Form!BPTotalMonths.

The rules say to refer to a field using the following syntax
Forms."Form Name".subform Name.form."controlName"

Apparently I have the last three terms correct because calculated
control
BPExpire works and I copied and pasted it into BPExpires:. Therefore
it
must
be the first two terms that are incorrect. The first term is simply
Forms.
That leaves the second term and how could I mistype frmBPData. So why
doesn't the following work? What a I doing wrong? What is the correct
syntax to use?.
BPExpires:
IIf(IsDate([BPIssueDate]),DateAdd("m",[Forms].[frmBPData].[sfrmBPPayments].
[Form]![BPTotalMonths],[BPIssueDate])," ")
What are your syntax rules for writing a calculated field in a query?

If this cant be done, I have no choice but to return to the problem of
saving the Calculated value BPExpire in tblBPData. Do you want the VBA
code
for saving calculated value BPExpire to investigate why it stops
functioning
when the fifth Building Permint etension is entered on sfrmBPPayments?

Paddler

:

I get the #Name when Access can't find something the way I named it.
This
usually happens if I've typed in the control source, rather than
selecting
it from a drop-down or copying/pasting.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff

I have a form frmBPdata open and it has a subform sfrmBPPayments
open
on
the
main form.

My main form has two text boxes
Textbox 1 has as its Name: BPExpire and as its Control Source
=IIf(IsDate([BPIssueDate]),DateAdd("m",Nz([sfrmBPPayments].[Form]![BPTotalMonths],0),[BPIssueDate]),"
")
This is a bound control and the equation works and displays
correctly
every
time I add a record in sfrmBPPayments to add more BPMonths to the
building
permit.
This is the equation I discussed in my previous post. Now if I want
to
write a report listing expired building permits, I do not know how
to
reference my calculated control BPExpire on frmBPData.

I assume the way around this problem is to calculate BPExpire in a
field
on
the query qryBPData which underlies frmBPData. So I change the name
to
BPExpires and write the field equation as:
BPExpires:
IIf(IsDate([BPIssueDate]),DateAdd("m",[Forms].[frmBPData].[sfrmBPPayments].
[Form]![BPTotalMonths],[BPIssueDate])," ")

For developent purposes, I have a second text field on frmBPData.
It
has
a
Name of BPExpires and a Control Source of BPExpires; this is a bound
control.
I can observe both BPExpire & BPExpires when I add BPMonths to
sfrmBPPayments.

The open text box BPExpires is now registering #Name? because
something
is
wrong with the BPExpires: equation probably my Form & Subform
reference
to
BPTotalMonths. If the equation is corrected and BPExpires is the
correct
date I will be OK. Now since BPExpires is in a query, I can
reference
it
when I write the query for a report listing expired building
permits.

I hope you can held me correct the BPExpires: equation. This would
certainly be a better solution than writing the calculated value of
BPExpire
into tblBPData as BPExpires.

If you need to return to my original question as to why I can't
write a
fifth change in the building permit RBExpire date to tblBPData, I
could
send
the VBA code. But it is rather long because it involves Allen
Browne's
'Return to the same record next time form is opened' VBA code for
uloading
and loading a form.

Paddler




:

How are you referring to that calculated control?

Do you have the form in which it lives open at the time you are
referring
to
it?

Is the control bound or unbound?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP



:

You've described all the calculating you are doing, but haven't
shown
us
the
expressions you are using. If you want specific suggestions
about
how
your
calculations are working, provide some specific information.

And if you know that storing a calculated value can cause you
issues
of
synchronization, why not use a query to do the calculation,
rather
than
your
form?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am having trouble updating a date value on a building permit
form
(frmBPData) when the number of entries on a related subform
(sfrmBPPayments)
is greater than 4. The building permit form has a field that
I
enter
'BPIssueDate' and calculated value 'DateExpire'. The subform,
which
permits
buying building permit extensions, has a field "BPMonths"
indicating
the
incremental months to be added to the building permit. A
field
on
the
subform 'BPTotalMonths' sums up the 'BPMonths'.
There is a field on the main form, which calculates a
'BPExpire'
date,
using
the DateAdd function and "m" referring to 'BPTotalMosnths' and
a
base
date
of
'BPIssueDate'. I use some VBA code to write the 'BPExpire'
value
to a
field
'BPExpire' in the table that underlies the building permit
form.

Yes I have heard that calculated values should not be stored,
but
other
parts of the application are easier to program with the stored
'BPExpires'
field.

Everything works great as long as there are four or fewer
entries
on
the
subform. Both 'BPExpire' & 'BPExpires' calculate & record
correctly.
As
soon as I enter a fifth entry on the subform, 'BPExpire'
calculates
OK
but
the stored value of 'BPExpires' reverts back to the
'BPIssueDate'.
I
have
over 1000 building permit forms and only four of them have
five
or
more
subform entries. The only building permit forms that don't
work
correctly
are these four.

Does anybody have an idea why the program works with four
subform
entries
and fails with five subform entries?




Jeff

I would be happy to write a field in a query to calculate
BPExpire
but
so
far all my efforts have failed. What I have is:

frmBPData - Record Source: qryBPdata that includes tblBPData
Text Box Name: BPIssueDate
ControlSource: BPIssueDate
Text Box Name: BPExpire
Control Source:
=IIf(IsDate([BPIssueDate]),DateAdd("m",Nz([sfrmBPPayments].[Form]!
[BPTotalMonths],0),[BPIssueDate])," ")

sfrBPPayments - Record Source: tblBPPayments
Text Box Name: BPMonths
ControlSource: BPMonths
Text Box Name: BPTotalMonths
ControlSource: =Sum([BPMonths])

BPExpire calculates OK and shows up in its text box for any and
all
subform
records. But I can't reference BPExpire in other reports,
queries
or
forms.

I have tied to write a field BPExpires:>>>>>> in qryBPData based
on
the
control source for BPExpire shown above but ave been
unsuccessful.
(Yes
Expires instead of Expire) Any criteria I do write that doesn't
result
in
an Error of Name violation doesn't show in text box BPExpires on
frmBPData.

Paddler

 

[Guest]

I see #Error when Access is having trouble understanding or calculating
something.

Since I'm not there, my questions have been directed at trying to learn
enough to offer specific suggestions.

It sounds like you have a less-than-optimal solution in mind.

It seems unusual that Access would allow 4, but not a fifth. Is there any
chance there is a subtle spelling error?

Best of luck on your project!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff

This is not the answer. When I put Forms![frmBPData].[BPExpire] in as the
control source for the building permit expire date on my report
rptBPermitList-Expired, and search for building permit issue dates
starting
1/1/06 and ending 6/1/06, there are 15 expired permits. But the BPExpire
control on the report reads #Error. OK so I open frmBPData and then open
the
report. Sure there is the BPExpire date (the one associated with the open
form) fifteen times on the open report but this is not the real BPExpire
date
that should be associated with the 15 expired permits. If I open
frmBPData
to a different record, that related wrong date shows up as the expire date
on
all 15 spaces. My report is supposed to show the BPexpire date associated
with that building permit not some random building permit that happens to
be
on open on the screen.

I give up. do you want to return to my original question as to why I can
save the first 4 calculated BPExpire dates but can't save the fifth? If
not,
I will cobble up something to limit the subform to four records. There
were
on 4 out of 1131 building permits that had more than four extensions.

Paddler

=Forms!YourFormName!YourControlName

Use this in your report, as the control source for a control in which you
want to see this date.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff
I want to refer to BPExpire (a date) so I can find and print a list of
Expired Building Permits. I have a calculated control on frmBPData
that
calculates BPExpire.
=IIf(IsDate([BPIssueDate]),DateAdd("m",Nz([sfrmBPPayments].[Form]!
[BPTotalMonths],0),[BPIssueDate])," ")
How do I reference BPExpire so I can generate this report?

If I can'r refer to the calculated control BPExpire, what are my
options
to
get an expiration date?
a. Save the calculated value BPExpire in a table tblBPData.
b. Calculate a field BPExpires in a Query so it can be referenced.
c. please define
I am having trouble doing b. above. I copied the calculated control to
a
field on qryBPData. I can't open the query in datasheet view because
the
query cant find parameter sfrmBPPayments.Form!BPTotalMonths.

The rules say to refer to a field using the following syntax
Forms."Form Name".subform Name.form."controlName"

Apparently I have the last three terms correct because calculated
control
BPExpire works and I copied and pasted it into BPExpires:. Therefore
it
must
be the first two terms that are incorrect. The first term is simply
Forms.
That leaves the second term and how could I mistype frmBPData. So why
doesn't the following work? What a I doing wrong? What is the correct
syntax to use?.
BPExpires:
IIf(IsDate([BPIssueDate]),DateAdd("m",[Forms].[frmBPData].[sfrmBPPayments].
[Form]![BPTotalMonths],[BPIssueDate])," ")
What are your syntax rules for writing a calculated field in a query?

If this cant be done, I have no choice but to return to the problem of
saving the Calculated value BPExpire in tblBPData. Do you want the VBA
code
for saving calculated value BPExpire to investigate why it stops
functioning
when the fifth Building Permint etension is entered on sfrmBPPayments?

Paddler

:

I get the #Name when Access can't find something the way I named it.
This
usually happens if I've typed in the control source, rather than
selecting
it from a drop-down or copying/pasting.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff

I have a form frmBPdata open and it has a subform sfrmBPPayments
open
on
the
main form.

My main form has two text boxes
Textbox 1 has as its Name: BPExpire and as its Control Source
=IIf(IsDate([BPIssueDate]),DateAdd("m",Nz([sfrmBPPayments].[Form]![BPTotalMonths],0),[BPIssueDate]),"
")
This is a bound control and the equation works and displays
correctly
every
time I add a record in sfrmBPPayments to add more BPMonths to the
building
permit.
This is the equation I discussed in my previous post. Now if I want
to
write a report listing expired building permits, I do not know how
to
reference my calculated control BPExpire on frmBPData.

I assume the way around this problem is to calculate BPExpire in a
field
on
the query qryBPData which underlies frmBPData. So I change the name
to
BPExpires and write the field equation as:
BPExpires:
IIf(IsDate([BPIssueDate]),DateAdd("m",[Forms].[frmBPData].[sfrmBPPayments].
[Form]![BPTotalMonths],[BPIssueDate])," ")

For developent purposes, I have a second text field on frmBPData.
It
has
a
Name of BPExpires and a Control Source of BPExpires; this is a bound
control.
I can observe both BPExpire & BPExpires when I add BPMonths to
sfrmBPPayments.

The open text box BPExpires is now registering #Name? because
something
is
wrong with the BPExpires: equation probably my Form & Subform
reference
to
BPTotalMonths. If the equation is corrected and BPExpires is the
correct
date I will be OK. Now since BPExpires is in a query, I can
reference
it
when I write the query for a report listing expired building
permits.

I hope you can held me correct the BPExpires: equation. This would
certainly be a better solution than writing the calculated value of
BPExpire
into tblBPData as BPExpires.

If you need to return to my original question as to why I can't
write a
fifth change in the building permit RBExpire date to tblBPData, I
could
send
the VBA code. But it is rather long because it involves Allen
Browne's
'Return to the same record next time form is opened' VBA code for
uloading
and loading a form.

Paddler




:

How are you referring to that calculated control?

Do you have the form in which it lives open at the time you are
referring
to
it?

Is the control bound or unbound?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP



:

You've described all the calculating you are doing, but haven't
shown
us
the
expressions you are using. If you want specific suggestions
about
how
your
calculations are working, provide some specific information.

And if you know that storing a calculated value can cause you
issues
of
synchronization, why not use a query to do the calculation,
rather
than
your
form?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am having trouble updating a date value on a building permit
form
(frmBPData) when the number of entries on a related subform
(sfrmBPPayments)
is greater than 4. The building permit form has a field that
I
enter
'BPIssueDate' and calculated value 'DateExpire'. The subform,
which
permits
buying building permit extensions, has a field "BPMonths"
indicating
the
incremental months to be added to the building permit. A
field
on
the
subform 'BPTotalMonths' sums up the 'BPMonths'.
There is a field on the main form, which calculates a
'BPExpire'
date,
using
the DateAdd function and "m" referring to 'BPTotalMosnths' and
a
base
date
of
'BPIssueDate'. I use some VBA code to write the 'BPExpire'
value
to a
field
'BPExpire' in the table that underlies the building permit
form.

Yes I have heard that calculated values should not be stored,
but
other
parts of the application are easier to program with the stored
'BPExpires'
field.

Everything works great as long as there are four or fewer
entries
on