Yes/No field in a table-Make exclusive or default?

Hi,
Can you have a yes/no field in a table that will have a validation rule that
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/7246-yes-no-field-table-make-exclusive.html
only one rowsource can have the "yes" choice selected? This would allow for
declaring that row source the default.

The validation rule would need to state, If "yes", then all other records in
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7246
the same table = no.

Is this possible? What would the expression for the validation rule be?

THank you,
Karl

*

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

Karl,

Validation rules apply to the data in the current record, so it can't be
used. But you can loop through the recordset, and accomplish this in the
checkbox' AfterUpdate event:

' If field in current record is checked, set all others to false,
' essentially creating a multi-record option group.
On Error Resume Next

Dim rst As DAO.Recordset
Dim intindex As Integer

' Remember ID of current record
intindex = Me!YourPrimaryKeyID

If Me!YourCheckBox = True Then

' Create recordset clone, loop through all records
Set rst = Me.RecordsetClone
rst.MoveFirst

Do Until rst.EOF
If rst![YourPrimaryKeyID] <> intindex Then
With rst
.Edit
!YourYesNoField = False
.Update
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7246
End With
End If
rst.MoveNext
Loop

' Clean up
Set rst = Nothing

End If

Sprinks



"Karl H" wrote:

> Hi,
> Can you have a yes/no field in a table that will have a validation rule that
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7246
> only one rowsource can have the "yes" choice selected? This would allow for
> declaring that row source the default.
>
> The validation rule would need to state, If "yes", then all other records in
> the same table = no.
>
> Is this possible? What would the expression for the validation rule be?
>
> THank you,
> Karl

Hi Sprinks,
After connecting the checkbox to the yes/no data field in the table, that
worked great. Thank you very much! Have a good wknd!
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7246
Karl

"Sprinks" wrote:

> Karl,
>
> Validation rules apply to the data in the current record, so it can't be
> used. But you can loop through the recordset, and accomplish this in the
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7246
> checkbox' AfterUpdate event:
>
> ' If field in current record is checked, set all others to false,
> ' essentially creating a multi-record option group.
> On Error Resume Next
>
> Dim rst As DAO.Recordset
> Dim intindex As Integer
>
> ' Remember ID of current record
> intindex = Me!YourPrimaryKeyID
>
> If Me!YourCheckBox = True Then
>
> ' Create recordset clone, loop through all records
> Set rst = Me.RecordsetClone
> rst.MoveFirst
>
> Do Until rst.EOF
> If rst![YourPrimaryKeyID] <> intindex Then
> With rst
> .Edit
> !YourYesNoField = False
> .Update
> End With
> End If
> rst.MoveNext
> Loop
>
> ' Clean up
> Set rst = Nothing
>
> End If
>
> Sprinks
>
>
>
> "Karl H" wrote:
>
> > Hi,
> > Can you have a yes/no field in a table that will have a validation rule that
> > only one rowsource can have the "yes" choice selected? This would allow for
> > declaring that row source the default.
> >
> > The validation rule would need to state, If "yes", then all other records in
> > the same table = no.
> >
> > Is this possible? What would the expression for the validation rule be?
> >
> > THank you,
> > Karl

Hi Sprinks,
Now I'm trying to load frmPilot so that a combo box called cboProvider will
open with the default provider. Most of this form is based on tblPilot,
rather than tblProv, but the combo box is related to tblProv.
I tiied this script w/o success:

'Inserts default provider information
Private Sub Form_Load()
cboAMEName.SetFocus
' Find default provider name and put in cboAMEName
SELECT Tables.tableProvider.ProvName
WHERE defProv = True
cboAMEName.Text = ProvName
End Sub

The debugging tool says "tables" is not a defined variable. How would I
wirte the correct code?

Thank you,
Karl

"Sprinks" wrote:

> Karl,
>
> Validation rules apply to the data in the current record, so it can't be
> used. But you can loop through the recordset, and accomplish this in the
> checkbox' AfterUpdate event:
>
> ' If field in current record is checked, set all others to false,
> ' essentially creating a multi-record option group.
> On Error Resume Next
>
> Dim rst As DAO.Recordset
> Dim intindex As Integer
>
> ' Remember ID of current record
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7246
> intindex = Me!YourPrimaryKeyID
>
> If Me!YourCheckBox = True Then
>
> ' Create recordset clone, loop through all records
> Set rst = Me.RecordsetClone
> rst.MoveFirst
>
> Do Until rst.EOF
> If rst![YourPrimaryKeyID] <> intindex Then
> With rst
> .Edit
> !YourYesNoField = False
> .Update
> End With
> End If
> rst.MoveNext
> Loop
>
> ' Clean up
> Set rst = Nothing
>
> End If
>
> Sprinks
>
>
>
> "Karl H" wrote:
>
> > Hi,
> > Can you have a yes/no field in a table that will have a validation rule that
> > only one rowsource can have the "yes" choice selected? This would allow for
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7246
> > declaring that row source the default.
> >
> > The validation rule would need to state, If "yes", then all other records in
> > the same table = no.
> >
> > Is this possible? What would the expression for the validation rule be?
> >
> > THank you,
> > Karl