Originally Posted by
Karl H
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
Validation rules cannot refer to other records. Also, unlike SQL Server, Access does not support triggers, which could be set up to do what you describe.
However, you can use a form to do this! The following sub should do the trick; change the code to fit your object names as needed:
Code:
Private Sub Form_AfterUpdate()
If Me!CheckBoxField Then
With DoCmd
.SetWarnings False
.RunSQL "UPDATE MyTable SET MyField = 0 " & _
"WHERE MyIDField <> " & Me!FieldWithUniqueID
.SetWarnings True
MsgBox "All other records set to false"
End With
End If
End Sub
[/code]