MS Access Table field Limit based on another field

Posted on 2012-09-07
Last Modified: 2012-11-01
I have an Access database table that is used to specify details about various sensors. A sensor could be, for example, a PRESSURE sensor so I have a column for SensorType and use a lookup that has fields such as: PRESS, TEMP, FLOW, etc.
Next, I need to have a column that holds the MeasurementUnits of the sensor, for example, PSI, kPa, MPa (for pressure) or F, C, R, K (for temperature). This column is to be used to enter the actual information from the sensor.
After that, I want to have two additional columns, one for English Units and one for metric units. This will tell a program I am writing which units to display when in English and in Metric.

My problem is this: After I select the SensorType, I want to limit the choices for the MeasurementUnits column based on the SensorType. I don't want people to choose a SensorType of TEMP and then be able to choose a MeasurementUnits of PSI (a pressure)

I think once I know how to do the first one I will be able to use the same technique to limit the choices EnglishUnits and MetricUnits.

I don't want the user to use a form to fill out this data, I just want them to fill out the actual database table.
Also note, sometimes all of the units are the same, for example, Relative Humidity is expressed as % (percent) no matter English or Metric.

Thanks in advance for your help,
Question by:charlieb01
    1 Comment
    LVL 47

    Accepted Solution

    This is called cascading combo boxes.  Generally, when I have that type of situation I build several "lookup" tables.



    Then a third linking table (tbl_lookup_SensorTypeUnits) which contains all of the acceptable combinations of SensorTypeID and MeasUnitID.  The query for your cbo_MeasUnits then becomes something like:

    SELECT MU.MeasUnitID, MU.MeasUnitDesc
    FROM tbl_lookup_SensorTypeUnits STU
    INNER JOIN tbl_lookup_MeasUnits MU
    ON STU.MeasUnitID = MU.MeasUnitID
    WHERE STU.SensorTypeID = Forms!yourFormName!cbo_SensorType

    Then, you just need to configure the AfterUpdate event of cbo_SensorType so that it requeries the cbo_MeasUnit

    Private Sub cbo_SensorType_AfterUpdate


    End Sub

    And, if you will be returning to records which have this data previously filled in, you will also need to put that Requery line in the Current event of the form, so that as you move from record to record, the values in that combo box are refreshed and relate to the value in the SensorType combo.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now