Filtering a combo box based on another combo box

[chohan78]

hi,

I am trying to select a value from one combo box and on the basis of this selection the other combo box show only those values which have link to the value I have selected.
I tried to search few links but couldn't get a proper answer, could anyone please help me >>

thanks.

 

[pr2-eugin]

I am not sure, which links you are using, but here are some good links.

http://www.fmsinc.com/MicrosoftAccess/Forms/combo-boxes/cascading.html
http://www.fontstuff.com/access/acctut10.htm
http://www.baldyweb.com/CascadingCombo.htm

 

[chohan78]

hi Paul,

I have checked the same links but couldn't understand the function. basically I have two tables one with complaint Reasons and other is the detail e.g. select a reason called "Damaged Goods" and the next combo box will open all the related reason for this which could be Damaged in warehouse, damaged in travelling, damage during loading , or unloading etc.
could you please help ???
thanks.

 

[pr2-eugin]

How are the two tables related? Does the second table have three columns one is the ID, the second being the main type from Table1 and third detailed expression? How are your tables set up? Be more specific.

 

[chohan78]

Please see below:
tbl_complaints:
ID (Primary Key)
Complaint Type (Text)

tbl_complaints_Details:
ID (Autonum)
Title (Text)
Description (Text)
Complaint Type (Foreign Key to Complaint Type in tbl complaints.
Row Source = SELECT tbl_complaint_Type.[ID], [tbl_complaint_Type].[Complaint_Type] FROM tbl_complaint_Type;

 

[chohan78]

hi Paul,

could you please check the above ??
thanks

 

[pr2-eugin]

Can you please upload a Stripped DB?

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)

 

[chohan78]

hi Paul,

I have attached the database for your review. I would appreciate if you could help me that would be great.

thanks

 

[pr2-eugin]

Here it is

 

[chohan78]

Could you please explain a little bit...... I am a little dumb sometimes>>

 

[pr2-eugin]

Okay so I first created a ComboBox (storeCombo) set its RowSource as

SELECT [tblStore].[StoreID], [tblStore].[strStoreName] FROM tblStore ORDER BY [strStoreName];

Then I created another ComboBox (managerCombo)

SELECT tblManager.ManagerID, tblManager.strManagerName FROM tblManager ORDER BY tblManager.strManagerName;

Then in the AfterUpdate event of the storeCombo, I added the code.

Private Sub storeCombo_AfterUpdate()
    Dim strSQL As String
    strSQL = "SELECT tblManager.ManagerID, tblManager.strManagerName " & _
             "FROM tblManager " & _
             "WHERE tblManager.StoreID = " & Me.storeCombo & _
             " ORDER BY tblManager.strManagerName;"
    Me.managerCombo.RowSource = strSQL
    Me.managerCombo.Requery
End Sub

This is nothing but, I am using the storeCombo's value to filter the managers.

 

[chohan78]

That's Great !

but when you created the combo boxes the wizard ask you where to choose the values from, do you select the table in the wizard or just draw a normal combo box and then assign the Row source ??? this is the bit where I am always confused.

 

[chohan78]

can we do this function from the same table as well??

 

[pr2-eugin]

Either way ! I use to create the Query on my own. I do not think it really does matter as using the Wizard is more swift and simple if you are using ComboBoxes in early stages of your development.

 

[chohan78]

Thanks Paul,
can we do this function from the same table ??

 

[pr2-eugin]

You mean the second combo also looks into the same table the first combo looks at? If so yea. You can do it.

 

[chohan78]

Sorry about raining of questions :
how could I do that ?? I mean what the code will be, the table reference will be same ?? e.g. if add both fields in a same table and how I will enter the data?
SELECT [tblStore].[StoreID], [tblStore].[strStoreName] FROM tblStore ORDER BY [strStoreName];
SELECT tblStorer.ManagerID, tblManager.strManagerName FROM tblManager ORDER BY tblStore.strManagerName;

and the Event Procedure:
rivate Sub storeCombo_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT tblManager.ManagerID, tblManager.strManagerName " & _
"FROM tblManager " & _
"WHERE tblManager.StoreID = " & Me.storeCombo & _
" ORDER BY tblManager.strManagerName;"
Me.managerCombo.RowSource = strSQL
Me.managerCombo.Requery
End Sub

 

[wheatlandacctech]

hi,

I am trying to select a value from one combo box and on the basis of this selection the other combo box show only those values which have link to the value I have selected.
I tried to search few links but couldn't get a proper answer, could anyone please help me >>

thanks.

This is what I'm trying to do except that my comboboxes are on a subform. How do I do this?

Thanks,

Joel

 

[CJ_London]

You are responding to an 8 year old thread. You’ve already started one and have responses and examples provided. Neither of the above 2 responders have been seen since 2017 so unlikely you will have much luck here

 

[The_Doc_Man]

First, given the age of the post, you might do better by just opening a new thread that refers to this thread. To open a thread that refers to a prior thread, navigate to the old thread to be reference. Right-click in the web address box that is just under the title bar and if the whole line is highlighted, you should be able to click on "Copy." Then open your new thread and in the body of the new thread, right-click for the drop-down menu and click paste. Or, heck, CTRL/C and CTRL/V work just as well for copy/paste.

But second, the short answer is that a sub-form is just a form that is not on the Access workspace, it's on another form. But the sub-form has its own Form Class Module where code can go. And, in fact, where it SHOULD go in your case, since the combos are in the sub-form. That is where their action events should be kept.