Solved Cascading Combo Boxes on a Continuous Form and keep previous record data displayed

[raziel3]

I'm trying the method to cascade combo boxes as described in this post

https://www.access-programmers.co.u...boxes-in-datasheet-or-continuous-form.275155/

But I am not getting it to work.

The Process:
After making the selection in cbo1,
......└ cbo2 rowsource is filtered.
............└After making a selection in cbo2 it filters cbo3.

This works as it should but as we all know on a continuous form, once data is changed in cbo2 only the related records will be on display in cbo3 for the previous records. As seen below

The Link by @CJ_London addressed that issue. There are other solutions to this like putting a transparent text box control over the combos etc but this method is the cleanest I've seen.

For testing purposes, I left cbo2 unbounded. Just need some help as to where I'm going wrong.

 

[CJ_London]

you are not following the example.

e.g. you have in your db

Private Sub cbo1_AfterUpdate()
Me.cbo2.Requery
End Sub

in the example for the equivalent control the code is

Private Sub cboProductType_AfterUpdate()

    'reset downstrean comboboxes
    cboColour = ""
    cboColour_AfterUpdate
    
End Sub

 

[raziel3]

you are not following the example.

e.g. you have in your db

Private Sub cbo1_AfterUpdate()
Me.cbo2.Requery
End Sub

in the example for the equivalent control the code is

Private Sub cboProductType_AfterUpdate()

    'reset downstrean comboboxes
    cboColour = ""
    cboColour_AfterUpdate
  
End Sub

It's not on cbo2 I want the method to work it's on cbo3.

I'm getting it to display properly but I am not able to filter cbo3 after updating cbo2

cbo3.RowSource = "SELECT DISTINCT FGVariants.FGVID," & _
                "[FGMD] & ' ' & [RMSUB].[RMCODE] & ' ' & [FGVariants].[DIMEN] AS [DESC]," & _
                "FGTYPE.FGTD, RMVariants.RMVID, FGVariants.LGTHft, FGVariants.WDTHft " & _
                "FROM ((FGTYPE LEFT JOIN FGMAS ON FGTYPE.FGTID = FGMAS.FGTID)" & _
                "LEFT JOIN (FGVariants LEFT JOIN RMSUB ON FGVariants.RMSUBID = RMSUB.RMSUBID)" & _
                "ON FGMAS.FGMID = FGVariants.FGMID)" & _
                "LEFT JOIN RMVariants ON RMSUB.RMSUBID = RMVariants.RMSUBID " & _
                "WHERE (((RMSUB.RMSUBID)=[Forms]![frmTable1]![txt1]) AND ((FGMAS.FGMID)=[Forms]![frmTable1]![cbo2])) " & _
                "ORDER BY [FGMD] & ' ' & [RMSUB].[RMCODE] & ' ' & [FGVariants].[DIMEN];"

The parameter
"WHERE (((RMSUB.RMSUBID)=[Forms]![frmTable1]![txt1]) AND ((FGMAS.FGMID)=[Forms]![frmTable1]![cbo2])) " & _
is where I'm getting the error. If I remove it, all the items are shown in cbo3.

 

[GPGeorge]

Here's a link to a demo database where I implemented a different approach to combo boxes in continuous forms using tempvars instead of hard-coded form references, but ALSO side-stepped the problem with unbound combo boxes by formatting the fields in the query differently. This doesn't hide any records, it only flags and sorts them differently.

 

[bastanu]

I am doing something like in this updated file; the combo looses the values for the brief moment it drops down to select a new value until you exit (you can even add code to its AfterUpdate event to immediately set focus to another control so the exit code fires.

Cheers,

 

[MajP]

I did a demo on your first combo so you can see the idea. I did what I think is the @CJ_London version, but I cannot get it to work properly. The values show and hide as you click on the form. In the second demo I did it with the textbox. I find the second method more reliable.

 

[CJ_London]

is where I'm getting the error. If I remove it, all the items are shown in cbo3.

I don't see any code in any cbo2 event

 

[raziel3]

I don't see any code in any cbo2 event

I removed it. In your example, I was only interested in techinque1 so my cbo2 is your cboProductType. I tried to follow everything related to cboProductType

As it is the On Load event triggers Call RowSourceDisplay.

I was thinking, can I use a parameter query to filter cbo3's row source after cbo2 selection?

Something along the lines of this
https://www.oreilly.com/library/view/access-cookbook/0596000847/ch01s17.html

 

[MajP]

@CJ_London
Following you example I thought the trick was simply when you enter you change the criteria to a filtered rowsource and when you leave you unfilter it.

Private Sub cbo2_Enter()
  Dim strSql As String
  If Not IsNull(Me.cbo1) Then
    strSql = "SELECT DISTINCT FGMAS.FGMID, FGMAS.FGMD, FGVariants.RMSUBID FROM FGMAS LEFT JOIN FGVariants ON FGMAS.FGMID = FGVariants.FGMID "
    strSql = strSql & "WHERE FGVariants.RMSUBID = " & Me.cbo1 & " ORDER BY FGMAS.FGMD "
    Me.cbo2.RowSource = strSql
  End If
End Sub


Private Sub cbo2_Exit(Cancel As Integer)
   Dim strSql As String
   strSql = "SELECT DISTINCT FGMAS.FGMID, FGMAS.FGMD, FGVariants.RMSUBID FROM FGMAS LEFT JOIN FGVariants ON FGMAS.FGMID = FGVariants.FGMID ORDER BY FGMAS.FGMD "
   Me.cbo2.RowSource = strSql
End Sub

So this kind of works but it flckers see my example. Sometimes values show and sometimes they do not. What am I missing?

 

[raziel3]

I am doing something like in this updated file; the combo looses the values for the brief moment it drops down to select a new value until you exit (you can even add code to its AfterUpdate event to immediately set focus to another control so the exit code fires.

Cheers,

This is nice.

 

[arnelgp]

there are 2 dummy textboxes "over" combo2 and combo3.
and will correctly show the "cascading" combo, without
spilling the second combo to the rest of the rows.

 

[raziel3]

Thanks @arnelgp. That looks very good.

I'm seeing everyone taking my vba sql and making a query gui with it in order for the continuous form to work. Is that the proper way to do it? Or can the same thing be done through vba?

Because one of the problems I was having was trying to pass the parameter through vba at this line

"WHERE (((RMSUB.RMSUBID)=[Forms]![frmTable1]![txt1]) AND ((FGMAS.FGMID)=[Forms]![frmTable1]![cbo2])) " & _

 

[arnelgp]

aside from the two textbox, i make the table 3 columns.
i used query because, i follow your Relationships, and therefore
easy to build from your Relationship.
sorry if i did not answer your question.

 

[raziel3]

After weeks of debugging this I finally got @CJ_London method to work. Things to take note of:

ComboA - The combo that provides the parameter
ComboB - The combo that will display the filtered and non filtered Rowsource

1. ComboB's Rowsource must be set in the Property Sheet. This query/table Rowsource must have the same amount of columns as well as the same order as the VBA's Select Statement. It must be the exact same query as stated in the Property Sheet.

2. In the AfterUpdate event of ComboA you have to put ComboB = 0 or "".

3. Only the parameter query is needed in VBA but you MUST separate the WHERE Clause. Like this:

Private Sub LoadFGVIDCombo(Filtered As Boolean) 'technique 2

Dim sqlFGMID As String
'use this technique where the combo box does not display the bound column once selected

'refresh the rowsource to show all options
'no need to sort since this is a simple lookup, never displayed as a list
sqlFGMID = "SELECT FGVariants.FGVID " & _
                        ",[FGMD] + ' ' + [RMCODE] AS FGVD " & _
                        ",FGVariants.FGMID " & _
                        ",FGVariants.RMSUBID " & _
                        "FROM (FGVariants " & _
                        "LEFT JOIN RMSUB ON FGVariants.RMSUBID = RMSUB.RMSUBID) " & _
                        "LEFT JOIN FGMAS ON FGVariants.FGMID = FGMAS.FGMID"
                    
cboFGVID.RowSource = sqlFGMID
cboFGVID.Requery

'now change the rowsource filtering to include the upstream combos
'- this will only affect the current combobox until it loses focus
If Filtered Then
    'wait for initial refresh to complete
    DoEvents
    'apply filter and order - note the use of the combobox name
    '- this only works if the rowsource is written directly as sql and not as a query
    cboFGVID.RowSource = sqlFGMID & " WHERE FGVariants.FGMID=[cboFGMID]" & _         <--------WHERE CLAUSE
                                                "AND FGVariants.RMSUBID=[txtCOILRMSUB] ORDER BY FGVID"
End If

End Sub

4. The sub must be set FALSE on the Load Event of the form.

After doing all this then you will be able to Call the sub ie the parameter query after any event you desire.