Check only one checkbox in a subform

[DJJ]

I have a sub-form that displays as a continuous-form a field and a checkbox.
I want to be able to check only ONE checkbox that appears as rows on the
sub-form. Making that particular record a default record. I found the
following code on www.tek-tips.com to do this that runs in the BeforeUpdate
event of the checkbox and fires an update query that sets the checkbox to
"No".

Private Sub chkAssign_BeforeUpdate(Cancel As Integer)
DoCmd.OpenQuery "qryUpdateRoutingAssign", acViewNormal
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acRefresh, , acMenuVer70
End Sub

When I ran this code a get the runtime error 2115:
"The macro or function set to the BeforeUpdate or ValidationRule property
for this field is preventing Access from saving the data in the field."

Is there a work around this or is this the wrong approach?

Thx

DJ

 

[Marshall Barton]

I have a sub-form that displays as a continuous-form a field and a checkbox.
I want to be able to check only ONE checkbox that appears as rows on the
sub-form. Making that particular record a default record. I found the
following code on www.tek-tips.com to do this that runs in the BeforeUpdate
event of the checkbox and fires an update query that sets the checkbox to
"No".

Private Sub chkAssign_BeforeUpdate(Cancel As Integer)
DoCmd.OpenQuery "qryUpdateRoutingAssign", acViewNormal
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acRefresh, , acMenuVer70
End Sub

When I ran this code a get the runtime error 2115:
"The macro or function set to the BeforeUpdate or ValidationRule property
for this field is preventing Access from saving the data in the field."

Is there a work around this or is this the wrong approach?

Both. That's the wrong approach for anything and there is a
better way to do what you asked.

First, the code should be in the check box's AfterUpdate
event procedure. The code itself would be more like:

If Me.chkAssign = True Then
CurrentDb.Execute "UPDATE thetable " _
& "Set Assign=False " _
& "WHERE Assign = True"
Me.Requery
End If

Note that your whole concept is practically meaningless if
multiple users could possible be doing this at nearly the
same time.

 

[DJJ]

Both. That's the wrong approach for anything and there is a
better way to do what you asked.

First, the code should be in the check box's AfterUpdate
event procedure. The code itself would be more like:

If Me.chkAssign = True Then
CurrentDb.Execute "UPDATE thetable " _
& "Set Assign=False " _
& "WHERE Assign = True"
Me.Requery
End If

Note that your whole concept is practically meaningless if
multiple users could possible be doing this at nearly the
same time.

Marsh,

I ran this code but it is updating all the checkboxes to the same Yes or No
value in the same manner an unbounded checkbox would behave.in a subform
when selected. I am trying to have only have one checkbox selected within a
group of records displayed within a continuous subform.

The subform shows a list of contractors who are authorized to build a
product that is listed on the main form. The purpose of the checkbox is to
designate which contactor is the preferred contractor for the work at any
given time.

 

[DJJ]

I guess I must have missed the "group" xoncept in your
original post. If you can define what constitutes a "group"
(in terms of the fields in the table and form), then we just
need to add that to the update query's WHERE clause.

I added the child fields that link to the main form to the WHERE statement
in your code to see if that made any difference but I still get the same
result.

Private Sub chkAssign_AfterUpdate()
If Me.chkAssign = True Then
CurrentDb.Execute "Update tblSmallRoutings" _
& " Set Assign = False" _
& " WHERE Assign = True" _
& " And OrdrNo = '"" & Me.txtOrdrNo.Value & ""'" _
& " And LineNum = '"" & CInt(Me.txtLineNum.Value) & ""'"
Me.Requery
End If
End Sub

Here's a link to a screen shot of the form
http://us.f13.yahoofs.com/bc/43e99c7emd1232ca7/bc/Picture3/subformcheckbox.jpg?BC3BXBGBfJdYIT2x

 

[Marshall Barton]

I ran this code but it is updating all the checkboxes to the same Yes or No
value in the same manner an unbounded checkbox would behave.in a subform
when selected. I am trying to have only have one checkbox selected within a
group of records displayed within a continuous subform.

The subform shows a list of contractors who are authorized to build a
product that is listed on the main form. The purpose of the checkbox is to
designate which contactor is the preferred contractor for the work at any
given time.

I guess I must have missed the "group" xoncept in your
original post. If you can define what constitutes a "group"
(in terms of the fields in the table and form), then we just
need to add that to the update query's WHERE clause.

 

[DJJ]

Here is a screen shot again (this one should open)
DJJ
http://pictures.aol.com/galleries/djj42ty

 

[Marshall Barton]

I added the child fields that link to the main form to the WHERE statement
in your code to see if that made any difference but I still get the same
result.

Private Sub chkAssign_AfterUpdate()
If Me.chkAssign = True Then
CurrentDb.Execute "Update tblSmallRoutings" _
& " Set Assign = False" _
& " WHERE Assign = True" _
& " And OrdrNo = '"" & Me.txtOrdrNo.Value & ""'" _
& " And LineNum = '"" & CInt(Me.txtLineNum.Value) & ""'"
Me.Requery
End If
End Sub

Here's a link to a screen shot of the form
http://us.f13.yahoofs.com/bc/43e99c7emd1232ca7/bc/Picture3/subformcheckbox.jpg?BC3BXBGBfJdYIT2x

You have too many quotes:

& " WHERE Assign = True" _
& " And OrdrNo = '" & Me.txtOrdrNo.Value & "' " _
& " And LineNum = '" & CInt(Me.txtLineNum.Value) & "' "

Are you sure that both those fields are Text fields? If
it's a number type field (in the table) then don't enclose
the value in quotes. E.g.
& " And LineNum = " & CInt(Me.txtLineNum.Value)

 

[DJJ]

You have too many quotes:

& " WHERE Assign = True" _
& " And OrdrNo = '" & Me.txtOrdrNo.Value & "' " _
& " And LineNum = '" & CInt(Me.txtLineNum.Value) & "' "

Are you sure that both those fields are Text fields? If
it's a number type field (in the table) then don't enclose
the value in quotes. E.g.
& " And LineNum = " & CInt(Me.txtLineNum.Value)

I am not quite sure if this is what you mean (see below). Any less quotes
and I get a complie error. But even with this unbridged code the checkboxes
are still all getting set to either Yes or No so something is still missing.

Private Sub chkAssign_AfterUpdate()
If Me.chkAssign = True Then
CurrentDb.Execute "Update tblSmallRoutings" _
& " Set Assign = False" _
& " WHERE Assign = True" _
& " And OrdrNo = '" & Me.txtOrdrNo.Value & "'" _
& " And LineNum = ' & Me.txtLineNum.Value & '"
Me.Requery
End If
End Sub

 

[DJJ]

To clarify the field datatypes:
OrdrNo = Text
LineNum = Numeric

 

[DJJ]

I am not quite sure if this is what you mean (see below). Any less quotes
and I get a complie error. But even with this unbridged code the
checkboxes are still all getting set to either Yes or No so something is
still missing.

Private Sub chkAssign_AfterUpdate()
If Me.chkAssign = True Then
CurrentDb.Execute "Update tblSmallRoutings" _
& " Set Assign = False" _
& " WHERE Assign = True" _
& " And OrdrNo = '" & Me.txtOrdrNo.Value & "'" _
& " And LineNum = ' & Me.txtLineNum.Value & '"
Me.Requery
End If
End Sub

Ok, I found the problem... The checkbox had mysteriously become unbound!
The code now works great.

Thank you VERY much for your help.

DJJ

 

[Marshall Barton]

Ok, I found the problem... The checkbox had mysteriously become unbound!
The code now works great.

Whew, that's a relief. It's really tough to figure out that
kind thing and just about impossible to do remotely.

Glad I could provide part of the solution.