Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-10-14
6
Medium Priority
?
335 Views
Last Modified: 2013-11-28
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
Comment
Question by:dplowman
  • 3
  • 2
6 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36970704
take a look at this
      
Cascading Lists for Access Forms
http://www.fontstuff.com/access/acctut10.htm
0
 
LVL 2

Accepted Solution

by:
daz84 earned 2000 total points
ID: 36970886
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
 

Author Comment

by:dplowman
ID: 36970896
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 2

Expert Comment

by:daz84
ID: 36970955
Ok, enjoy your weekend, I sure will be! It's curry night :)
0
 

Author Comment

by:dplowman
ID: 36981452
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
 

Author Comment

by:dplowman
ID: 36981723
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

804 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question