Check only one checkbox in a subform

  • Thread starter Thread starter DJJ
  • Start date Start date
D

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
 
DJJ said:
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?
Click to expand...


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.
 
Marshall Barton said:
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.
Click to expand...

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.
 
Marshall Barton said:
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.
Click to expand...

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
 
DJJ said:
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.
Click to expand...


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 said:
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
Click to expand...


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)
 
Marshall Barton said:
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)
Click to expand...

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 said:
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
Click to expand...

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

Thank you VERY much for your help.

DJJ
 
DJJ said:
Ok, I found the problem... The checkbox had mysteriously become unbound!
The code now works great.
Click to expand...


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.
 
Back
Top