Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2009
    Location
    UK
    Posts
    19

    Unanswered: Yes/No data type question

    A table in an Access 2003 database I am trying to create has two fields set to yes/no data type. The fields are called Studio and Proofing.
    I have a form which is based on a query and shows the selected records which I can edit. After I am ready with editing I would like to be able to change the Yes value of this record to No and at the same time change the No value for this record in the Proofing field to Yes.
    At the moment I have two check box controls on the form, one for Studio and one for Proofing. They are bound to the relevant fields in the table. Once I am ready with editing I untick the Studio check box (which set the data type in the Studio field to No) and tick the Proofing check box (which set the data type in the Proofing field to Yes). The problem with this method is that occasionally both check boxes are left either ticked or unticked and the query/form will display the wrong records.
    I would like to get a button on the form which after pressing causes the Studio check box to be unchecked and the Proofing check box checked at the same time so that it is impossible that both check boxes have the same value.
    I have no idea if this is possible and as I do not know VB I am at a loss.
    Any help will be very much appreciated.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This is reasonaly difficult to spot case of a design problem. It does not conform to third normal form. This simply states that any attributes not part of the key should not be affected by the value of another non-key attribute.
    This might not seem obvious, but imagine instead you had a "Percent Complete" instead of two stagtes. Would you have 101 columns (0, 1, 2, 3 -> 100)? Nah, you'd have a single column.

    I recommend a single column called something like Stage. It is text and allows "Proofing" or "Studio". Use a combo box on your form to set these values. Your table is now well designed, scalable (it is trivial to add new stages), and your form is clean and simple (no VBA).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    pootle flump is right: data normalization is the basement of any decent database design (see: http://www.tonymarston.net/php-mysql...e-design.html).

    If you do not like the idea of a text field and want to keep a yes/no schema and a visual interface, an OptionGroup with two OptionButtons would be a better choice: it does not need any code and makes chosing both values impossible (there is only one column into the table that contains a different value set by the value of the pushed button).

    However, if you still decide to keep the actual design of your database (you should not and don't complain later : you where warned ), you can add a button with the following code:

    Code:
    Private Sub MyButton_Click()
    
        Me.Check_Studio.Value = False
        Me.Check_Proofing.Value = True
        
    End Sub
    But seriously consider changing the design of the table.

    Have a nice day!
    Attached Thumbnails Attached Thumbnails ScreenShot030.jpg  

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I don't really understand what you are doing, but it sounds to me that you do have a design issue, but its primarily a user interface design.

    if its either or then you should be using a single value Ie one YES / NO or a radio butten in a group.

    As I understand it the records state is either studio OR proofing, which isn't really a yes/no choice so a radio group would stylistically be a better bet
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2009
    Location
    UK
    Posts
    19
    Pootle flump, Sinndho and healdem thank you very much for your response which was very helpful. I am learning very slowly how to get on with Access and help from people like you is indispensable. I am grateful for the time and effort you people make. There will be (many) more questions popping up I am afraid.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •