MS Access Table field Limit based on another field

Posted on 2012-09-07
Medium Priority
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 49

Accepted Solution

Dale Fye earned 2000 total points
ID: 38376831
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

862 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