In this post we will discuss how to setup cascading comboboxes in your subform.
This post was in response to this issue that someone was having:
So the real key to solving this issue is the ways the tables are setup.
Here are the tables:
The 1st 2 tables, tblCashVouchers and tblCashVouchersDetails are setup in the typical parent/child relationship.
tblCashVouchersDetails is the detail table, and will hold the id numbers for all the main tables.
This is the table that is used as the recordsource for the subform.
The comboboxes in the subform are based off of 2 tables: tblAccountNames (the main one) and tblAccountTypes (the sub one)
tblAccountNames is related to tblAccountTypes by the “ATAccountTypeRef” field.
After I select an “Account Name”, I requery the form and can select an “Account Type” corresponding to the “Account Name”.
In my opinion, setting the recordsource is better set in VBA when the “AfterUpdate” event is run.
Here is the code, and I am using the AfterUpdate and Current events to set the “CVDAccountTypeID” combobox recordsource .
Private Sub CVDAccountNameID_AfterUpdate()
Private Sub Form_Current()
Dim strSQL As String
strSQL = "SELECT ATID, ATAccoutName, ATAccountTypeRef FROM tblAccountTypes WHERE ATAccountTypeRef= " & Me.CVDAccountNameID
Me.CVDAccountTypeID.RowSource = strSQL
Watch a video of how it’s all put together:
Do you need to sort? Try this:
strSQL = "SELECT ATID, ATAccoutName, ATAccountTypeRef FROM tblAccountTypes WHERE ATAccountTypeRef= " & Me.CVDAccountNameID & " ORDER BY ATAccountTypeRef "
Let me know if you have any questions.
PS. Do your cell values keep deleting? Well make sure the field you are querying doesn’t have the column headers set to 0,1. In other words, don’t hide to first column and show the second, show both, or else your values will look like they are disappearing, but will be in the table.