Automation by not storing calulated variables "elliottpt via AccessMonster.com" <u47125[ at ]uwe> 11/12/2008 1:30:23 PM 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200811/1

Re: Automation by not storing calulated variables "Klatuu" <david.hargis[ at ]realpage.com> 11/12/2008 4:54:43 PM 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 "elliottpt via AccessMonster.com" <u47125[ at ]uwe> wrote in message news:8d145065a74a9[ at ]uwe... [Quoted Text] > 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 > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200811/1 >

Re: Automation by not storing calulated variables "BruceM" <bamoob[ at ]yawhodotcalm.not> 11/12/2008 4:59:34 PM 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. "elliottpt via AccessMonster.com" <u47125[ at ]uwe> wrote in message news:8d145065a74a9[ at ]uwe... [Quoted Text] > 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 > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200811/1 >

Re: Automation by not storing calulated variables "elliottpt via AccessMonster.com" <u47125[ at ]uwe> 11/13/2008 7:39:37 AM 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 Klatuu wrote: [Quoted Text] >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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200811/1

Re: Automation by not storing calulated variables "elliottpt via AccessMonster.com" <u47125[ at ]uwe> 11/13/2008 7:50:08 AM 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 BruceM wrote: [Quoted Text] >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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200811/1

Re: Automation by not storing calulated variables "elliottpt via AccessMonster.com" <u47125[ at ]uwe> 11/13/2008 8:35:56 AM Hi Klatuu Thanks its worked out now... :) elliottpt wrote: [Quoted Text] >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] >>> Regards >>> Philip -- Message posted via http://www.accessmonster.com