Me.UpdatedOn = Now() (How To Modify)

  • Thread starter Thread starter Steve Crowhurst
  • Start date Start date
S

Steve Crowhurst

This is very helpful and has certainly done the job, but
how do I adapt the event procedure to allow this to be
used in more than one form in the same database?

I tried it and I get a "compile error"
I presume I need to refer to a specfic form as opposed
to "Me".UpdatedOn = Now() At a guess it's something like
Forms!frmPrimarySubForm etc... Any help would be greatly
appreciated, thank you guys.
 
Definitely, you can only refer to

Me.UpdatedOn = Now()

if the form you are working on ("Me") contains this data field, UpdatedOn.
If it does not and you still want to update the UpdatedOn field in some
table, you can simply execute and SQL statement like this from anywhere
in your code:

DoCmd.RunSQL "UPDATE MyTable SET UpdatedOn = #" & CStr(Now()) & "# WHERE
Criteria = XXX"

setting the criteria properly to select the record for which you want to
reset the UpdatedOn time stamp.
You could refer to UpdatedOn via other forms and subforms, but what if
they are not opened, either? As long as you can identify the record you
need, this method will always work.

Pavel
 
I don't understand,are you saying that this will work even
if I have or have not got a field [UpdatedOn]. Will this
method update all those records have have been updated
without me needing to specify criteria, Criteria will =
all records that have updated, how is this written is an
SQL statement.
 
No, No! Of course you need the field [UpdatedOn] in your table. It just
doesn't have to be in the form's record source. What i am trying to say
is this.
When you used Me.UpdatedOn = Now() in a form, that form clearly had
[UpdateOn] field in its data source in order for the statement to work.
If I understood you correctly, you now want to set UpdatedOn from
another form, which does not have this field in its data source, and you
were going to refer to UpdatedOn using Forms!frmPrimarySubForm... etc
to get to that specific record containing UpdatedOn that you want to change.
What i proposed instead is that you find the record using some criteria,
then use SQL to update the field you need to set to Now().
I apologize if I didn't understand your need correctly.
Cheers,
Pavel


I don't understand,are you saying that this will work even
if I have or have not got a field [UpdatedOn]. Will this
method update all those records have have been updated
without me needing to specify criteria, Criteria will =
all records that have updated, how is this written is an
SQL statement.
-----Original Message-----
Definitely, you can only refer to

Me.UpdatedOn = Now()

if the form you are working on ("Me") contains this data field, UpdatedOn.
If it does not and you still want to update the UpdatedOn field in some
table, you can simply execute and SQL statement like this from anywhere
in your code:

DoCmd.RunSQL "UPDATE MyTable SET UpdatedOn = #" & CStr(Now ()) & "# WHERE
Criteria = XXX"

setting the criteria properly to select the record for which you want to
reset the UpdatedOn time stamp.
You could refer to UpdatedOn via other forms and subforms, but what if
they are not opened, either? As long as you can identify the record you
need, this method will always work.

Pavel


.
Click to expand...
Click to expand...
 
Pavel,

Thank you for your ideas but, I don't think I too have
expressed my requirement very well, I'll try and
isllustrate the requirement in a clearer fashion, here
goes.
I am developing an application for a group of user's, and
there is a requirement that a field be included in every
table, that is able to record exactly when each record is
updated or created (New Record)- this field might be
called [UpdatedOn] for example. Question, how am I able
to acheive this goal and the answer must be able to be
used in several forms in the same database. I have tried
using - Me.UpdatedOn = Now() - but this only seems to
work on one form in database, does anybody know of a way
to modify the event procedure to reference other forms in
the same database ie Forms!frmPrimarySubForm[UpdatedOn =
Now() or something similar. Thank you all for your time
and effort.




-----Original Message-----
No, No! Of course you need the field [UpdatedOn] in your table. It just
doesn't have to be in the form's record source. What i am trying to say
is this.
When you used Me.UpdatedOn = Now() in a form, that form clearly had
[UpdateOn] field in its data source in order for the statement to work.
If I understood you correctly, you now want to set UpdatedOn from
another form, which does not have this field in its data source, and you
were going to refer to UpdatedOn using Forms! frmPrimarySubForm... etc
to get to that specific record containing UpdatedOn that you want to change.
What i proposed instead is that you find the record using some criteria,
then use SQL to update the field you need to set to Now ().
I apologize if I didn't understand your need correctly.
Cheers,
Pavel


[email protected] said:
I don't understand,are you saying that this will work even
if I have or have not got a field [UpdatedOn]. Will this
method update all those records have have been updated
without me needing to specify criteria, Criteria will =
all records that have updated, how is this written is an
SQL statement.
-----Original Message-----
Definitely, you can only refer to

Me.UpdatedOn = Now()

if the form you are working on ("Me") contains this
Click to expand...
data
field, UpdatedOn.
If it does not and you still want to update the
Click to expand...
UpdatedOn
field in some
table, you can simply execute and SQL statement like
Click to expand...
this
from anywhere
in your code:

DoCmd.RunSQL "UPDATE MyTable SET UpdatedOn = #" & CStr
Click to expand...
(Now
()) & "# WHERE
Criteria = XXX"

setting the criteria properly to select the record for which you want to
reset the UpdatedOn time stamp.
You could refer to UpdatedOn via other forms and subforms, but what if
they are not opened, either? As long as you can
Click to expand...
identify
the record you
need, this method will always work.

Pavel

Steve Crowhurst wrote:

This is very helpful and has certainly done the
Click to expand...
job,
but
how do I adapt the event procedure to allow this to be
used in more than one form in the same database?

I tried it and I get a "compile error"
I presume I need to refer to a specfic form as opposed
to "Me".UpdatedOn = Now() At a guess it's something like
Forms!frmPrimarySubForm etc... Any help would be greatly
appreciated, thank you guys.
.
Click to expand...
Click to expand...
.
Click to expand...
 
I wasn't far off after all, I guess.
You can do it almost exactly as you yourself said, by putting something
like this in your VBA code:

Forms!frmMain.SubForm!UpdatedOn = Now()

provided frmMain is open.
Me!UpdatedOn = Now() will work for every form that has UpdatedOn field
in its record source.
It seems that you need to update these fields in different tables, so I
think you might as well include UpdatedOn into the record source for
every form you use, and use the simpler Me!UpdatedOn = NoW().

Good luck,
Pavel
Pavel,

Thank you for your ideas but, I don't think I too have
expressed my requirement very well, I'll try and
isllustrate the requirement in a clearer fashion, here
goes.
I am developing an application for a group of user's, and
there is a requirement that a field be included in every
table, that is able to record exactly when each record is
updated or created (New Record)- this field might be
called [UpdatedOn] for example. Question, how am I able
to acheive this goal and the answer must be able to be
used in several forms in the same database. I have tried
using - Me.UpdatedOn = Now() - but this only seems to
work on one form in database, does anybody know of a way
to modify the event procedure to reference other forms in
the same database ie Forms!frmPrimarySubForm[UpdatedOn =
Now() or something similar. Thank you all for your time
and effort.
-----Original Message-----
No, No! Of course you need the field [UpdatedOn] in your table. It just
doesn't have to be in the form's record source. What i am trying to say
is this.
When you used Me.UpdatedOn = Now() in a form, that form clearly had
[UpdateOn] field in its data source in order for the statement to work.
If I understood you correctly, you now want to set UpdatedOn from
another form, which does not have this field in its data source, and you
were going to refer to UpdatedOn using Forms! frmPrimarySubForm... etc
to get to that specific record containing UpdatedOn that you want to change.
What i proposed instead is that you find the record using some criteria,
then use SQL to update the field you need to set to Now ().
I apologize if I didn't understand your need correctly.
Cheers,
Pavel


[email protected] said:
I don't understand,are you saying that this will work even
if I have or have not got a field [UpdatedOn]. Will this
method update all those records have have been updated
without me needing to specify criteria, Criteria will =
all records that have updated, how is this written is an
SQL statement.
-----Original Message-----
Definitely, you can only refer to

Me.UpdatedOn = Now()

if the form you are working on ("Me") contains this data
field, UpdatedOn.
If it does not and you still want to update the UpdatedOn
field in some
table, you can simply execute and SQL statement like this
from anywhere
in your code:

DoCmd.RunSQL "UPDATE MyTable SET UpdatedOn = #" & CStr (Now
()) & "# WHERE
Criteria = XXX"

setting the criteria properly to select the record for
which you want to
reset the UpdatedOn time stamp.
You could refer to UpdatedOn via other forms and
subforms, but what if
they are not opened, either? As long as you can identify
the record you
need, this method will always work.

Pavel

Steve Crowhurst wrote:

This is very helpful and has certainly done the job,
but
how do I adapt the event procedure to allow this to be
used in more than one form in the same database?

I tried it and I get a "compile error"
I presume I need to refer to a specfic form as opposed
to "Me".UpdatedOn = Now() At a guess it's something like
Forms!frmPrimarySubForm etc... Any help would be greatly
appreciated, thank you guys.
.
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top