Access 2003: Setting up an automatic end date

[mattia]

Hi, I'm very new to Access...my company decided we need to have a database and I'm somehow trying to get my arms around this...

I'm using a 2003 version of Microsoft Access running on Windows Vista. I would like to know how I can set up an automatic end date. I'll explain:

I have a 'task' table which, among others, includes two fields: Active and End Date. The "Active" field is a simple Yes/No data type; when I check off the Active box, the task disappear from a related 'task' form. I would like Access to generate automatically an End Date every time I check off an Active box. Obviosuly, the end date should be current day/month/year and should appear in the "End Date" field.

How can I do this?

I've browsed the website looking for similar posts, but haven't found any. If I've missed it, it would be great if you could point me to that.

Many thanks for your help!
Mattia

[nico5038]

I would change the Active Yes/No into a Start date, this will enable the calculation of the duration of a task in the future.

For both dates I would use a [<Now] button to put the current date into the field.
Just put in the OnClick event of the button:

Me.[End Date] = Date()
 

Nic;o)

[PianoMan64]

Hi, I'm very new to Access...my company decided we need to have a database and I'm somehow trying to get my arms around this...

I'm using a 2003 version of Microsoft Access running on Windows Vista. I would like to know how I can set up an automatic end date. I'll explain:

I have a 'task' table which, among others, includes two fields: Active and End Date. The "Active" field is a simple Yes/No data type; when I check off the Active box, the task disappear from a related 'task' form. I would like Access to generate automatically an End Date every time I check off an Active box. Obviosuly, the end date should be current day/month/year and should appear in the "End Date" field.

How can I do this?

I've browsed the website looking for similar posts, but haven't found any. If I've missed it, it would be great if you could point me to that.

Many thanks for your help!
Mattia

Mattia,

Simply put, you'd have to set the value for the field EndDate when the value of Active is set to true.

I'm assuming first of all that you have this table displayed in a form, and you're not updating the table directly in the table view?

I'm also assuming that if you are updating the information, that you're using a continuous form, or a form that has the "Active" field in it, and may or may not have the "EndDate" field on that form.

If either of those are true then here's what you need to do:

1. Go into design mode of the form
2. right-click on the CheckBox and select Properties.
3. Locate the "On Change" Event from the list of the available events
3. Then click on the ... button that is on the far right of Properties window, and select Code Builder.
4. You will then see a empty subroutine for "Active_OnChange" Event.
5. In the middle of that copy and paste the following code:

 
          If me.active = True Then
               me.EndDate = Date
          Else
               me.EndDate = Null
          End If
 
 

[mattia]

PianoMan64,

Thanks, your answer is very clear. However, I'm such a begineer and I have a question: in the "Event" tab of the properties I have various "on change" items:
On Data Change, on Data Set Change, On PivotTable Change, On Selection Change, on View Change...which one is the one you are referring to?

Also, just to make sure we are on the same page, once I implement the new code, where will the End Date appear? On the Table? Not on the Form, since when the task is completed (i.e.: the Active box is checked off) the task disappear from the Form...

Many thanks!
Mattia

[PianoMan64]

PianoMan64,

Thanks, your answer is very clear. However, I'm such a begineer and I have a question: in the "Event" tab of the properties I have various "on change" items:
On Data Change, on Data Set Change, On PivotTable Change, On Selection Change, on View Change...which one is the one you are referring to?

Also, just to make sure we are on the same page, once I implement the new code, where will the End Date appear? On the Table? Not on the Form, since when the task is completed (i.e.: the Active box is checked off) the task disappear from the Form...

Many thanks!
Mattia

Hey Mattia,

The reason that you have all those choices is because when you right-clicked, you right-clicked on the form and not the check box control. If you will right-click on the check box control instead of some place on the form, then you will see the only change option that I'm speaking of.

That should clear up any confusion that you're having on the form.

You'll also notice that in the upper-left hand corner of the properties window, you'll see the name's of the controls that are on the current form, and well as other control names like (FORM, DETAIL) and maybe, FORMHEADER, FORMFOOTER, PAGEHEADERSECTION, PAGEFOOTERSECTION). These are for controls that are on the form that have events associated with them as well.

Since we're dealing with only the check box control, you need to make sure that when you right-click, that it is on the check box itself or use the pull-down menu in the properties window to select the name of the check box control.

I hope that helps,

Joe P.

[mattia]

Thanks! I'll try this out!

M