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

[Guest]

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

 

[Guest]

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

 

[Guest]

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

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

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

 

[Guest]

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

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

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