Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    triggering event (a2000)

    I found some nifty code to run some calculations on dates using the after update event of the initial date text box. I'm wondering how to trigger the event when the value in the text box isn't being updated. I set the initial date not by entering the value in the text box on the form but by running an update query on the table's date field. By the time it's displayed in a form, the date isn't going to be changing. Any suggestion on how to approach this?

    E

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: triggering event (a2000)

    You'll have to tell us what you want to accomplish.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: triggering event (a2000)

    I want to be able to able to run the function on the value in the initial text box if there is no value on the text box that holds the result of the calculation...I guess I pretty much answered my own question.

    So, would setting up a command button in the header of a continuous form that executed the function if the result text box was null do the deed?

    DaysAhead and DaysBehind are functions in a basDaysAheadOrBehind

    If IsNull ([txtTargetDate]) Then
    If Left([txtInterval], 1) = "-" Then
    Me.txtTargetDate = DaysBehind([Forms]![frmProjToDos]![txtAnchorDate], [Forms]![frmProjToDos]![txtInterval])
    Else
    Me.txtTargetDate = DaysAhead([Forms]![frmProjToDos]![txtAnchorDate], [Forms]![frmProjectToDos]![txtInterval])
    End If
    End If

    E

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: triggering event (a2000)

    Why not create an update query that sets the target date according to the calculation in the records with a blank target date? You'd run this query once. If you run the update query you mentioned in the first post in this thread again, run the new one to, or merge them into one update query.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: triggering event (a2000)

    That's been my current strategy. However, the way I have it set up doesn't handle weekends and holidays and this module does. (Module attached) I don't have the expertise to translate this VBA in SQL so I'm trying to work around my ignorance.

    E
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: triggering event (a2000)

    You don't need to convert the functions to SQL, for you can use VBA functions in Access queries. In an update query, the Update To for the target date field would look like this:

    IIf(Left([Interval], 1) = "-", DaysBehind([AnchorDate], [Interval]), DaysAhead([AnchorDate], [Interval]))

    or

    IIf([Interval] < 0, DaysBehind([AnchorDate], [Interval]), DaysAhead([AnchorDate], [Interval]))

    where AnchorDate and Interval are the names of the fields in the table. The Criteria for the target date would be Is Null, so that you update only records for which the target date is blank.

Posting Permissions

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