MS Access: Dependent drop down combobox in Split form without affecting the rest?

I am trying to make a dependent drop down work with my database here but it is giving me a hard time for different reasons that I will explain.

This is what I have:

A form called "tblOTS" (split form based on an actual "tblOTS" table):

As well as a table called "tblAlphaCode":

When looking at the property sheet for the "Alpha Code" combobox (actually called "strOTSAlphaCode") on my tblOTS form, this is what I have:

The current SQL statement under "Row Source" for this strOTSAlphaCode is:

SELECT tblAlphaCode.strAlphaCode, tblAlphaCode.strCategory,   tblAlphaCode.ID, tblAlphaCode.numSortingOrder   FROM tblAlphaCode   ORDER BY tblAlphaCode.strCategory, tblAlphaCode.numSortingOrder;  

Basically, when user select an Alpha code in the drop down from "tblOTS" form, it adds the ID of the tblAlphaCodes into the actual tblOTS record.

My goal is to have the user select first a "Category" (here FASTENERS/HARDWARE ...) when adding/modifying an OTS record, and then have the "Alpha Code" drop down dependent on what category was just selected.

I first tried to changed the SQL statement using a WHERE condition on the category field, based on the form category field itself:

SELECT tblAlphaCode.strAlphaCode, tblAlphaCode.strCategory, tblAlphaCode.ID, tblAlphaCode.numSortingOrder  FROM tblAlphaCode  WHERE (((tblAlphaCode.strCategory)=[Forms]![tblOTS]![strOTSCategory]))  ORDER BY tblAlphaCode.strCategory, tblAlphaCode.numSortingOrder;  

And created a strOTSAlphaCode.requery in my Form_Current event to update it everytime.

However, it affects the whole form itself as my control source is directly affected by my Row Source in this instance, and the form looks like this:

You can see that the drop down is working beautifully, however, all the fields that do not have the same category as the current record that I work on (FASTENER <> HARDWARE here), are missing their Alpha code (the second record is missing "O-RING" compared to the first picture from this post); this behavior is problematic!

I tried to find workarounds by changing strOTSAlphaCode into a simple text box only containing the Alpha Code ID, added an unbound text box on top with dlookup function to find the actual alpha code related to this ID, and an unbound combobox in between with the exact same SQL statement inside the Row Source Property, that would update the strOTSAlphaCode with some VBA ... not ideal right?? Ahaha. This is why I believe there is a simpler way that I am not aware of, but also I would like to use a "search as you type" code for this combobox later on and this solution was making it very difficult.

How can I make this work? I believe the answer is a simple/different SQL statement to put in my Row Source property, that is probably something related to some type of JOIN statement? Or something else? I expect to write some VBA code to make all of this work flawlessly, but I want to make sure that I have the right SQL statement first.

https://stackoverflow.com/questions/66467138/ms-access-dependent-drop-down-combobox-in-split-form-without-affecting-the-rest March 04, 2021 at 09:06AM