Update other fields on same form when checkbox is selected

[bibbyd01]

Hi all

I have a form which displays multiple items. I have a checkbox to indicate whether the item (an order) has been invoiced. If this is selected I want two other fields to be updated, the user and the date/time.

I'd like this to happen on the form instantly if possible into fields the user can't edit, or in the background (set as not visible).

I've had a go at the below code and entered it in to the afterupdate for the checkbox. It doesn't work, hence why I'm now here! The fields are tblWIPcomments_billed, which is the checkbox, tblWIPcomments_billedon which is the date field and tblWIPcomments_billedby which is the user field. I also have a procedure called getuser which finds and stores the current user.

Private Sub tblWIPcomments_billed_AfterUpdate()
If (Me.tblWIPcomments_billed) = "yes" Then
Me.tblWIPcomments_billedon = Now()
End If
End Sub

 

[pr2-eugin]

You are trying to compare the Checkbox to a Yes String.. But it should be normally compared against constants like True/False or -1/0.. That is more relaiable..
You can also use Enabled instead of Visible.. I prefer Enabled.. but it is upto you..

Private Sub tblWIPcomments_billed_AfterUpdate()
    If Me.tblWIPcomments_billed = -1 Then
        Me.tblWIPcomments_billedon = Now()
        'Me.tblWIPcomments_billedon.Enabled = False
        Me.tblWIPcomments_billedon.Visible = False
        Me.tblWIPcomments_billedby = getUser()
        'Me.tblWIPcomments_billedby.Enabled = False
        Me.tblWIPcomments_billedby.Visible = False
    End If
End Sub

The above code will work.. Now we need to make it a bit more robust.. What if they Select and then Unselect? The values will not be updated.. i.e. once selected it will always hold the value billedOn and nilledBy information as the Now() and User Name.. Think about how you can work on that.. To be extra sure.. you can use the same method in the Befor Update event of the Form that way.. Whatever the final changes will be captured.. Hope that makes sense..