triggering event (a2000)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote