none
Input Masks and Validtion Rules in Access 2016 RRS feed

  • Question

  • I'm trying to build an Access Database for a Business contact list. I need to know inside the Form the format I should use for the Categories "Hot", "Warm", and "Cold" Contacts using an Input Mask. Inside the Form I would like the User to just be able to put in "H" or "h" for Hot, "W" or "w" for Warm and "C" or "c" for Cold Contacts, or they could just put in the whole words Hot, Warm or Cold. I think I may need a Validation code or something so it will only take the H/W/C. I do not know how to use an Input Mask or what else to use for this.

    I also want to know how to add a Check Box in a Form in Access.

    Thanx bunches GVHSE

    Thursday, May 10, 2018 1:42 AM

All replies

  • Hi,

    To better fix the issue, I will move the thread to Access forum for more help.

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Best Regards,
    Winnie Liang


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Thursday, May 10, 2018 8:09 AM
  • Forget about masks and validation rules.  You should model this with two tables, in broad outline as follows:

    Categories
    ....CategoryID  (PK)
    ....Category

    Contacts
    ....ContactID  (PK)
    ....FirstName
    ....LastName
    ....CategoryID  (FK)
    ....etc

    In the contacts form the control for the CategoryID  foreign key column would be a combo box set up as follows:

    ControlSource:   CategoryID

    RowSource:     SELECT CategoryID, Category FROM Categories ORDER BY Category;

    BoundColumn:   1
    ColumnCount:    2
    ColumnWidths:  0cm

    If your units of measurement are imperial rather than metric Access will automatically convert the unit of the last one to inches.  The important thing is that the dimension is zero to hide the first column.

    The user can either select the category from the combo box's drop down list, or type the first character of the category, which by virtue of the control's AutoExpand property being True, will select the appropriate category without the need for the user to type the remaining characters.

    As regards adding a check box to a form, select the check box control from the Controls area of the Design ribbon while in form design view and drag and drop it onto the form.  If the control is to be bound to a Boolean (Yes/No) column in the form's table set the ControlSource property of the control to the name of the column.  I'd strongly advise that you change the control's Name property from the meaningless default name which Access gives it to a name which reflects its purpose, e.g. if the column to which it is bound is named Active for instance, name the control chkActive.

    Ken Sheridan, Stafford, England

    Thursday, May 10, 2018 12:03 PM