Automation by not storing calulated variables

[elliottpt via AccessMonster.com]

Hi

After some investigation it seems best practice not to store calulated
variables, and I
can't seem to do it either, but rather to recalculate them. This however is
posing a little
frusting dilemma for me.

I have a single form(Vesselcapturing) which reads and displays data from a
table(tblTFCMASTER)to the end user fot editing. If the user edits/updates a
record I need to
be aware of this, as this newly updated record must be displayed in other
forms and be exported to excel. Currently I have a check box(TEST) which is
manually changed to True by
the user upon completion of the editing.This state of the check box is then
saved to
tblTFCMASTER. Since in its aletered state I can filter on it (TEST = True).

For the exporting of the data to excel I have a query that is exported to
excel. Therefore
filtering here is no problem.I also need to filter in 3 other forms(e.g
frmDOC). I have an on load event filter which does the
job:

Me.Filter = "TEST = True"
Me.FilterOn = True

This entire scenario functions perfectly but obviosuly it would be better if
the end user
does not have to manually check the check box but rather have a
expression/formula that
would automate it based on updated values in other fileds in the
Vesselcapturing form.

So here are my thoughts on automating the process(which isn't working):

In the other forms and the query an if statement can be placed which will
change the status
of the TEST checkbox.

eg.possable statements

EXP 1.
expression in the control source of TEST field:
=IIf([DATEARRIVE]>#01/01/2008#,Yes,No)

[This changes the status of the check box for each individaul record and
works]

EXP 2.
expression in the on open event
=IIf([DATEARRIVE]>#01/01/2008#,[TEST]=True,[TEST]=False)

[this is supposed to change the status of the check box,but doesn't work]

EXP3.
vb code in the on open event

If [DATEARRIVE] > #1/1/2008# Then
[TEST] = True
Else
[TEST] = False
End If

[this is supposed to change the status of the check box, TEST has no control
source, this
statement however does not differentiate between the records>it changes all
the records TEST
value based upon the 1st records DATEARRIVE]

Now the question is where would I place an if statement/expression since if I
place EXP1 in
the control source the filter does not pick up the change since on load it is
still false.
My thoughts were on placing EXP2 or EXP3 on the on open event and leave the
filter on the on
load event, so as to 1st change the status of the check box before the filter
is fired.
However neither EXP2 or EXP3 function properly.

any suggestions/ correction would be well appreciated
Regards
Philip

 

[Klatuu]

The easiest solution would be to change the value of the checkbox in the
form where you do the editing of the record. It would be easy enough to
make the check box hidden and in the form Before Update event:

Me.Test = True

Hi

After some investigation it seems best practice not to store calulated
variables, and I
can't seem to do it either, but rather to recalculate them. This however
is
posing a little
frusting dilemma for me.

I have a single form(Vesselcapturing) which reads and displays data from a
table(tblTFCMASTER)to the end user fot editing. If the user edits/updates
a
record I need to
be aware of this, as this newly updated record must be displayed in other
forms and be exported to excel. Currently I have a check box(TEST) which
is
manually changed to True by
the user upon completion of the editing.This state of the check box is
then
saved to
tblTFCMASTER. Since in its aletered state I can filter on it (TEST =
True).

For the exporting of the data to excel I have a query that is exported to
excel. Therefore
filtering here is no problem.I also need to filter in 3 other forms(e.g
frmDOC). I have an on load event filter which does the
job:

Me.Filter = "TEST = True"
Me.FilterOn = True

This entire scenario functions perfectly but obviosuly it would be better
if
the end user
does not have to manually check the check box but rather have a
expression/formula that
would automate it based on updated values in other fileds in the
Vesselcapturing form.

So here are my thoughts on automating the process(which isn't working):

In the other forms and the query an if statement can be placed which will
change the status
of the TEST checkbox.

eg.possable statements

EXP 1.
expression in the control source of TEST field:
=IIf([DATEARRIVE]>#01/01/2008#,Yes,No)

[This changes the status of the check box for each individaul record and
works]

EXP 2.
expression in the on open event
=IIf([DATEARRIVE]>#01/01/2008#,[TEST]=True,[TEST]=False)

[this is supposed to change the status of the check box,but doesn't work]

EXP3.
vb code in the on open event

If [DATEARRIVE] > #1/1/2008# Then
[TEST] = True
Else
[TEST] = False
End If

[this is supposed to change the status of the check box, TEST has no
control
source, this
statement however does not differentiate between the records>it changes
all
the records TEST
value based upon the 1st records DATEARRIVE]

Now the question is where would I place an if statement/expression since
if I
place EXP1 in
the control source the filter does not pick up the change since on load it
is
still false.
My thoughts were on placing EXP2 or EXP3 on the on open event and leave
the
filter on the on
load event, so as to 1st change the status of the check box before the
filter
is fired.
However neither EXP2 or EXP3 function properly.

any suggestions/ correction would be well appreciated
Regards
Philip

 

[BruceM]

I'm not sure I understand. Are you just trying to set a check box for a
visual reference? If so, in the form's Current event:
Me.chkTest = Me.DATEARRIVE > #01/01/2008#

The Open event is too early since the records are not yet loaded, and the
Load event runs once only (after the Open event) when the recordset is
loaded.

Note that if you want to test for the current year rather than revising the
code each year you could do something like this:

Dim datArrive as Date

datArrive = DateSerial(Year(Date()),1,1)

Me.chkTest = Me.DATEARRIVE > datArrive

A further note is that in a Continuous form you will need to use a different
approach. For instance, in the RecordSource query:
DateTest: IIf([DATEARRIVE] > DateSerial(Year(Date()),1,1),-1,0)

I think this would also work as:
DateTest: IIf([DATEARRIVE] > DateSerial(Year(Date()),1,1),True,False)

Bind a check box to DateTest. This would work on a single form or in
datasheet view, as well as on a continuous form.

Hi

After some investigation it seems best practice not to store calulated
variables, and I
can't seem to do it either, but rather to recalculate them. This however
is
posing a little
frusting dilemma for me.

I have a single form(Vesselcapturing) which reads and displays data from a
table(tblTFCMASTER)to the end user fot editing. If the user edits/updates
a
record I need to
be aware of this, as this newly updated record must be displayed in other
forms and be exported to excel. Currently I have a check box(TEST) which
is
manually changed to True by
the user upon completion of the editing.This state of the check box is
then
saved to
tblTFCMASTER. Since in its aletered state I can filter on it (TEST =
True).

For the exporting of the data to excel I have a query that is exported to
excel. Therefore
filtering here is no problem.I also need to filter in 3 other forms(e.g
frmDOC). I have an on load event filter which does the
job:

Me.Filter = "TEST = True"
Me.FilterOn = True

This entire scenario functions perfectly but obviosuly it would be better
if
the end user
does not have to manually check the check box but rather have a
expression/formula that
would automate it based on updated values in other fileds in the
Vesselcapturing form.

So here are my thoughts on automating the process(which isn't working):

In the other forms and the query an if statement can be placed which will
change the status
of the TEST checkbox.

eg.possable statements

EXP 1.
expression in the control source of TEST field:
=IIf([DATEARRIVE]>#01/01/2008#,Yes,No)

[This changes the status of the check box for each individaul record and
works]

EXP 2.
expression in the on open event
=IIf([DATEARRIVE]>#01/01/2008#,[TEST]=True,[TEST]=False)

[this is supposed to change the status of the check box,but doesn't work]

EXP3.
vb code in the on open event

If [DATEARRIVE] > #1/1/2008# Then
[TEST] = True
Else
[TEST] = False
End If

[this is supposed to change the status of the check box, TEST has no
control
source, this
statement however does not differentiate between the records>it changes
all
the records TEST
value based upon the 1st records DATEARRIVE]

Now the question is where would I place an if statement/expression since
if I
place EXP1 in
the control source the filter does not pick up the change since on load it
is
still false.
My thoughts were on placing EXP2 or EXP3 on the on open event and leave
the
filter on the on
load event, so as to 1st change the status of the check box before the
filter
is fired.
However neither EXP2 or EXP3 function properly.

any suggestions/ correction would be well appreciated
Regards
Philip

 

[elliottpt via AccessMonster.com]

Hi Klatuu

Thanks so much; two things tho>

a) it doesn't make the change
b) am I corect in understanding that this will change all records if they are
tabbed through; even tho there was no edit made.

would it be better to use the if?

regards
philip

The easiest solution would be to change the value of the checkbox in the
form where you do the editing of the record. It would be easy enough to
make the check box hidden and in the form Before Update event:

Me.Test = True

Hi

[quoted text clipped - 91 lines]

Regards
Philip

 

[elliottpt via AccessMonster.com]

Hi Bruce

Thanks for you input.

To clarify:

Date arrive is blank a field in the form; therefor I am using it as a test
value to change the value of the TEST check box as if it is bigger then
01/01/2008 since then I know that the record has been updated. At the moment
it is dsiplayed as the user has to manually check it; upon automation I will
hide it.

Regards
Philip

I'm not sure I understand. Are you just trying to set a check box for a
visual reference? If so, in the form's Current event:
Me.chkTest = Me.DATEARRIVE > #01/01/2008#

The Open event is too early since the records are not yet loaded, and the
Load event runs once only (after the Open event) when the recordset is
loaded.

Note that if you want to test for the current year rather than revising the
code each year you could do something like this:

Dim datArrive as Date

datArrive = DateSerial(Year(Date()),1,1)

Me.chkTest = Me.DATEARRIVE > datArrive

A further note is that in a Continuous form you will need to use a different
approach. For instance, in the RecordSource query:
DateTest: IIf([DATEARRIVE] > DateSerial(Year(Date()),1,1),-1,0)

I think this would also work as:
DateTest: IIf([DATEARRIVE] > DateSerial(Year(Date()),1,1),True,False)

Bind a check box to DateTest. This would work on a single form or in
datasheet view, as well as on a continuous form.

Hi

[quoted text clipped - 91 lines]

Regards
Philip

 

[elliottpt via AccessMonster.com]

Hi Klatuu

Thanks its worked out now...

Hi Klatuu

Thanks so much; two things tho>

a) it doesn't make the change
b) am I corect in understanding that this will change all records if they are
tabbed through; even tho there was no edit made.

would it be better to use the if?

regards
philip

The easiest solution would be to change the value of the checkbox in the
form where you do the editing of the record. It would be easy enough to

[quoted text clipped - 7 lines]