• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 338
  • Last Modified:

MS ACCESS Form Autofill multiple comboboxes based on original selection from a combobox.

The attached spreadsheet has been imported into access as a table called qv_categories. I created a form that a user must select which section between compliance or procedural, then the next field "category" would be prefilled in a combobox with categories tied to the section chosed. The same would go for the subcategory. Once the user selects all three fields, the data should be updated into the qv_category table. My issue is that there are multiple rows for the compliance and procedural sections. So when I create a combo box from the table, there are 16 rows in the combo box with multiple compliance and procedural records. How can I limit this two just one compliance and one procedural, then have another combobox updated with the corresponding category from either on chosen.

QV-Categories.xlsx
0
dplowman
Asked:
dplowman
  • 3
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
take a look at this
      
Cascading Lists for Access Forms
http://www.fontstuff.com/access/acctut10.htm
0
 
daz84Commented:
First off, make sure you have the comboxes set with the correct control source, so this would be the name of the field the data will be stored in within your qv_category table.

Now you want to ensure the rowsource of each combox is correct. The rowsource is the list of options you have to choose from, there are a number of ways in which to do this. If you enter in design mode your combox properties you will see under the data tab a property called 'row source type'. For a small set of values that will never change it is useful to use value list, this you can simply type into the 'row source' property in design time and it will be set. This would probably be best for your first box for Compliance/Procedural.

To enter the row source as a value list simply enter the values you want separated by a semi-colon so it would look like this

COMPLIANCE;PROCEDURAL

Now the rest of the boxes are reliant upon this selection, so they need to be queries.

This will depend where the data is coming from. It is most likely to be either from the table qv_category itself or from a separate lookup table. I will assume it is from a lookup table called qv_category_lookup since you have not clarified this yet.

The row source would be a query where the criteria is the current value of the previous combox

Instead of setting it at design time with something like this
SELECT [category] FROM [qv_category_lookup] WHERE [section]=[forms]![myQVform]![section];
you would find it needs to be updated with the new data every time it is due to change, there is an easier way

Create an event in the first combox (where you choose PROCEDURAL/COMPLIANCE) for "After Update" and enter the following

Private Sub cboSection_AfterUpdate()
  me.cboCategory.RowSource = "SELECT [category] FROM [qv_category_lookup] WHERE [section]='" & me.cboSection.Value & "';"
End Sub

Open in new window


You could then easily adapt this to create a similar procedure to update the second combox based on the value of the first.

If you want to be extra-clever, try setting all except the first of the comboxes to enabled=false in design time then as they are updated set enabled=true on the next combox while it's rowsource is being updated at the same time.

Hope this helps and solves your problem.

Feel free to ask for explinations of any part of it, or if I have misunderstood you please explain further and I will be happy to correct myself.

Daz
0
 
dplowmanAuthor Commented:
Thanks for the quick reply. I will try to look at this over the weekend and let you know if I have any questions early next week!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
daz84Commented:
Ok, enjoy your weekend, I sure will be! It's curry night :)
0
 
dplowmanAuthor Commented:
Daz,

I placed the below code in the first combobox with the compliance and procedural values.

Private Sub cboSection_AfterUpdate()

Me.cboCategory.RowSource = "SELECT [category] FROM [dbo_qv_categories$] WHERE [section]='" & Me.cboSection.Value & "';"

End Sub

When I select the category field from the second combobox, the fields are empty.What should I be placing in the row source for the category combobox?
0
 
dplowmanAuthor Commented:
Perfect again! Not sure what the issue was, but I deleted the category combobox and recreated it. Your process works perfect! Stay tuned, because I may need some help creating a call monitoring form!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now