Solved Cascading Combo Boxes on a Continuous Form and keep previous record data displayed (1 Viewer)

raziel3

Registered User.
Local time
Today, 15:57
Joined
Oct 5, 2017
Messages
273
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.
 

Attachments

  • Cascade1.jpg
    Cascade1.jpg
    200.2 KB · Views: 228
  • Cascade2.jpg
    Cascade2.jpg
    196.4 KB · Views: 226
  • Cascading Combo.accdb
    988 KB · Views: 229

CJ_London

Super Moderator
Staff member
Local time
Today, 19:57
Joined
Feb 19, 2013
Messages
16,553
you are not following the example.

e.g. you have in your db

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

in the example for the equivalent control the code is

Code:
Private Sub cboProductType_AfterUpdate()

    'reset downstrean comboboxes
    cboColour = ""
    cboColour_AfterUpdate
    
End Sub
 

raziel3

Registered User.
Local time
Today, 15:57
Joined
Oct 5, 2017
Messages
273
you are not following the example.

e.g. you have in your db

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

in the example for the equivalent control the code is

Code:
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

Code:
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.
 

Attachments

  • Cascading Combo.accdb
    900 KB · Views: 217

GPGeorge

Grover Park George
Local time
Today, 12:57
Joined
Nov 25, 2004
Messages
1,776
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

AWF VIP
Local time
Today, 12:57
Joined
Apr 13, 2010
Messages
1,401
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,
 

Attachments

  • CascadingCombo_Updated.accdb
    872 KB · Views: 248

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:57
Joined
May 21, 2018
Messages
8,463
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.
 

Attachments

  • Cascading Combo_MajP.accdb
    1.2 MB · Views: 243

CJ_London

Super Moderator
Staff member
Local time
Today, 19:57
Joined
Feb 19, 2013
Messages
16,553
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

Registered User.
Local time
Today, 15:57
Joined
Oct 5, 2017
Messages
273

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:57
Joined
May 21, 2018
Messages
8,463
@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.

Code:
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

Registered User.
Local time
Today, 15:57
Joined
Oct 5, 2017
Messages
273
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. (y)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:57
Joined
May 7, 2009
Messages
19,169
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.
 

Attachments

  • Cascading Combo.accdb
    860 KB · Views: 248

raziel3

Registered User.
Local time
Today, 15:57
Joined
Oct 5, 2017
Messages
273
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:57
Joined
May 7, 2009
Messages
19,169
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

Registered User.
Local time
Today, 15:57
Joined
Oct 5, 2017
Messages
273
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:

Code:
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.
 
Last edited:

Users who are viewing this thread

Top Bottom