MS Access 2013 - One dropdown dependent on another in datasheet view

0
votes

I have a view in MS Access 2013 that is in the datasheet layout. The view is based on a single SQL Server 2012 table and is editable.

I have two columns in the datasheet view that are linked to dropdowns where the user can select values. Based on the value of the first column, I want the second column to be filtered on a subset of values that meet the criteria in the first column.

The lookup values come for each column come from two tables (from SQL Server 2012, but stored as views in MS Access):

Table 1: (Number is the "ID" and description is the "label")

  1. Open
  2. Action
  3. Closed

Table 2:

  1. Met Criteria (pertains to Table1ID #1)
  2. User Added (pertains to Table1ID #1)
  3. Sent for review (pertains to Table1ID #2)
  4. Response Received (pertains to Table1ID #2)
  5. Finalized (pertains to Table1ID #2)
  6. Did not meet criteria (pertains to Table1ID #3)
  7. Process completed (pertains to Table1ID #3)

The intended functionality is that if the user selects "Open" in the first column (dropdown is based on values in Table 1), that the second column will only display IDs #1 and #2 from Table 2 as valid selections (i.e. dropdown for the second column is filtered where Table1ID = 1).

If a record is already filled out (e.g. column 1 has ID 1 and column 2 has ID 2) and a user edits the record, for example selecting ID 2 in column 1, then column 2 will remove the invalid combination (e.g. column 2's ID of 2 is no longer valid as it is not part of the subset for Table 1's ID selected, which is now 2, changed from 1).

My question is above, but for clarity, I have the SQL views and datasheet views the way I want and have the dropdowns working independently of each other (albeit column 2 shows all values in table 2), I just need assistance with relating/linking the two dropdowns together.

Thank you in advance for your assistance.

1
sql-serverms-accessdependenciesms-access-2013datasheet

1 Answers

1
votes

Assuming by drop down you are refering to a ComboBox in Access. To make them dependant on each other, You need to modify the row source of the second drop down. Goto the design view of the form click on the ComboBox and goto the Data Tab on the Propoerty Sheet. Next to the Row Source there will be an icon with 3 dots (...) click that.

It will open a Query Editor Window. In the Query Editor Select your table and add the column to the field list below. Then you need to define a Criteria which in your case if I read correctly will be a Like Like & "*" & Forms![FormName]![Combo1] & "*"

Now this will ensure combobox2 is dependant on ComboBox1. Now gointo the ComboBox 1 Properties and goto the event tab and add the following code in the AfterUpdate Event.

me.Combo2.Requery

Me.Combo2.value = ""