Results 1 to 2 of 2
  1. #1
    Karl H Guest

    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
    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. #2
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275

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

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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Default Information for an existing field HELP
    By opinionsrfun in forum Access
    Replies: 1
    Last Post: 06-19-2013, 06:30 AM
  2. Adding new column to make-table query
    By dtn118 in forum Access
    Replies: 2
    Last Post: 08-03-2008, 06:51 AM
  3. Default Value in table = Field plus 3 days
    By AmyLynnHill in forum Access
    Replies: 1
    Last Post: 08-03-2008, 01:58 AM
  4. Link ComboBox to field in a table
    By DrDebate in forum Forms
    Replies: 0
    Last Post: 04-27-2007, 08:03 AM
  5. Replies: 2
    Last Post: 09-01-2006, 04:03 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums