Unbound checkbox in subform datasheet

[Guest]

On the form load event of one of my subforms, I want to loop through the
recordset, and set the value of an unbound checkbox for each record in the
subform. I tried this, where bit2 is the name of the unbound checkbox:

Private Sub Form_Load()
Dim ds As Recordset
Set ds = [Form_Commitment subform].Recordset
While Not ds.EOF
If (ds("bit1date") <> "") Then
bit2 = True
End If
ds.MoveNext
Wend
End Sub

However, the line bit2 = True checks all of the checkboxes in the entire
column. Is there a way for me to reference the checkbox only in the current
record if the checkbox is not bound?

Thanks,
Pete

 

[Allen Browne]

You cannot have a different value in an unbound control on different rows of
a datasheet or continuous form.

Can you base this subform on a query, with a calculated field in the query?
You can then bind the check box to the calculated field, and it will display
the correct results.

 

[Guest]

Good idea. I tried it out like so, where ds("bit3") is a calculated field
(=False).

Private Sub Form_Load()
Dim ds As Recordset
Set ds = [Form_Commitment subform].Recordset
While Not ds.EOF
If (ds("bit1date") <> "") Then
ds.Edit
ds("bit3") = True
ds.Update
End If
ds.MoveNext
Wend
ds.MoveFirst
End Sub

However, now it tells me that the field (bit3) cannot be updated. Is it
that I can't update a calculated field, or is that I'm trying to set the
value of my new bound checkbox incorrectly.

Thanks for the help,
Pete

You cannot have a different value in an unbound control on different rows of
a datasheet or continuous form.

Can you base this subform on a query, with a calculated field in the query?
You can then bind the check box to the calculated field, and it will display
the correct results.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

On the form load event of one of my subforms, I want to loop through the
recordset, and set the value of an unbound checkbox for each record in the
subform. I tried this, where bit2 is the name of the unbound checkbox:

Private Sub Form_Load()
Dim ds As Recordset
Set ds = [Form_Commitment subform].Recordset
While Not ds.EOF
If (ds("bit1date") <> "") Then
bit2 = True
End If
ds.MoveNext
Wend
End Sub

However, the line bit2 = True checks all of the checkboxes in the entire
column. Is there a way for me to reference the checkbox only in the
current
record if the checkbox is not bound?

Thanks,
Pete

 

[Guest]

Aha! After further thought, I see what you mean. You mean to not use the
Form_Load event at all, to bind to the calculated field, and let the
calculated field do all the work. If that is indeed what you meant, it
worked.

Thanks again,
Pete

You cannot have a different value in an unbound control on different rows of
a datasheet or continuous form.

Can you base this subform on a query, with a calculated field in the query?
You can then bind the check box to the calculated field, and it will display
the correct results.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

On the form load event of one of my subforms, I want to loop through the
recordset, and set the value of an unbound checkbox for each record in the
subform. I tried this, where bit2 is the name of the unbound checkbox:

Private Sub Form_Load()
Dim ds As Recordset
Set ds = [Form_Commitment subform].Recordset
While Not ds.EOF
If (ds("bit1date") <> "") Then
bit2 = True
End If
ds.MoveNext
Wend
End Sub

However, the line bit2 = True checks all of the checkboxes in the entire
column. Is there a way for me to reference the checkbox only in the
current
record if the checkbox is not bound?

Thanks,
Pete

 

[Allen Browne]

Yes, that's the idea. Good news.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Aha! After further thought, I see what you mean. You mean to not use the
Form_Load event at all, to bind to the calculated field, and let the
calculated field do all the work. If that is indeed what you meant, it
worked.

Thanks again,
Pete

You cannot have a different value in an unbound control on different rows
of
a datasheet or continuous form.

Can you base this subform on a query, with a calculated field in the
query?
You can then bind the check box to the calculated field, and it will
display
the correct results.

On the form load event of one of my subforms, I want to loop through
the
recordset, and set the value of an unbound checkbox for each record in
the
subform. I tried this, where bit2 is the name of the unbound checkbox:

Private Sub Form_Load()
Dim ds As Recordset
Set ds = [Form_Commitment subform].Recordset
While Not ds.EOF
If (ds("bit1date") <> "") Then
bit2 = True
End If
ds.MoveNext
Wend
End Sub

However, the line bit2 = True checks all of the checkboxes in the
entire
column. Is there a way for me to reference the checkbox only in the
current
record if the checkbox is not bound?

Thanks,
Pete