Full Version: Filter combo box options based on another combo box
UtterAccess Forums > Microsoft® Access > Access Forms
Yuba
Hi all
I'm sure this is a very common query and I know it's been dealt with before, but I'm still struggling to make it work.
I am making a simple knowledge base. The main table is called 'tblArticles' in which there are several numeric fields for things like ApplicationID, CategoryID, AuthorID, etc. All of these numeric fields are related to lookup tables that provide the actual name of the App, Category, etc.
New articles are of course to be entered by a form. What I am aiming to do is have the user select an application from a drop-down combo box, then select an issue category from another drop-down. I want to filter the category options based on which application has been selected. The categories are held in a lookup table containing the fields 'CatID' (linked to CatID in tblArticles), 'AppID' (linked to AppID in tblLookupApplications) and 'Category' (the actual category description).
I have created a subform containing the category combo box, to be placed on the main form. The Child/Master field is AppID. Where I fall down is knowing where to go from here. Can anyone point me in the right direction or give me a simple example to work with? I'd be grateful for any assistance.
Best,
Yuri
dashiellx2000
Check out Candace Tripp's website. She has a great example of cascading combo boxes.
TH.
Yuba
Thanks William - I had previously downloaded her cascading combo boxes demo but failed to decipher it.
think I've nailed this one now - I have the subform filtering to only the categories associated with a particular app. I also have the combo box refreshing its list every time a different application is selected from the parent combo box. However, I'm not sure which event is associated with navigating to a different record - I need this action to trigger a refresh as well. Do you (or anyone, of course) happen to know?
dashiellx2000
What you want to do is on the Current Event, reset the Combo Box SQL to it's original state. This will reset the combo boxes when a new record is loaded on the form.
TH.
Yuba
Gotcha - that works, thank you William.
nother issue seems to have arisen in the meantime (oh, I'm full of them today!) which suggests that I've gone about it a little wrongly.
When I create a new record using the form, the subform containing the 'category' drop-down disappears - entering new data results in an error which reads 'You cannot add or change a record because a reated record is required in table 'tblLookupArticleCategory''.
I am supposing that this is because 1. populating the combo box in the subform relies on data being present in a field in the main form, and 2. that data (i.e. the Application ID number) has not yet been written to the table. In this instance I am using the control value on the form, rather than the field value in the table; I'm not sure whether this will make a difference.
Do you have any thoughts? Why would the subform suddenly disappear when a new record is created?
Yuba
OK, you'll probably laugh at the naivete of my code, but I tried to do something like this, so that the category combo would appear if an application was selected:
CODE
Private Sub cmbApplicationID_AfterUpdate()
If Not frmSelectCategorySubform.Form Then
Load frmSelectCategorySubform
Else
Me.frmSelectCategorySubform.Form.Refresh
End If
End Sub

... is there anyone with a better grasp of code who can see what I'm trying to do and indicate how it should be done? I'm pretty much flying blind. Oh, and by the way, that code crashes A2003 every time it is executed, which I find pretty impressive laugh.gif
dashiellx2000
First thing to check is to make sure the form/subform are linked properly. have you tried forcing the subform to a new record? Do you have the Access Navigation Bar in the Subform to check and make sure it is going to a new record?
Yuba
Well the subform has Link Child Fields and Link Master Fields set to 'AppID' on the relevant tables/forms.
The subform offers only a list of predefined values. The user should not be able to create a new record in the subform, only select from a filtered list of existing ones.
It was a good suggestion to have the navigation bar in the subform (i didn't have it there previously)- that indicated that when I moved between records on the main form, the number of matching records in the subform changed based on the selected application. Obviously when a new record is created there are no matching records because there is no value in the field on which the criterion is based (AppID).
One thing that may be relevant - the control is set to the CatID field in tblArticles, but the value list populated by a query of tblLookupApplicationCategory, which stores the category names. So when the results of the query are null, the subform does not appear. What I'd like to happen is that it should appear with a default value unless an application is selected.
Yuba
The combo box in the subform is populated by the folllowing query:
CODE
  
SELECT tblLookupArticleCategory.Category, tblLookupArticleCategory.AppID, tblLookupArticleCategory.CatID
FROM tblLookupArticleCategory
WHERE (((tblLookupArticleCategory.AppID)=Forms!frmArticleEntry!AppID));

Perhaps what is needed is an onCurrent event that says only perform this query if the AppID field in the main table is not null.
dashiellx2000
I wouldn't set the subform's combo box SQL until the conditions for it are met on the main form. So checking for a null value on the current event should work as longs as you remember to update the SQL on the after update event of the control.
TH.
Yuba
Well, here's my first attempt:
CODE
Private Sub Form_Current()
Dim sfrmSQL As String
If Forms.frmArticleEntry.AppID Is Not Null Then
sfrmSQL = "SELECT tblLookupArticleCategory.Category, tblLookupArticleCategory.AppID, tblLookupArticleCategory.CatID
FROM tblLookupArticleCategory
WHERE (((tblLookupArticleCategory.AppID)=Forms!frmArticleEntry!AppID));"
Else: sfrmSQL = "SELECT tblLookupArticleCategory.Category, tblLookupArticleCategory.AppID, tblLookupArticleCategory.CatID
FROM tblLookupArticleCategory;"
End If
frmSelectCategorySubform.cmbCategory.RowSource = sfrmSQL
End Sub

- I get error 438 (object does not support this property or method) on the IF statemement if I open the subform on its own, and 424 (object required) when I open the main form containing the subform. Basically I don't know how to
  • Programmatically check for a null value in the parent form's control
  • Set and apply the SQL on the child form's combo box

Should I perhaps be checking the table itself for a null value, rather then the control on the form? My apologies if my questions seem obtuse.
Yuba
To those reading (esp William, thanks for your help so far) - I've abandoned this line of enquiry and will post another question based on current issues.
Cheers
Yuri
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.