Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

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

Discussion in 'Information Technology' started by Karl H, Nov 12, 2005.

  1. Karl H

    Karl H
    Expand Collapse
    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
     
  2. Loading...


  3. Sprinks

    Sprinks
    Expand Collapse
    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



    "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. Karl H

    Karl H
    Expand Collapse
    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

    "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
    > > the same table = no.
    > >
    > > Is this possible? What would the expression for the validation rule be?
    > >
    > > THank you,
    > > Karl
     
  5. Karl H

    Karl H
    Expand Collapse
    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

    "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
    > > the same table = no.
    > >
    > > Is this possible? What would the expression for the validation rule be?
    > >
    > > THank you,
    > > Karl
     

Share This Page