How to clear Date Field if another field is null

[Rossco73]

I have a form which has (Signed_Out) & (Allocated_Date_Time) Fields. I am attempting to clear the allocated_Date_Time field when I clear the Signed_Out.

I currently have below code to auto insert date/time on entry f Signed_Out which is working well. How do I add to this in order to do above request?

Private Sub Signed_Out_AfterUpdate()
    Me.Allocated_Date_Time = Now()
End Sub

Thanks Ross

[twinnyfo]

Rossco73,

Welcome to Bytes!

First, some requirements, then some advice, then a proposed solution.

Requirement: Please use code tags when you post code to the forum (the [CODE/] button in your post editor).

Advice: It is always highly recommended that you rename the controls on your form to something other than "merely" the underlying field name. Yes, this is a default of MS access, but I think MS has failed here. When you have a text box named Allocated_Date_Time that refers to an underlying field with the same name, when you refer to this field/control, this can be ambiguous for the DB. You always want to be explicit, not vague. So, I would recommend you rename these controls to txtAllocated_Date_Time and chkSigned_Out (I am making an assumption that this is a Yes/No field and you have a Check Box control--based upon context it would make sense. If not, then we will have to modify the proposed solution below.

Proposed Solution: It appears that when you check out an item, you are recording the date it is checked out. Then, when it is checked back in, you clear out the date. Again, assumptions, because you have provided very little information on this. So, if this is the case, then you want to check the value of your chkSigned_Out control to determine what you are going to do with the date control. See below:

Private Sub chkSigned_Out_AfterUpdate()
    Me.txtAllocated_Date_Time = _
        IIf(chkSigned_Out, _
            Now(), _
            Null)
End Sub

As you can see, this code assiigns a value to txtAllocated_Date_Time, but it first checks the value of your Check Box. If the check box is checked, it assigned the current Date/Time. If it is unchecked, it clears the text box.

Hope this hepps.