yes/no type

[Mike]

Hi,

I have a field with yes/no type which i use for
determining active/passive record, so when a user ticks
the option viewed in subform and that record has a value
of yes, i want all the records containg yes value to
contain "no" value because there can be only one active
record. it's like an option group only one can be
selected, but related with records.

thanks for any help on this.

best regards

 

[Kevin Sprinkel]

I have a field with yes/no type which i use for

determining active/passive record, so when a user ticks
the option viewed in subform and that record has a value
of yes, i want all the records containg yes value to
contain "no" value because there can be only one active
record. it's like an option group only one can be
selected, but related with records.

thanks for any help on this.

Hi, Mike.

The strategy is to loop through the recordset, toggling
the value. I do this in selecting an Awarded Bid from the
many construction bids we might receive. It works as you
suggest as a "multi-record option group". The first step
is getting the key field of the record you just toggled to
True.

Private Sub chkAwardedBid_AfterUpdate()

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

On Error Resume Next

Dim rst As DAO.Recordset
Dim intindex As Integer

' Get key of current record
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

Good luck.
Kevin Sprinkel

 

[mike]

hi kevin,

i just tried the method you mentioned and it worked
perfectly for me, Thanks for your help.

i guess i have to focus on working with recordsets, i have
intermediate knowledge of VBA for excel and Visual Basic
programming, but i have to improve myself on using VBA for
Access methods, maybe you can advice some online manuals
for this, anyway your help is muchly apreciated.

regards.

 

[Kevin Sprinkel]

... maybe you can advice some online manuals

for this, anyway your help is muchly apreciated.

The best reference book I use is The Access Developer's
Handbook by Litwin, Getz, et al. It comes with a CD-ROM
of code examples. Stephen Lebans, who is a frequent
contributor here, also has an excellent website, as do
some of the other MVP's. And, of course, even though we
tend to complain about its inadequacies, VBA Help has
syntax and examples as well.

Good luck.

Kevin Sprinkel