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

#1 (**permalink**) 12-Nov-2005, 22:39 PM

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
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

*

#2 (**permalink**) 12-Nov-2005, 22:39 PM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7246
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

#3 (**permalink**) 12-Nov-2005, 22:40 PM

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!
Karl

"Sprinks" wrote:

> Karl,
>
> Validation rules apply to the data in the current record, so it can't be
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7246
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7246
> 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
> 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

#4 (**permalink**) 13-Nov-2005, 17:33 PM

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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7246
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
> 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
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