How to setup cascading comboboxes in datasheet subform

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:

-tblCashVouchers
-tblCashVouchersDetails
-tblAccountNames
-tblAccountTypes

tblCashVouchers

tblCashVouchersDetails

tblAccountNames

tblAccountTypes

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 .


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Private Sub CVDAccountNameID_AfterUpdate()
    SetRecordsource

End Sub

Private Sub Form_Current()
    SetRecordsource
End Sub
Sub SetRecordsource()
    Dim strSQL As String

    strSQL = "SELECT ATID, ATAccoutName, ATAccountTypeRef FROM tblAccountTypes WHERE ATAccountTypeRef= " & Me.CVDAccountNameID

    Me.CVDAccountTypeID.RowSource = strSQL
End Sub

Watch a video of how it’s all put together:

Do you need to sort? Try this:

1
    strSQL = "SELECT ATID, ATAccoutName, ATAccountTypeRef FROM tblAccountTypes WHERE ATAccountTypeRef= " & Me.CVDAccountNameID & " ORDER BY ATAccountTypeRef "

Here is the “How to setup cascading comboboxes in subform” database

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.
*******************

Click here for more information on comboboxes and filter related posts.



Offsite Related Information:

“Go Fund Me” Page


($5 suggested amount)

(…10% of your gift amount will go to charity)

Free! Subscribe To Our YouTube Channel!

Free MS Access VBA Programming Course

Facebooktwitterredditpinterestlinkedinmailby feather
Tags: , , , ,