Date Field Update question

[elektrik]

Hi all. I have a database that I want to change the contents of one field based on the selection of a certain value. The first field is a combo box that has several choices ("New";"Open";"Closed"...). This is my "Master" field-to which the slave field should reflect changes. The second field should have the current date and time inserted into it when "Closed" is selected in the "Master" field.

My question is this: Although I've gotten this to work with a Macro, the macro itself only functions correctly if I put it in the "OnCurrent" Event for the Form. I was thinking I could get the macro to work on the field. Why does the macro only work at the "OnCurrent" event and not on the individual field on the form?

Thanks in advance!

Jerrold Horgan

 

[Pat Hartman]

You don't need a macro for this. The code is very simple. In the BeforeUpdate event of the form put the following:

If me.YourCombo = "Closed" Then
me.YourDate = Now()
end if

It is also possible to put this code in the AfterUpdate event of the combobox. This will also work fine if the date/time field only contains data when the status is "Closed". You'll need to modify the code slightly however to take care of the possibility that the status is changed more than once.

If me.YourCombo = "Closed" Then
me.YourDate = Now()
Else
me.YourDate = Null
end if

 

[elektrik]

Thanks!