0

I'm a novice access user. I have created a form which brings through multiple values from a table, I have a column which I have as combo boxes. What I want to do is limit these combo boxes by the value in the same ro of a different column. Example: combo box column contains different component names on a product(red bottle, Blue bottle or red cap, blue cap) I want to limit the combo box so when you are looking at different components you are only looking at like for like , caps is a list of caps, bottles is list of bottles. Another column in the table has this value.

Justin Baker
  • 1
  • 4
  • How did you set up your combo box? Is it getting its list of options from a table, or did you manually provide the list of options? – Matt Hall Feb 11 '16 at 12:11
  • Also: is your form's Default View property set to `single` or `continuous`? – Matt Hall Feb 11 '16 at 12:18
  • Hi Matt, thanks for responding. The form is a continuous form and the data is pulled through from a query created from a table. – Justin Baker Feb 11 '16 at 13:18
  • I set up the combo box by creating a tabulated form and then right clicking the column that I wanted and change to combo box – Justin Baker Feb 11 '16 at 13:20

1 Answers1

0

I've created this mock-up, which filters the combo box options based on data from the row it resides on.

The options for the combo box come from a table I've created. This table has a column called ComponentName, which is what users of the form will see as the options in combo box, but there is also another column called ComponentType, which is what will be used later to match against the row in which the combo box will be placed.

enter image description here

Here's the form (set to continuous) with some component types to match on and our combo box:

enter image description here

When picking from the combo box, it will match the Component Type Needed data for the row to the table underlying the combo box options and show only those options in the combo box with the matching Component Type:

enter image description here

To achieve this, the Row Source property of the combo box has been set up so that it is limited in a WHERE clause to whatever data is in the ComponentTypeNeeded field for the same row on the form:

SELECT [tblComponents].[ComponentID], [tblComponents].[ComponentType], [tblComponents].[ComponentName] FROM tblComponents WHERE [tblComponents].[ComponentType] = [ComponentTypeNeeded] ORDER BY [ComponentName];

I've then set the Column Widths property of the combo box so that only the ComponentName column shows in the combo box (the other 2 are hidden by being set to a width of 0cm):

enter image description here

Hope this gives you some pointers for your project :)

Matt Hall
  • 2,412
  • 7
  • 38
  • 62
  • Thanks Matt, will give it a go and let you know if it works for me. Looks exactly what I'm after. – Justin Baker Feb 11 '16 at 14:31
  • Hi Matt, Thank you for your help with this. One thing that I have noticed is that when you are on the Form in the combo boxes it only shows either Caps or Bottles but not both so on a cap line you see all line with any caps selected and on a bottle line only bottles. Is this the same when you view it or have I done something wrong? – Justin Baker Feb 15 '16 at 11:48
  • I thought this is the behaviour you were after? "I want to limit the combo box so when you are looking at different components you are only looking at **like for like** [...] caps is a list of caps, bottles is list of bottles" – Matt Hall Feb 15 '16 at 14:04
  • Hi Matt, thanks for replying. When I'm using the combo box I want the view to be limited, however when the items are chosen I want then to stay visible. So as an example: I choose a blue bottle from the bottle combo, then move onto the cap I would still want to see which bottle has been chosen in the combo box. I want to limit the choice in the combo box, which it does but once chosen stay visible. – Justin Baker Feb 15 '16 at 15:18