listbox gets only applicable items from a combo box

[Repent]

I am trying to setup a form that has basically two boxes. the first is a box that lists all available categories. The second box is empty to start. when the user selects a category in the first box, only the options applicable to that category are listed in the second box. Since more than one option can be correct the user needs to be able to select multiple items on the right.

The user then needs to be able to select another category on the left and select multiple options, again, on the right.

There are 12 categories listed on the left with applicable options on the right. the user will have to go through the category list on the left, one by one, and select multiple options on the right as they do so. After the user has selected the last category and options set, there should be a button labeled "Done" nearby that the user clicks on to record all the categories/options selected and to close the form.

this form will be started from a macro button on another form.

any help would be greatly appreciated.

 

[Galaxiom]

Firstly the combo and list boxes:

Create a tblCategory table with field name: CatID, CatName.
Set the properties of the Category combobox.
Row Source: SELECT CatID, CatName FROM tblCategory
Column Count: 2
Bound Column: 1
Column Widths: 0cm;3cm

This will hold the value as a key while displaying the name.

Create a tblOptions table with field names: OptionID, OptionName, Cat1, Cat2 ...... Cat12. Format the Cat# colums as CheckBoxes. Tick the boxes that indicate which options are applicable to which category.
An option can be in more than one category.

Format the Options ListBox with the same settings as the Category combo except for the Row Source.

The Row Source needs to be dynamic and will be based on the Options table. In the After Update event sub of the Category combo place the following code:

Me!Options.RowSource = "SELECT tblOptions.OptionID, tblOptions.OptionName FROM tblOptions WHERE tblOptions.Cat" & Me!Category & " = True;"

This will set the Options RowSource to the records with ticked boxes in the Options column matching the Category Selection (Cat1 etc).

The after update event of the Options box will need to store the values. There this could be done with variables (an array would make sense) or appending records to a table.

You also need to consider how the selected options can be reviewed and how an option could be deselected if no longer required. I would probably display a form based on a tblSelected table with a checkbox that could be deselected to remove an option.

 

[Mr. B]

Repent,

You did not indicate just how your database is structured right now. How are the Options stored? What tables are you currently using?

 

[Repent]

Firstly the combo and list boxes:

Create a tblCategory table with field name: CatID, CatName.--Created a table called "tblCategory", kept the autonumber ID field as the key and created two fields called CatID and CatName.
Set the properties of the Category combobox.--created a combobox on the form
Row Source: SELECT CatID, CatName FROM tblCategory--in row source dropdown the tblcategory table along with the other tables are listed but no "CatID or CatName" I clicked on the elipsis... and a query builder came up?
Column Count: 2--found this
Bound Column: 1--found this
Column Widths: 0cm;3cm--found this

This will hold the value as a key while displaying the name.

Create a tblOptions table with field names: OptionID, OptionName, Cat1, Cat2 ...... Cat12.--did this Format the Cat# colums as CheckBoxes.--did this Tick the boxes that indicate which options are applicable to which category. --did this
An option can be in more than one category. --did this

Format the Options ListBox with the same settings as the Category combo except for the Row Source.--no control source either?

The Row Source needs to be dynamic and will be based on the Options table. In the After Update event sub of the Category combo place the following code:

Me!Options.RowSource = "SELECT tblOptions.OptionID, tblOptions.OptionName FROM tblOptions WHERE tblOptions.Cat" & Me!Category & " = True;"--did this but get error (Run-time error '2465' can't find the field 'category' referred to in your expression.)

This will set the Options RowSource to the records with ticked boxes in the Options column matching the Category Selection (Cat1 etc).

The after update event of the Options box will need to store the values. There this could be done with variables (an array would make sense) or appending records to a table.--??

You also need to consider how the selected options can be reviewed and how an option could be deselected if no longer required. I would probably display a form based on a tblSelected table with a checkbox that could be deselected to remove an option.

--Can you explain this more?

any further help is appreciated.

 

[Galaxiom]

& Me!Category & " = True;"--did this but get error (Run-time error '2465' can't find the field 'category' referred to in your expression.)

Access is looking for a field in the form's Record Source instead of the combobox.
The difference in syntax between the two is vey slight. Access will usually sort it if there is no ambiguity.

Try: & Me.Category & " = True;"
Or: & Me.Category.Value & " = True;"

 

[Repent]

& Me!Category & " = True;"--did this but get error (Run-time error '2465' can't find the field 'category' referred to in your expression.)

Access is looking for a field in the form's Record Source instead of the combobox.
The difference in syntax between the two is vey slight. Access will usually sort it if there is no ambiguity.

Try: & Me.Category & " = True;"
Or: & Me.Category.Value & " = True;"

I tried these and neither worked. I think I need to start over with this and maybe rethink a few things. Some of the instructions provided to me I don't understand. Here is a little more detail. The categories I mention are different pieces of equipment in a manufacturing process. Each category has numerous items listed under it. Each item listed is something that went wrong in the manufacturing process. More than one item can be selected.

When a user has finished making a pallet of finished goods they will open this form (initial form is already created) on the computer and while going through the form they will answer questions like: their operator #, their name, the line #, the product created, the name of their helper, and several others. The form is already created with various linked tables and uses comboboxes. I want to add to this main form. I thought about having a macro button on the form that opens this other form with the categories on it as a separate form. A better way may be to have combo/list boxes that open on the main form to answer these questions and not have a separate form open.

The user can currently step back and forth on the form using the VCR style controls added to the form to change answers to the current comboboxes in a record in case a mistake was made. the user should also be able to see and change these additional comboboxes/listboxes as they step back through the records.

I know that new tables need to be created because the current tables don't contain the data being represented. This project already has numerous tables connected via 1-many relationships.

I can provide a sample DB that has the project as it is now with some fictional data if that would be easier to visualize what I'm after. I would like to be able to do the work myself with your instructions so that I can learn from this.

thanks all so far;
chris