Option Group for Detail Records

[Kevin]

In a detail subform, the low, high, avg, and other
construction bids are entered. There is a checkbox field
called "AwardedBid". The values from this particular
record are used in several ways throughout the application.

I would like to ensure that one and only one of the
AwardedBid boxes are checked, much like an option group
would for a single record, which I guess means cycling
through the records, and displaying a message to the user
and not allowing them to exit the subform until one and
only one is checked.

Valid data should look something like:

LOW $15,000,000 $220/SF AwardedBid = True
HI $18,000,000 $264/SF AwardedBid = False
AVG $16,500,000 $242/SF AwardedBid = False

Thanks for any input on implementing this.

Kevin

 

[Elwin]

Use the BeforeUpdate() event of your check box control to
validate. good luck.

Private Sub MyControlName_BeforeUpdate(Cancel As Integer)
Dim rst as DAO.recordset
If Me!MyControlName = False Then
Set rst = Me.RecordsetClone
Do Until rst.EOF
If rst!AwardedBid Then
MsgBox "Only one bid can be checked."
Cancel = True
Exit Do
End If
Loop
End If
End Sub

 

[Kevin]

Thanks for your response. On further review, however, I
decided that rather than post a message, if the user
changes the AwardedBid to True, I want to set all others
to False. The following code was successful.

Private Sub chkAwardedBid_AfterUpdate()
' If awarded bid is checked, all others are set to false,
' essentially creating a multi-record option group.

Dim rst As DAO.Recordset
Dim intIndex As Integer
intIndex = Me!BidDataID

If Me!chkAwardedBid = True Then
' Create recordset clone, loop through all records
Set rst = Me.RecordsetClone
rst.MoveFirst

Do Until rst.EOF
If rst!BidDataID <> intIndex Then
With rst
.Edit
!AwardedBid = False
.Update
End With
End If
rst.MoveNext
Loop

' Clean up
Set rst = Nothing

End If
End Sub