SetValue ?

[Xavier]

How can I make access stamp date & time on a field in a
table when a user checks a yes/no box.

I have a form with a table when a user receives an item
it will stamp the date&time in a field, when he finishes
working on this item he will open the record and check a
box stating he finished, well I have another field for
date&time completed and I want this field stamped with
system time and date when he checks the complete box.

I tried a macro with setvalue then in the item I put the
field name i want stamped, in expression I put =Now()
then I call this macro from the form in the check box
properties when it is clicked.

when I do this i get a msg "The expression you entered
containes invalid syntax", you may have entered an
operator without an operand.

Is there an easy way to do this im using access 2002

Thanks
Xavier

 

[Steve Schapel]

Xavier,

You shouldn't have the = in the expression. Just use
Now()

You might also want to consider using the AfterUpdate event rather than
the Click event, and possibly using a macro Condition to enforce that
the Date and Time don't get updated if the checkbox is clicked multiple
times.

 

[Xavier]

Steve

Thanks for your reply, I had already tried without the =
sign, when I do and I check the box I get the following
msg.
"The Object doesn't contain the automation object "my
table name" this is i bold then under it it says
"You tried to run a visual basic procedure to set a
property or method for an object. However, the component
doesn't make the property or method available for
automation operations" I then select OK and the next msg
says
"Action Failed where it gives Macro Name, Condition,
Action Name and argument "argument is as follows [my
table name]![field name],NOW()" here my only option is to
halt the macro.

In the above action failed msg where I wrote the argument
if I leave the = sign then instead of saying "NOW() it
would say the system time.

I have done this before but its been quite a while and Im
sure it's written correctly but im missing something.

Any help is greatly appreciated!!
Thanks
Xavier

 

[Steve Schapel]

Xavier,

What do you have entered as the Item argument of the SetValue macro
action? I had assumed it was the name of the control on the form where
you want the date/time recorded?

 

[Steve Schapel]

Xavier,

I do not understand what [Vehicle Extraction Log]![Date Completed] is
supposed to refer to. In your explanation, you did not mention anything
named Vehicle Extraction Log, nor anything named Date Completed.
However, assuming Date Completed is the name of the control on the
subform, and the checkbox activating the macro is also on the same
subform, I don't think you will need anything in the Item argument
except [Date Completed]. It seems to me that there is nothing wrong
with the basic concept you are trying to put into practice, but the
problem relates to your use of the Item argument in the macro.

 

[Xavier]

Steve

Sorry those names are the table name and field name I
forgot to lable them accordingly.

Your assumption was correct the date completed is the
control (field to store the system date created by the
macro) and it is in the same table and form where the
check box is.

Well I brough a copy of the DB home with little data and
surprizingly I got it to work. It was something we all
hate a very small typo error, I feel so bad about it.

Last thing and Ill try not to bother you any more.

How can I keep it from changing once it has been checked
and the field was stamped with a date to avoid it being
changed at a later date.

thanks
Xavier

-----Original Message-- ---
Xavier,

I do not understand what [Vehicle Extraction Log]![Date Completed] is
supposed to refer to. In your explanation, you did not mention anything
named Vehicle Extraction Log, nor anything named Date Completed.
However, assuming Date Completed is the name of the control on the
subform, and the checkbox activating the macro is also on the same
subform, I don't think you will need anything in the Item argument
except [Date Completed]. It seems to me that there is nothing wrong
with the basic concept you are trying to put into practice, but the
problem relates to your use of the Item argument in the macro.

--
Steve Schapel, Microsoft Access MVP

Steve

In the Item I have entered:
[Vehicle Extraction Log]![Date Completed]
In the Expression I have:
NOW() "this would give me date and time stamped in the
same field"

In the property of the check box I tried calling it from
the following:
beforeupdate, afterupdate, on click, and on focus

I did notice that if I used beforeupdate the check would
disappear after I clicked Halt in the error window.

If i used it in afterupdate, on click, or on focus I
would just get the action error I described earlier.

My main form is [frmVehicleExtractionLog]
My Subform name is [subVehicleExtractionLog]
My table mname is [tblvehicleextractionlog]
My field name is [whenCompleted] in above tbl
My check box is [whencompleted] in above tbl

Field and check are in above tbl which are in above
subform which is in a main form where I link it to the
main database where our case numbers are for reference.

I really am apreciative of your help I read many of the
postings and find solutions to issues in many of them,
there are a number of postings regarding the setvalue in
a macro and all say the same and write it as I have some
even have your name in them but for some reason it will
not work, I even took a sample from the MS site where
they apply it to the NWTraders DB and it worked there but
it will not work in mine.

I hate to ask but would it be possible for you to put the
action in a mdb with just the two fields "complete and a
check box" and trigering the date time by the check box
giving me the alternative you mentioned earlier where
once it is stamped it will not change if the box is
checked repeatedly.

If you decide to send it to me use the following address
notice I changed the @ sign for a number 2 and eliminated
the .com

xaviervp2aol

Thanks in advance
Xavier

.

 

[Steve Schapel]

Xavier,

If you mean the date/time stamp will not be re-entered if the check box
is clicked again, you can put a Condition in the macro for the SetValue
action... this should do it:
[Date Completed] Is Null

If you mean you would also want to prevent manual editing of the Date
Completed field, I guess the easiest way would be to set the properties
of the textbox on the form, Locked = Yes and Enabled = No.

 

[Simon]

Hi Steve and Xavier

I happened to have read your e-mail exchanges where it got
my attention on the error mssg which Xavier has mentioned:

"The object doesn't contain the Automation object "Form"
You tried to run a Visual Basic procedure to set ......"

I received same error mssg everytime I ran the setvalue
macro where i put as argument : [Form]![Form name]!
[Command1].[Forecolor] (Command1 is a button) I have
posted this problem on the news group for week but no-one
is anwsering until today.

So I followed Steve's suggestion to remove from argument
[Form]![Form name]! and leave only the [Command1].
[Forecolor], and the macro works, and my problem solved.

What is interesting is, for all above is done on my Access
2000. But when I tried the same on my Access 2003
software (I newly purchased Access 2003), and use the full
syntex in the setvalue macro ([Form]![Form name]!
[Command1].[Forecolor]), it works, i.e., no need to remove
the [Form]![Form name].

So Steve, do you have any idea about the situation
mentioned above?


Best regards
Simon

-----Original Message-----
Xavier,

I do not understand what [Vehicle Extraction Log]![Date Completed] is
supposed to refer to. In your explanation, you did not mention anything
named Vehicle Extraction Log, nor anything named Date Completed.
However, assuming Date Completed is the name of the control on the
subform, and the checkbox activating the macro is also on the same
subform, I don't think you will need anything in the Item argument
except [Date Completed]. It seems to me that there is nothing wrong
with the basic concept you are trying to put into practice, but the
problem relates to your use of the Item argument in the macro.

--
Steve Schapel, Microsoft Access MVP

Steve

In the Item I have entered:
[Vehicle Extraction Log]![Date Completed]
In the Expression I have:
NOW() "this would give me date and time stamped in the
same field"

In the property of the check box I tried calling it from
the following:
beforeupdate, afterupdate, on click, and on focus

I did notice that if I used beforeupdate the check would
disappear after I clicked Halt in the error window.

If i used it in afterupdate, on click, or on focus I
would just get the action error I described earlier.

My main form is [frmVehicleExtractionLog]
My Subform name is [subVehicleExtractionLog]
My table mname is [tblvehicleextractionlog]
My field name is [whenCompleted] in above tbl
My check box is [whencompleted] in above tbl

Field and check are in above tbl which are in above
subform which is in a main form where I link it to the
main database where our case numbers are for reference.

I really am apreciative of your help I read many of the
postings and find solutions to issues in many of them,
there are a number of postings regarding the setvalue in
a macro and all say the same and write it as I have some
even have your name in them but for some reason it will
not work, I even took a sample from the MS site where
they apply it to the NWTraders DB and it worked there but
it will not work in mine.

I hate to ask but would it be possible for you to put the
action in a mdb with just the two fields "complete and a
check box" and trigering the date time by the check box
giving me the alternative you mentioned earlier where
once it is stamped it will not change if the box is
checked repeatedly.

If you decide to send it to me use the following address
notice I changed the @ sign for a number 2 and eliminated
the .com

xaviervp2aol

Thanks in advance
Xavier

.

 

[Xavier]

Steve

Thank you, that did it now once it is set only I can go
back and manualy edit it.

Have a wonderful weekend.

Xavier

-----Original Message-----
Xavier,

If you mean the date/time stamp will not be re-entered if the check box
is clicked again, you can put a Condition in the macro for the SetValue
action... this should do it:
[Date Completed] Is Null

If you mean you would also want to prevent manual editing of the Date
Completed field, I guess the easiest way would be to set the properties
of the textbox on the form, Locked = Yes and Enabled = No.

--
Steve Schapel, Microsoft Access MVP

Steve

Sorry those names are the table name and field name I
forgot to lable them accordingly.

Your assumption was correct the date completed is the
control (field to store the system date created by the
macro) and it is in the same table and form where the
check box is.

Well I brough a copy of the DB home with little data and
surprizingly I got it to work. It was something we all
hate a very small typo error, I feel so bad about it.

Last thing and Ill try not to bother you any more.

How can I keep it from changing once it has been checked
and the field was stamped with a date to avoid it being
changed at a later date.

thanks
Xavier

.

 

[Steve Schapel]

Simon,

The full reference to the command button needs a 's' on Forms, i.e.
[Forms]![Form name]![Command1].[Forecolor]