Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Ontario, Canada
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Drive the status of a checkbox from date field (2000 (any))

    I am trying to drive the status of a checkbox (yes or no) based on the value of several date fields (about 14 of them). The checkbox is an indication of whether or not a project is active or inactive. If these date fields (completion dates of several steps in the project) are ALL filled in, then the checkbox would be empty ("NO"). If ANY date is not filled in, then the checkbox should remain checked ("YES").

    I tried doing this using an If > Then statement for the Form_AfterUpdate event but it is not working. Any hints?

    Thanks in advance!!

    Drew

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Drive the status of a checkbox from date field (2000 (any))

    Drew,

    Since you are using the Form_AfterUpdate event, I am assuming this all occurs on a form. If so, where or how are the 14 date fields being input? Do they reside in a table, text boxes on the form, or other? If in a table, please indicate the table name and format.
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Location
    Ontario, Canada
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drive the status of a checkbox from date field (2000 (any))

    The date fields as well as the checkbox are all on the same form. A user may call up the form to make an update to a date field (when a particular step is complete) and if that is the last date to be completed, then the checkbox, labelled "Active", should toggle to NO (cleared). If any of the date fields in question are still empty, the checkbox should be left alone. The default value for the checkbox in new records is YES as defined in the table behind this form. Basically, this checkbox should only ever toggle off when the last date gets entered in the form.

    I was not the original developer of this database but I have since acquired the responsibility to maintain it since the original author, a co-op student, is no longer with our company.

    Thanks in advance for your help,

    Drew

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Drive the status of a checkbox from date field (2000 (any))

    Drew,

    If I understand you correctly, you have a form where the dates are entered. The data source for the form is a table that holds data. The data would be a field for what is being tracked (i.e., project 1), date fields (14 of them) and then a field for the checkbox status. Is this correct or is a different setup being used? Sorry, I can't help answer the question until I understand how the data resides.
    Regards,

    Gary
    (It's been a while!)

  5. #5
    Star Lounger
    Join Date
    Feb 2001
    Location
    Ontario, Canada
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drive the status of a checkbox from date field (2000 (any))

    HI Gary,

    Yes, that is correct.

    Drew

  6. #6
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Drive the status of a checkbox from date field (2000 (any))

    Drew,

    You have a couple of options here. For this example, I am going to assume that you only have three date fields, D1, D2, and D3.

    Option 1: In lieu of using the checkbox status field from the table, insert a new checkbox on the form and insert the following in the control source of the data tab:
    =IIF([D1]+[D2]+[D3] IS NULL, 0,-1)
    Even though these are date fields, summing any null date value will give you a null value and provide you with the zero (uncheck).

    Option 2: Create an update query that is triggered in the after update event of any of the date fields to update the value of the checkbox field in the table holding the data. The query would be something similar to this:

    - Create a new query based on the table holding the data.
    - Select the field for the checkbox value
    - Change the query type to an update query
    - In the Update To field, insert: IIF(([D1]+[D2]+[D3]) IS NULL, 0,-1)
    - Trigger this query to run anytime a date value is changed on the form along with a Refresh statement.
    This will update the data feeding the form.

    HTH
    Regards,

    Gary
    (It's been a while!)

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drive the status of a checkbox from date field (2000 (any))

    Seeing as you have 14 date fields I thing you should consider looping through the controls and checking the values. I have attached an example you should be able to adapt.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •