Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Macros / August 2004

Tip: Looking for answers? Try searching our database.

Auto Insert Date?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Dunner - 24 Aug 2004 15:53 GMT
Hi,

Is there a way to automatically set a field with the
current date when a user ticks yes in another yes/no
field?

It needs to be a global sort of implementation as the
field can be edited from several different forms.

Thanks for the help

Dunner
Ken Snell [MVP] - 24 Aug 2004 16:15 GMT
Using a macro, you could do this *if* the name of the field (better, an
invisible textbox that is bound to the field) is the same in all the forms.
Macros allow you to drop off the form name if the macro is being called from
a form and you want to use a control or field that is in that form.

MacroName: macSetTodayDate

   Condition:  [NameOfCheckBoxControl] = True
   Action:  SetValue
       Item:  [NameOfTheDateControl]
           Expression:  Date()

   Condition:  [NameOfCheckBoxControl] = False
   Action:  SetValue
       Item:  [NameOfTheDateControl]
           Expression:  Null

The above macro should be run from the After Update property (when the
AfterUpdate event occurs) of the checkbox control on the form. Note that
each form must use the same name for the checkbox control too. This macro
will put today's date in the textbox if the checkbox is checked, and will
put Null in the textbox if the checkbox is unchecked.

A shorter way to do this same macro would be this:

MacroName: macSetTodayDate

   Action:  SetValue
       Item:  [NameOfTheDateControl]
           Expression:  IIf([NameOfCheckBoxControl], Date(), Null)
Signature


       Ken Snell
<MS ACCESS MVP>

> Hi,
>
[quoted text clipped - 8 lines]
>
> Dunner
Dunner - 24 Aug 2004 16:44 GMT
Hi Ken,

Would this update the field with today's date each time
the form is updated (i.e. if I update the checkbox today
the field will read 24/08 but if I update a different
field on the same record say tomorrow the date will be
updated to 25/08)?

The reason I ask is that I need it to be static once the
box is checked so that the date can be compared with
today's date and then various procedures executed when it
reaches a certain age.

Thanks for your help

Dunner

>-----Original Message-----
>Using a macro, you could do this *if* the name of the field (better, an
[quoted text clipped - 27 lines]
>        Item:  [NameOfTheDateControl]
>            Expression:  IIf([NameOfCheckBoxControl], Date(), Null)
Ken Snell [MVP] - 24 Aug 2004 17:23 GMT
The macro that I posted will only update the date value if you click the
checkbox itself. Changing other controls on the form will not affect the
date value.

Signature

       Ken Snell
<MS ACCESS MVP>

> Hi Ken,
>
[quoted text clipped - 53 lines]
> >            Expression:  IIf([NameOfCheckBoxControl],
> Date(), Null)
Dunner - 25 Aug 2004 09:18 GMT
That works perfectly now.

Cheers Ken much appreciated!

Dunner
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.