Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Nov 2005
    Location
    Br. Columbia, Canada
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Required Field (2003)

    Our database is being designed to collect statistical information. There is a form that tracks the appointments a client has with a certain person, in this case a legal advocate. Data is collected on the Area of Law their appointment is regarding as well as what Level of Service they are receiving. The data is collected on a form with each Area of Law or Level of Service represented by a yes/no checkbox.

    Is it possible that when you enter the Area of Law, it requires the user to enter the level of service and only allows one level of service to be selected?

    K

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Required Field (2003)

    Why don't you enclose the level of service options in an option box? This will ensure only one gets selected.

    However you will have to introduce an extra field in the table called LevelofService, this will hold a value of between 1 and the number of levels of service.

    Another alternative is to place a check in the form's BeforeUpdate event that counts the number of levels of service you have checked.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Required Field (2003)

    Here is another approach.

    For each of Area of law and Level of Service have just a single field, rather than a number of yes/no fields. Make each a combo box with values coming from a lookup table.
    Set the required property of each field to yes, and it can't be left blank.

    On the form I have the set the "limit to List "property to Yes so that you must choose from the list, but then put code in the "Not in List" event to allow users to add new values to the lists.
    Regards
    John



  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Required Field (2003)

    I would go with John's solution for no other reason that it's a more flexible approach because you have a table to hold all these values.

  5. #5
    Lounger
    Join Date
    Nov 2005
    Location
    Br. Columbia, Canada
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Required Field (2003)

    Thank you for the quick reply. John's solution will work very well. It has prompted another question though.

    If a user selects the other field, before there was a combo box that they then selected Other Detail. This was created with a macro that allowed the user to add a new value if there wasn't the correct detail supplied. Can this be made to only accept a value if Other is selected under the Area of Law?

    K

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Required Field (2003)

    My solution completely gets rid of the "Other" value in Area of Law, and the second field for Other values.

    Users can either use an existing Area of Law or add to the list of Areas of Law. If you type something new into the Area of Law field you will receive a message.
    Regards
    John



  7. #7
    Lounger
    Join Date
    Nov 2005
    Location
    Br. Columbia, Canada
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Required Field (2003)

    The problem with eliminating the other detail field is that the report that is generated counts only family, housing, income security and other. The other detail is part of a different report and therefore needs to be seperated out.

    Is there a way that you can set the form so that a value can only be selected if another value was selected first? For example, a user selects the Other field from a drop down list and can then select a value in the Detail list, but if they do not select other, the detail list is not available to them. Also is it possible to set it so that if other is selected the detail field is required?

    K

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Required Field (2003)

    My preference would still be just to have a single field. If you can't change the report, then put a calculated field in the query behind the report, and use that calcualted field for the report.

    Area: IIf([AreaofLaw] In ("housing","family","income security"),[AreaofLaw],"Other")

    this means: If the Area of Law is any of "housing","family","income security", use it, otherwise just treat it as an "Other"
    Regards
    John



  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Required Field (2003)

    To make it a little more flexible you could have an extra field in the table that signifies "Other". In that way you keep all the data in the tables instead of in the query.

  10. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Required Field (2003)

    Good idea Patt. Much more flexible too.

    So in the Lookup Table add a yes/no field Other, with a default value of Yes. Then join this table to the query behind the report.
    Regards
    John



Posting Permissions

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