me.dirty

[Guest]

i have a form which has a bit of vba that's used the "On Current" event, the
part that concerns this post is just below:

Me.Time_on_List = DateDiff("d", Me.Date_on_List, Now())

and the same form has the following code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Dirty Then
Me.Updated_by = LAS_GetUserName()
Me.Last_edited = Now()
End If
End Sub

which edits the two fields "Udated_By" and "Last_edited" each record just
prior to its being updated.

before adding the "Time_on_List" field which is computed every time the user
observes a record (as the user scrolls through the form), the code above was
modified only if a user deliberately edited some control on the form. at this
point, even scrolling through the records has the same effect on the
"Updated_by", but particularly the "Last_edited" fields since this last one
is computed from "Now()" and gets changed every time the user observes a
record!

is there some way to get the code to ignore the fact the "Time_on_List"
field when assessing whether a record's dirty or not?

 

[Sandra Daigle]

Hi Ted,

Time_On_List really should not be a stored value since it is a derived value
and in this case, is only valid when you visit the record. Instead it should
be a calculated field in either the query or form. I would recommend that
you remove this code from the current event and then pu the expression into
the ControlSource of the Time_On_List control or create a calculated field
in the query, also based on the expression. Thenremove the field from the
table. If you go this route - you should probably also use the Date function
instead of Now to get the comparison time.

If you *really* want a stored value, which is only as good as the last time
the row was visited, then you could create a module level boolean variable
which is set to true when you want to skip the timestamping of the record.
Then always set back to false by the BeforeUpdate event.

 

[Geof Wyght]

Ted,
Sandra Daigle beat me to it. If you make time_on_list an
unbound control then the dirty property won't bother you.
Geof.

 

[Guest]

hi,

i didn't know that at the time, but earlier on when i had it as an unbound
field and the user used a datasheet view of the underlying table to review
the data (in edit off mode), the value of the Time_on_List variable would
remain the same throughout the column until the record pointer moved to
another record and thenn the same value would be displayed for every other
record on the screen. i reasoned this was because of some datasheet artifact
that i could live w/o and added the Time_on_List to my underlying table.

ted

 

[Sandra Daigle]

In datasheet view or in continuous form this is the nature of an *unbound*
control. An unbound control only has one value even though it may be shown
multiple times when used in the detail section of a continuous form.

Using a *calculated* control, where the ControlSource is the expression, you
will see the correct value for each row.

 

[Geof Wyght]

Interesting. That explains why my checkbox is checked for
all rows of the datasheet. I had to do the same thing as
Ted - turn it into a bound field...
Geof.

-----Original Message-----
In datasheet view or in continuous form this is the nature of an *unbound*
control. An unbound control only has one value even though it may be shown
multiple times when used in the detail section of a continuous form.

Using a *calculated* control, where the ControlSource is the expression, you
will see the correct value for each row.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

hi,

i didn't know that at the time, but earlier on when i had it as an
unbound field and the user used a datasheet view of the underlying
table to review the data (in edit off mode), the value of the
Time_on_List variable would remain the same throughout the column
until the record pointer moved to another record and thenn the same
value would be displayed for every other record on the screen. i
reasoned this was because of some datasheet artifact that i could
live w/o and added the Time_on_List to my underlying table.

ted

.

 

[Guest]

let me just extend this thread a tad, sandra

can the calculated value be used to sort records either in a form or a
report (say e.g. to list records in descending order of "Time_on_List" when
the value of another control "Outcome" = 'Pending' or is this restricted to
having the "Time_on_List" datum in the table and btw, i'm with you 100% of
the way if your method means getting around the constantly varying updated
fields i wrote about.

ted

ps: what's the big deal with the difference(s) btwn a 'derived' vs a
'calculated' control? i'm relatively 'new' to a2k so it seems like a
meaningless distinction.

In datasheet view or in continuous form this is the nature of an *unbound*
control. An unbound control only has one value even though it may be shown
multiple times when used in the detail section of a continuous form.

Using a *calculated* control, where the ControlSource is the expression, you
will see the correct value for each row.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

hi,

i didn't know that at the time, but earlier on when i had it as an
unbound field and the user used a datasheet view of the underlying
table to review the data (in edit off mode), the value of the
Time_on_List variable would remain the same throughout the column
until the record pointer moved to another record and thenn the same
value would be displayed for every other record on the screen. i
reasoned this was because of some datasheet artifact that i could
live w/o and added the Time_on_List to my underlying table.

ted

 

[Sandra Daigle]

That's probably it - it would be nice to be able to select records using
continuous forms and an unbound checkbox but as you've discovered, it
doesn't work. Listboxes are handy for this (assuming you're just using the
checkbox to select records for some temporary purpose).

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Interesting. That explains why my checkbox is checked for
all rows of the datasheet. I had to do the same thing as
Ted - turn it into a bound field...
Geof.

-----Original Message-----
In datasheet view or in continuous form this is the nature of an
*unbound* control. An unbound control only has one value even though
it may be shown multiple times when used in the detail section of a
continuous form.

Using a *calculated* control, where the ControlSource is the
expression, you will see the correct value for each row.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

hi,

i didn't know that at the time, but earlier on when i had it as an
unbound field and the user used a datasheet view of the underlying
table to review the data (in edit off mode), the value of the
Time_on_List variable would remain the same throughout the column
until the record pointer moved to another record and thenn the same
value would be displayed for every other record on the screen. i
reasoned this was because of some datasheet artifact that i could
live w/o and added the Time_on_List to my underlying table.

ted

:

Ted,
Sandra Daigle beat me to it. If you make time_on_list an
unbound control then the dirty property won't bother you.
Geof.

.

 

[Geof Wyght]

Yeah, the checkbox is for temporary use.

-----Original Message-----
That's probably it - it would be nice to be able to select records using
continuous forms and an unbound checkbox but as you've discovered, it
doesn't work. Listboxes are handy for this (assuming you're just using the
checkbox to select records for some temporary purpose).

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Interesting. That explains why my checkbox is checked for
all rows of the datasheet. I had to do the same thing as
Ted - turn it into a bound field...
Geof.

-----Original Message-----
In datasheet view or in continuous form this is the nature of an
*unbound* control. An unbound control only has one value even though
it may be shown multiple times when used in the detail section of a
continuous form.

Using a *calculated* control, where the ControlSource is the
expression, you will see the correct value for each row.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Ted wrote:
hi,

i didn't know that at the time, but earlier on when i had it as an
unbound field and the user used a datasheet view of the underlying
table to review the data (in edit off mode), the value of the
Time_on_List variable would remain the same throughout the column
until the record pointer moved to another record and thenn the same
value would be displayed for every other record on the screen. i
reasoned this was because of some datasheet artifact that i could
live w/o and added the Time_on_List to my underlying table.

ted

:

Ted,
Sandra Daigle beat me to it. If you make time_on_list an
unbound control then the dirty property won't bother you.
Geof.


.

.

 

[Sandra Daigle]

Yes - you can sort on a calculated field but I think it would need to be in
the query (rather than just a calculated control on the form).

ps: what's the big deal with the difference(s) btwn a 'derived' vs a
'calculated' control? i'm relatively 'new' to a2k so it seems like a
meaningless distinction.

No difference - derived values should be represented by calculated fields in
queries or on forms. This is part of normalizing your data, you shouldn't
store data that can be derived from one or more fields. The rationale is
that you can always calculate (or derive) the value when needed and you
eliminate the possibility of using data which is no longer correct because
the component values have changed and the code to update the stored data
hasn't been executed (ie it's 2 days later and I'm using a different form to
view your data and it doesn't recalculate the timeonlist value.)

 

[Geof Wyght]

Ted,
Add the calculated field to your record source query and
sort by it. As in Select ... , [Date_on_list] - Now() AS
TimeOnList ...
Order by [Date_on_list] - Now()

But keep the control unbound.
Geof.

-----Original Message-----
let me just extend this thread a tad, sandra

can the calculated value be used to sort records either in a form or a
report (say e.g. to list records in descending order of "Time_on_List" when
the value of another control "Outcome" = 'Pending' or is this restricted to
having the "Time_on_List" datum in the table and btw, i'm with you 100% of
the way if your method means getting around the constantly varying updated
fields i wrote about.

ted

ps: what's the big deal with the difference(s) btwn a 'derived' vs a
'calculated' control? i'm relatively 'new' to a2k so it seems like a
meaningless distinction.

In datasheet view or in continuous form this is the nature of an *unbound*
control. An unbound control only has one value even though it may be shown
multiple times when used in the detail section of a continuous form.

Using a *calculated* control, where the ControlSource is the expression, you
will see the correct value for each row.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

hi,

i didn't know that at the time, but earlier on when i had it as an
unbound field and the user used a datasheet view of the underlying
table to review the data (in edit off mode), the value of the
Time_on_List variable would remain the same throughout the column
until the record pointer moved to another record and thenn the same
value would be displayed for every other record on the screen. i
reasoned this was because of some datasheet artifact that i could
live w/o and added the Time_on_List to my underlying table.

ted

:

Ted,
Sandra Daigle beat me to it. If you make time_on_list an
unbound control then the dirty property won't bother you.
Geof.

.

 

[Guest]

so sandra, and i guess geoff too, i guess you mean

a) i ought to have query which is based on the table currently underlying
the form and
b) use a calculated control on the form defined to be:
=DateDiff("d",[Date_on_List],Date())

OR did you mean

a) create a query based on the table which is supplemented with an
additional field called "Time_on_List":=DateDiff("d",[Date_on_List],Date())

Yes - you can sort on a calculated field but I think it would need to be in
the query (rather than just a calculated control on the form).

ps: what's the big deal with the difference(s) btwn a 'derived' vs a
'calculated' control? i'm relatively 'new' to a2k so it seems like a
meaningless distinction.

No difference - derived values should be represented by calculated fields in
queries or on forms. This is part of normalizing your data, you shouldn't
store data that can be derived from one or more fields. The rationale is
that you can always calculate (or derive) the value when needed and you
eliminate the possibility of using data which is no longer correct because
the component values have changed and the code to update the stored data
hasn't been executed (ie it's 2 days later and I'm using a different form to
view your data and it doesn't recalculate the timeonlist value.)

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

let me just extend this thread a tad, sandra

can the calculated value be used to sort records either in a form or a
report (say e.g. to list records in descending order of
"Time_on_List" when the value of another control "Outcome" =
'Pending' or is this restricted to having the "Time_on_List" datum in
the table and btw, i'm with you 100% of the way if your method means
getting around the constantly varying updated fields i wrote about.

ted

ps: what's the big deal with the difference(s) btwn a 'derived' vs a
'calculated' control? i'm relatively 'new' to a2k so it seems like a
meaningless distinction.

 

[Geof Wyght]

I vote for:
a) create a query based on the table which is supplemented
with an additional field called "Time_on_List":=DateDiff
("d",[Date_on_List],Date())
b) use a calculated control on the form defined to be:
=DateDiff("d",[Date_on_List],Date())

Geof.

-----Original Message-----
so sandra, and i guess geoff too, i guess you mean

a) i ought to have query which is based on the table currently underlying
the form and
b) use a calculated control on the form defined to be:
=DateDiff("d",[Date_on_List],Date())

OR did you mean

a) create a query based on the table which is supplemented with an
additional field called "Time_on_List":=DateDiff("d", [Date_on_List],Date())

Yes - you can sort on a calculated field but I think it would need to be in
the query (rather than just a calculated control on the form).

ps: what's the big deal with the difference(s) btwn a 'derived' vs a
'calculated' control? i'm relatively 'new' to a2k so it seems like a
meaningless distinction.

No difference - derived values should be represented by calculated fields in
queries or on forms. This is part of normalizing your data, you shouldn't
store data that can be derived from one or more fields. The rationale is
that you can always calculate (or derive) the value when needed and you
eliminate the possibility of using data which is no longer correct because
the component values have changed and the code to update the stored data
hasn't been executed (ie it's 2 days later and I'm using a different form to
view your data and it doesn't recalculate the timeonlist value.)

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

let me just extend this thread a tad, sandra

can the calculated value be used to sort records either in a form or a
report (say e.g. to list records in descending order of
"Time_on_List" when the value of another control "Outcome" =
'Pending' or is this restricted to having the "Time_on_List" datum in
the table and btw, i'm with you 100% of the way if your method means
getting around the constantly varying updated fields i wrote about.

ted

ps: what's the big deal with the difference(s) btwn a 'derived' vs a
'calculated' control? i'm relatively 'new' to a2k so it seems like a
meaningless distinction.

.

 

[Sandra Daigle]

a) create a query based on the table which is supplemented with an
additional field called
"Time_on_List":=DateDiff("d",[Date_on_List],Date())

Then you can add a control to the form which is bound to the calculated
field. This will enable you to sort on the new field.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

so sandra, and i guess geoff too, i guess you mean

a) i ought to have query which is based on the table currently
underlying the form and
b) use a calculated control on the form defined to be:
=DateDiff("d",[Date_on_List],Date())

OR did you mean

a) create a query based on the table which is supplemented with an
additional field called
"Time_on_List":=DateDiff("d",[Date_on_List],Date())

Yes - you can sort on a calculated field but I think it would need
to be in the query (rather than just a calculated control on the
form).

ps: what's the big deal with the difference(s) btwn a 'derived' vs a
'calculated' control? i'm relatively 'new' to a2k so it seems like a
meaningless distinction.

No difference - derived values should be represented by calculated
fields in queries or on forms. This is part of normalizing your
data, you shouldn't store data that can be derived from one or more
fields. The rationale is that you can always calculate (or derive)
the value when needed and you eliminate the possibility of using
data which is no longer correct because the component values have
changed and the code to update the stored data hasn't been executed
(ie it's 2 days later and I'm using a different form to view your
data and it doesn't recalculate the timeonlist value.)

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

let me just extend this thread a tad, sandra

can the calculated value be used to sort records either in a form
or a report (say e.g. to list records in descending order of
"Time_on_List" when the value of another control "Outcome" =
'Pending' or is this restricted to having the "Time_on_List" datum
in the table and btw, i'm with you 100% of the way if your method
means getting around the constantly varying updated fields i wrote
about.

ted

ps: what's the big deal with the difference(s) btwn a 'derived' vs a
'calculated' control? i'm relatively 'new' to a2k so it seems like a
meaningless distinction.

 

[Guest]

what i've ended up doing/testing w/ some success is 'a' (from below) and
referring to "Time_on_List" as the Control Source for the Me.[Time_on_List]
control on my form rather than have the CS for it be set to '
=DateDiff("d",[Date_on_List],Date()) '

the unwanted auto updating that was happening before seems a thing of the
past at this point which is great

and looking at the sort ascending/descending buttons of the toolbar atop the
forms, they seem to be available/active (as opposed to greyed/stippled out)
when i plonk my cursor into the 'Time_on_List' form control.

(also, the good conditional formatting geoff and i sweated through the wknd
trying to figure our way through continues to work!!)

so, barring some unforeseen difficulty, it looks like we're through this
part of the woods.

with many thanks and best regards,

ted

I vote for:
a) create a query based on the table which is supplemented
with an additional field called "Time_on_List":=DateDiff
("d",[Date_on_List],Date())
b) use a calculated control on the form defined to be:
=DateDiff("d",[Date_on_List],Date())

Geof.

-----Original Message-----
so sandra, and i guess geoff too, i guess you mean

a) i ought to have query which is based on the table currently underlying
the form and
b) use a calculated control on the form defined to be:
=DateDiff("d",[Date_on_List],Date())

OR did you mean

a) create a query based on the table which is supplemented with an
additional field called "Time_on_List":=DateDiff("d", [Date_on_List],Date())

Yes - you can sort on a calculated field but I think it would need to be in
the query (rather than just a calculated control on the form).

ps: what's the big deal with the difference(s) btwn a 'derived' vs a
'calculated' control? i'm relatively 'new' to a2k so it seems like a
meaningless distinction.

No difference - derived values should be represented by calculated fields in
queries or on forms. This is part of normalizing your data, you shouldn't
store data that can be derived from one or more fields. The rationale is
that you can always calculate (or derive) the value when needed and you
eliminate the possibility of using data which is no longer correct because
the component values have changed and the code to update the stored data
hasn't been executed (ie it's 2 days later and I'm using a different form to
view your data and it doesn't recalculate the timeonlist value.)

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Ted wrote:
let me just extend this thread a tad, sandra

can the calculated value be used to sort records either in a form or a
report (say e.g. to list records in descending order of
"Time_on_List" when the value of another control "Outcome" =
'Pending' or is this restricted to having the "Time_on_List" datum in
the table and btw, i'm with you 100% of the way if your method means
getting around the constantly varying updated fields i wrote about.

ted

ps: what's the big deal with the difference(s) btwn a 'derived' vs a
'calculated' control? i'm relatively 'new' to a2k so it seems like a
meaningless distinction.

.

 

[Geof Wyght]

You're welcome. Actually, Sandra's response below is
technically better than my suggestion.
Geof.

-----Original Message-----
what i've ended up doing/testing w/ some success is 'a' (from below) and
referring to "Time_on_List" as the Control Source for the Me.[Time_on_List]
control on my form rather than have the CS for it be set to '
=DateDiff("d",[Date_on_List],Date()) '

the unwanted auto updating that was happening before seems a thing of the
past at this point which is great

and looking at the sort ascending/descending buttons of the toolbar atop the
forms, they seem to be available/active (as opposed to greyed/stippled out)
when i plonk my cursor into the 'Time_on_List' form control.

(also, the good conditional formatting geoff and i sweated through the wknd
trying to figure our way through continues to work!!)

so, barring some unforeseen difficulty, it looks like we're through this
part of the woods.

with many thanks and best regards,

ted

I vote for:
a) create a query based on the table which is supplemented
with an additional field called "Time_on_List":=DateDiff
("d",[Date_on_List],Date())
b) use a calculated control on the form defined to be:
=DateDiff("d",[Date_on_List],Date())

Geof.

-----Original Message-----
so sandra, and i guess geoff too, i guess you mean

a) i ought to have query which is based on the table currently underlying
the form and
b) use a calculated control on the form defined to be:
=DateDiff("d",[Date_on_List],Date())

OR did you mean

a) create a query based on the table which is supplemented with an
additional field called "Time_on_List":=DateDiff("d", [Date_on_List],Date())

:

Yes - you can sort on a calculated field but I think

it
would need to be in

the query (rather than just a calculated control on

the
form).

ps: what's the big deal with the difference(s) btwn a 'derived' vs a
'calculated' control? i'm relatively 'new' to a2k

so
it seems like a

meaningless distinction.

No difference - derived values should be represented

by
calculated fields in

queries or on forms. This is part of normalizing

your
data, you shouldn't

store data that can be derived from one or more

fields.
The rationale is

that you can always calculate (or derive) the value when needed and you
eliminate the possibility of using data which is no longer correct because
the component values have changed and the code to update the stored data
hasn't been executed (ie it's 2 days later and I'm using a different form to
view your data and it doesn't recalculate the timeonlist value.)

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Ted wrote:
let me just extend this thread a tad, sandra

can the calculated value be used to sort records either in a form or a
report (say e.g. to list records in descending

order
of

"Time_on_List" when the value of another control "Outcome" =
'Pending' or is this restricted to having the "Time_on_List" datum in
the table and btw, i'm with you 100% of the way if your method means
getting around the constantly varying updated

fields
i wrote about.

ted

ps: what's the big deal with the difference(s)

btwn
a 'derived' vs a

'calculated' control? i'm relatively 'new' to a2k

so
it seems like a

meaningless distinction.




.

.

 

[Guest]

whooops....i might've spoken a tad toooo soooon.

before using the query having the added 'Time_on_List' calculated field on
it as the control source for my forms, the availability of an 'Advanced
Sort/Filter' button on the customized tool bar i give to the user let the
user remove the filter/sorting instructions by clicking on it to reveal a
query which he was asked to Clear Grid for and then hit the 'Apply' filter
button on the same toolbar; the effect of that was to delete the filter/sort
and allow the pk of the underlying table (the former control source) to have
their way and sort the records by lastname, firstname, mi, medrecnumber.

now, with the query in the place of the table, this feature seems
unavailable to the user. the 'Advanced Sort/Filter' button opens to a query
revealing the filter and sorting criteria but there's no way to remove/them!!!

any theories out there?

Ted,
Add the calculated field to your record source query and
sort by it. As in Select ... , [Date_on_list] - Now() AS
TimeOnList ...
Order by [Date_on_list] - Now()

But keep the control unbound.
Geof.

-----Original Message-----
let me just extend this thread a tad, sandra

can the calculated value be used to sort records either in a form or a
report (say e.g. to list records in descending order of "Time_on_List" when
the value of another control "Outcome" = 'Pending' or is this restricted to
having the "Time_on_List" datum in the table and btw, i'm with you 100% of
the way if your method means getting around the constantly varying updated
fields i wrote about.

ted

ps: what's the big deal with the difference(s) btwn a 'derived' vs a
'calculated' control? i'm relatively 'new' to a2k so it seems like a
meaningless distinction.

In datasheet view or in continuous form this is the nature of an *unbound*
control. An unbound control only has one value even though it may be shown
multiple times when used in the detail section of a continuous form.

Using a *calculated* control, where the ControlSource is the expression, you
will see the correct value for each row.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Ted wrote:
hi,

i didn't know that at the time, but earlier on when i had it as an
unbound field and the user used a datasheet view of the underlying
table to review the data (in edit off mode), the value of the
Time_on_List variable would remain the same throughout the column
until the record pointer moved to another record and thenn the same
value would be displayed for every other record on the screen. i
reasoned this was because of some datasheet artifact that i could
live w/o and added the Time_on_List to my underlying table.

ted

:

Ted,
Sandra Daigle beat me to it. If you make time_on_list an
unbound control then the dirty property won't bother you.
Geof.

.

 

[Sandra Daigle]

Are the buttons (Clear Grid and Apply Filter) missing or are they visable
but disabled? What happens if you click Edit "Delete Columns" or "Clear
Grid"?

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

whooops....i might've spoken a tad toooo soooon.

before using the query having the added 'Time_on_List' calculated
field on
it as the control source for my forms, the availability of an
'Advanced Sort/Filter' button on the customized tool bar i give to
the user let the user remove the filter/sorting instructions by
clicking on it to reveal a query which he was asked to Clear Grid for
and then hit the 'Apply' filter button on the same toolbar; the
effect of that was to delete the filter/sort and allow the pk of the
underlying table (the former control source) to have their way and
sort the records by lastname, firstname, mi, medrecnumber.

now, with the query in the place of the table, this feature seems
unavailable to the user. the 'Advanced Sort/Filter' button opens to a
query revealing the filter and sorting criteria but there's no way to
remove/them!!!

any theories out there?

Ted,
Add the calculated field to your record source query and
sort by it. As in Select ... , [Date_on_list] - Now() AS
TimeOnList ...
Order by [Date_on_list] - Now()

But keep the control unbound.
Geof.

-----Original Message-----
let me just extend this thread a tad, sandra

can the calculated value be used to sort records either in a form or a
report (say e.g. to list records in descending order of "Time_on_List" when
the value of another control "Outcome" = 'Pending' or is this restricted to
having the "Time_on_List" datum in the table and btw, i'm with you 100% of
the way if your method means getting around the constantly varying updated
fields i wrote about.

ted

ps: what's the big deal with the difference(s) btwn a 'derived' vs a
'calculated' control? i'm relatively 'new' to a2k so it seems like a
meaningless distinction.

:

In datasheet view or in continuous form this is the nature of an *unbound*
control. An unbound control only has one value even though it may be shown
multiple times when used in the detail section of a continuous form.

Using a *calculated* control, where the ControlSource is the expression, you
will see the correct value for each row.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Ted wrote:
hi,

i didn't know that at the time, but earlier on when i had it as an
unbound field and the user used a datasheet view of the underlying
table to review the data (in edit off mode), the value of the
Time_on_List variable would remain the same throughout the column
until the record pointer moved to another record and thenn the same
value would be displayed for every other record on the screen. i
reasoned this was because of some datasheet artifact that i could
live w/o and added the Time_on_List to my underlying table.

ted

:

Ted,
Sandra Daigle beat me to it. If you make time_on_list an
unbound control then the dirty property won't bother you.
Geof.



.

 

[Guest]

......

what if i applied the same sort order to my query as i have my compliex pk
doing in the table?

ted

Are the buttons (Clear Grid and Apply Filter) missing or are they visable
but disabled? What happens if you click Edit "Delete Columns" or "Clear
Grid"?

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

whooops....i might've spoken a tad toooo soooon.

before using the query having the added 'Time_on_List' calculated
field on
it as the control source for my forms, the availability of an
'Advanced Sort/Filter' button on the customized tool bar i give to
the user let the user remove the filter/sorting instructions by
clicking on it to reveal a query which he was asked to Clear Grid for
and then hit the 'Apply' filter button on the same toolbar; the
effect of that was to delete the filter/sort and allow the pk of the
underlying table (the former control source) to have their way and
sort the records by lastname, firstname, mi, medrecnumber.

now, with the query in the place of the table, this feature seems
unavailable to the user. the 'Advanced Sort/Filter' button opens to a
query revealing the filter and sorting criteria but there's no way to
remove/them!!!

any theories out there?

Ted,
Add the calculated field to your record source query and
sort by it. As in Select ... , [Date_on_list] - Now() AS
TimeOnList ...
Order by [Date_on_list] - Now()

But keep the control unbound.
Geof.
-----Original Message-----
let me just extend this thread a tad, sandra

can the calculated value be used to sort records either
in a form or a
report (say e.g. to list records in descending order
of "Time_on_List" when
the value of another control "Outcome" = 'Pending' or is
this restricted to
having the "Time_on_List" datum in the table and btw, i'm
with you 100% of
the way if your method means getting around the
constantly varying updated
fields i wrote about.

ted

ps: what's the big deal with the difference(s) btwn
a 'derived' vs a
'calculated' control? i'm relatively 'new' to a2k so it
seems like a
meaningless distinction.

:

In datasheet view or in continuous form this is the
nature of an *unbound*
control. An unbound control only has one value even
though it may be shown
multiple times when used in the detail section of a
continuous form.

Using a *calculated* control, where the ControlSource
is the expression, you
will see the correct value for each row.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Ted wrote:
hi,

i didn't know that at the time, but earlier on when i
had it as an
unbound field and the user used a datasheet view of
the underlying
table to review the data (in edit off mode), the
value of the
Time_on_List variable would remain the same
throughout the column
until the record pointer moved to another record and
thenn the same
value would be displayed for every other record on
the screen. i
reasoned this was because of some datasheet artifact
that i could
live w/o and added the Time_on_List to my underlying
table.

ted

:

Ted,
Sandra Daigle beat me to it. If you make
time_on_list an
unbound control then the dirty property won't bother
you.
Geof.



.

 

[Guest]

hi sandra,

in my custom tooldbar, i have a 'filter by selection', 'filter excluding
selection', 'advanced filter/sort','apply filter', delete record, sort
ascending, sort descending'

so 'clear grid' is missing. is this one i can load onto the toolbar i
cobbled together. before, my user would use the advanced filter/sort and pull
down from something that had 'clear grid' on it and go from there --- or by
removing/deleting the table from the query design window displayed...

ted

Are the buttons (Clear Grid and Apply Filter) missing or are they visable
but disabled? What happens if you click Edit "Delete Columns" or "Clear
Grid"?

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

whooops....i might've spoken a tad toooo soooon.

before using the query having the added 'Time_on_List' calculated
field on
it as the control source for my forms, the availability of an
'Advanced Sort/Filter' button on the customized tool bar i give to
the user let the user remove the filter/sorting instructions by
clicking on it to reveal a query which he was asked to Clear Grid for
and then hit the 'Apply' filter button on the same toolbar; the
effect of that was to delete the filter/sort and allow the pk of the
underlying table (the former control source) to have their way and
sort the records by lastname, firstname, mi, medrecnumber.

now, with the query in the place of the table, this feature seems
unavailable to the user. the 'Advanced Sort/Filter' button opens to a
query revealing the filter and sorting criteria but there's no way to
remove/them!!!

any theories out there?

Ted,
Add the calculated field to your record source query and
sort by it. As in Select ... , [Date_on_list] - Now() AS
TimeOnList ...
Order by [Date_on_list] - Now()

But keep the control unbound.
Geof.
-----Original Message-----
let me just extend this thread a tad, sandra

can the calculated value be used to sort records either
in a form or a
report (say e.g. to list records in descending order
of "Time_on_List" when
the value of another control "Outcome" = 'Pending' or is
this restricted to
having the "Time_on_List" datum in the table and btw, i'm
with you 100% of
the way if your method means getting around the
constantly varying updated
fields i wrote about.

ted

ps: what's the big deal with the difference(s) btwn
a 'derived' vs a
'calculated' control? i'm relatively 'new' to a2k so it
seems like a
meaningless distinction.

:

In datasheet view or in continuous form this is the
nature of an *unbound*
control. An unbound control only has one value even
though it may be shown
multiple times when used in the detail section of a
continuous form.

Using a *calculated* control, where the ControlSource
is the expression, you
will see the correct value for each row.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Ted wrote:
hi,

i didn't know that at the time, but earlier on when i
had it as an
unbound field and the user used a datasheet view of
the underlying
table to review the data (in edit off mode), the
value of the
Time_on_List variable would remain the same
throughout the column
until the record pointer moved to another record and
thenn the same
value would be displayed for every other record on
the screen. i
reasoned this was because of some datasheet artifact
that i could
live w/o and added the Time_on_List to my underlying
table.

ted

:

Ted,
Sandra Daigle beat me to it. If you make
time_on_list an
unbound control then the dirty property won't bother
you.
Geof.



.