Creating a function to autofill dates based on click from Yes/No box

[jmitchelldueck]

Hi,

I have a form that tracks when tasks are completed. In order to make it easier to use, I am using check boxes that autofill a date field when they are clicked. However, on this one form alone, I have 15 separate tasks and feel there must be a better way to write the VBA code (in the form of a function that would take the relevant check box and date box as arguments) so it doesn't exist as separate bits of code. What I have for one set of controls is this:

Private Sub Check167_Click()
If Me.Check167 = -1 Then
    Me.Ctl5_3.ForeColor = vbGreen
Else
    Me.Ctl5_3 = ""
End If
End Sub

I would appreciate any input on this. Furthermore, the check boxes are not linked to any tables, so in order to display that they have previously been checked off, I have this to auto-check the controls when the form opens:

Private Sub Form_Load()


If IsNull(Ctl5_1) Then
    Me.Check161 = 0
Else
    Me.Check161 = -1
End If

End Sub

I know it is it not ideal to have something on a form not saved in a table, but that's the way I have it set up. I'm looking for the same thing here; a function that I can use in a For loop on the form load. A thought I had was to use the index number of each control, but I don't know where to find it and everything else I have read indicates it is a bad idea to do so.

Thanks,
jmd

[ranman256]

Run an update query the change all the fields to true (or false)
this will set the checkboxes.

(unless your checkboxs are not bound...which they should be)

[Missinglinq]

First off, your posted code has nothing to do with your title

"Creating a function to autofill dates based on click from Yes/No box "

The posted code does nothing to populate a date anywhere!

Secondly, if your Checkboxes are Unbound, as you indicated, ( the check boxes are not linked to any tables) they will always be unchecked, when the Form opens; that's how Unbound Controls work.

Linq ;0)>

[jmitchelldueck]

Hi missinglinq. I must have copied and pasted in an incomplete set of code! Here it is:

Private Sub Check161_afterupdate()
If Me.Check161 = -1 Then
    Me.Ctl5_1 = Now()
    Me.Ctl5_1.ForeColor = vbGreen
Else
    Me.Ctl5_1 = ""
End If
End Sub

As for the unbound checkboxes, the second batch of code I posted above is what I'm using to automatically check them:

Private Sub Form_Load()


If IsNull(Ctl5_1) Then
    Me.Check161 = 0
Else
    Me.Check161 = -1
End If
End Sub

This checks to see if my date field (Ctl5_1) is empty - if so the Checkbox remains unchecked. If not, it checks it automatically. What I am looking for is a function that would do the same for every combination of Checkbox and date field on the form, without me having to create separate code for every single one of them.

Let me know if you know of any way to cycle through the combinations like that. Thanks.

[June7]

Code can loop through form's Controls Collection but that means the code will look at every single control on the form.

Alternative is to give controls similar names, like chk1, chk2, tbxDate1, tbxDate2, etc. Then use looping structure that looks at only those controls:

For x = 1 to 15
If Me("chk" & x) = True Then
Me("tbxDate" & x) = Now()
...

If textbox is bound to date/time field, don't set to empty string, set to Null.

[Missinglinq]

Also note that by having your code in the Form_Load event, the Checkbox in every Record is going to be set according to the status of Ctl5_1 in the first Record that is displayed, when the Form opens! In order for this to be Record-appropriate, the code needs to be in the Form_Current event, instead of in Form_Load... and this will only work in a Single View Form.

In a Continuous View Form the Checkbox in all Records will be set according to the status of Ctl5_1 in the Current Record, at any point in time.

Linq ;0)>

[jmitchelldueck]

Thanks Linq, I discovered the Form_Current issue quickly enough! I'm using single view forms and went with June's solution.