Best way to have cascading comboboxes appear and update a table based on a multi-selection combo box?

Your first mistake is attempting to use a multi-valued field.  Although these types of fields were designed to make databases easier for newbies to develop, they are actually very difficult to use when it comes to queries and reports.  I know this will be confusing, and we might need to take this in baby steps, but in the long run, I would not recommend using multi-valued fields.

Instead of using a multi-value field, what you should do is create a new table which allows you to assign multiple types to an HXXID value.  I'm not sure what you would call this table, because I'm not familiar with your data structure or what HXXID represents, but lets just call this tbl_HXX_Types, with fields (HXXID and TypeID (or maybe Type if that is a numeric value).

This table would contain TypeID, TypeDesc, and IsSelected (Y/N) fields and in your forms current event, I would populate this table by first deleting all of the previous records, and then appending all of the records from the Types table and an indication of whether that record already exists in tbl_HXX_Types.  Those queries would look something like:

currentdb.Execute "DELETE FROM tbl_HXX_Types_temp"
strSQL = "INSERT INTO tbl_HXX_Types_temp (TypeID, TypeDesc, IsSelected) " _
             & "SELECT T.TypeID, T.TypeDesc, IIF(IsNull(HT.TypeID), False, True) " _
             & "FROM tbl_Types LEFT JOIN (" _
             & "SELECT TypeID FROM tbl_HXX_Types WHERE HXXID = " & me!HXXID & ") as HT " _
             & "ON T.TypeID = HT.TypeID"
currentdb.Execute strsql

This would add every record from tbl_Types into this temporary table, and would set the [IsSelected] value to True if the TypeID exists in tbl_HXX_Types for the current HXXID Value, and False if not.

Then, you can create a subform based on this table to display the types, and a checkbox for the [IsSelected] field.  Then, you would use your combo box to filter the records displayed in this subform, and would use the AfterUpdate event of the subform to actually insert or remove values from tbl_HXX_Types as appropriate.

I know this sounds complicated, but in the long run, if you learn these techniques, it will save you many headaches by not having to deal with multi-valued fields.

Post back if you are interested in doing this the right way.

Dale

Hi Dale,

Thank You for your explanation, I might not be using the right verbiage.   I am creating everything from scratch so this is perfect.

I can create a list box, check box or single drop down combo box.

The HXXID would be a member ID.  
When they call we ask them a series of questions.

cboReason - Not multiselect, just a combo box.
Type 1
Type 2
Both


If I select Type1 in my combobox (Not Multiselect)
then I only want to see the Reason for Type1 in my reason type lstReasonType list box.  Then I will make selections.

 If I select Type2 in my combobox (Not Multiselect)
then I only want to see the Reason for Type2 my reason type lstReasonType list box.  Then I will make selections.

If I select Both in my combobox (Not Multiselect)
then I only want to see all in my reason type lstReasonType list box.  Then I will make selections.

Once the values are selected I would like them to stored in a table that will have:

HXXID
ReasonID
ReasonType
Reason

Does this help?  I am trying the cascading combobox code but it does not seem to work and will not hide or show the list box or update the table based on a selection I chose.

If you are only looking for a single reason for each member, then putting these values (both the type and reason) in the Members table would make sense.  If you are going to have multiple reasons for each member then a separate table is necessary.  And you probably don't even need to use my previous explanation, as simple continuous subform which is linked to the main form on memberID (HXXID) via a master/child relationship should do.

With that structure, you would display the  Type and Reason as combo boxes on the continuous subform, and might display the Reason as well.  Then when the user selects (Type1, Type2, or Type3) from the Type combo, you requery the Reason Combo.  Assuming you have a Reasons table (tblReasons) with ReasonID, TypeID, and ReasonDesc, you would set the RowSource of the cboReason to something like:

SELECT ReasonID, ReasonDesc
FROM tblReasons
WHERE TypeID = [Forms]![yourformname].cboType

This is the critical part of the Cascading Combo concept.

But there is a downside to this as well, because if you are selecting multiple reasons, and using different Type values for some of the reasons, then you could end up records where the reason value does not display properly in the combo box.  This is better explained in another thread I contributed to.  Take a look at the original post and then at my explanation and the sample database included in that thread.