Solved Struggle to cascade Combobox in Subform

[silentwolf]

Hi again,

Sorry beeing a pain and ask again for your help and advice!

It is a extention to my previous thread on Finding Articles easily in long list of Items where CJ_London did a amazing job of helping me!!

However it would be just a little nicer if I can select in a sfmAuftragModelle first the "Lieferant" = cboLieferant and then show only the cboModelle wich belong to the cboLieferant. Lieferant=Supplier or Manufacturer... and Modell is well Model..

The issue I have is that I got tbl_Lieferant to tbl_Modell 1:N relationship
then tbl_AuftragModelle linked to tbl_Modelle and tbl_Auftraege

I got a subform sfm_AuftragModelle where I would like to be able to pic the "Lieferant" and then the Modell and then add the additional Fields from tbl_AuftragModelle.

I add a DataStructure below.

To create a Cascading Combobox in a Single Form is not a Problem when tbl_Lieferant is picked with cboLieferant and update the cboModel
but as there are tblLieferant to tblModel to tblAuftragModel I have not beeing able to put all into one Subform..

I would like to have it as a Continious Form as you can see all Models related to the appropiate "Auftrag"="Order"

This is what I tried so far in the sfmAuftragModel

Private Sub cboLieferant_AfterUpdate()
    cboModel = ""
    cboModel_AfterUpdate
End Sub

Private Sub cboModel_AfterUpdate()
    Me.Requery
End Sub

Private Sub cboModel_GotFocus()
    'use this technique where the combo box does not display the bound column once selected
    popProduct (True) 'technique 2
End Sub

Private Sub popProduct(Filtered As Boolean) 'technique 2
Dim sqlstr 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
    sqlstr = "SELECT Mod_ID, Mod_Bezeichnung FROM tbl_Modell"
    cboModel.RowSource = sqlstr
    cboModel.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
        cboModel.RowSource = sqlstr & " WHERE Mod_Lief_IDRef=[cboLieferant]"
    End If
    
End Sub

Private Sub Form_Load()
    popProduct (False) 'technique 2

End Sub

All this Code is based on CJ_London Code for Cascading Comboboxes... well I modified it so that is why is not working lol
Not that someone things I am saying CJ_London done anything wrong!

Or is there a better way of doing this so it is also visible what "Models" are used in each "Auftrag" (Order)

Struggling with RowSource and how it all needs to be put together..

Currently it will update all records in the Subform with the first cboLieferant picked...

 

[bastanu]

I think what you need to do is to set the row source of the cboModel combo to be unfiltered so it shows the value for all the records, then in its GotFocus (or Enter) event change it to be filtered (like you have it now, I don't know if you really need all that new sub, I would just set the row source, then in LostFocus (or Exit) event reset it to all:

Private Sub cboModel_GotFocus()
    Me.cboModel.RowSource="SELECT Mod_ID, Mod_Bezeichnung FROM tbl_Modell WHERE Mod_Lief_IDRef=" & Me.cboLieferant
    Me.cboModel.DropDown
End Sub
Private Sub cboModel_LostFocus()
    Me.cboModel.RowSource="SELECT Mod_ID, Mod_Bezeichnung FROM tbl_Modell"
    Me.Refresh 
End Sub

Cheers,

 

[silentwolf]

Hi Bastanu,
thanks for your reply..

I am getting closer with your advice but once I change the cboLieferant all of the records are getting the same selected Item.

This is what I got at present..

Private Sub Form_Load()

End Sub

Private Sub cboLieferant_AfterUpdate()

End Sub

Private Sub cboModel_GotFocus()
    Me.cboModel.RowSource = "SELECT Mod_ID, Mod_Bezeichnung FROM tbl_Modell WHERE Mod_Lief_IDRef=" & Me.cboLieferant
    Me.cboModel.Dropdown
End Sub

Private Sub cboModel_LostFocus()
    Me.cboModel.RowSource = "SELECT Mod_ID, Mod_Bezeichnung FROM tbl_Modell"
    Me.Refresh
End Sub

'Queries for cboLieferant
SELECT tbl_Lieferanten.Lief_ID, tbl_Lieferanten.Lief_Firmenname FROM tbl_Lieferanten;
And the Control Source of this combo is empty!

So I guess it is a minor issue what needs to be changed in order to make it work.

As you can see I did leave the Form Load Event empty
And cboLieferant_AfterUpdate also empty.

Query for cboModel
SELECT Mod_ID, Mod_Bezeichnung FROM tbl_Modell
And the Control Source AufM_Mod_IDRef



Cheers

Albert

 

[bastanu]

Albert,

That is the expected outcome of having an unbound combo box in a continuous form; you need to bind it (populate the control source) to a field in the form's recordsource if you want it to show the accurate data for each record.

Cheers,

 

[silentwolf]

Hi Vlad,

I did change the ControlSource to AufM_Auft_IDRef in the cboLieferant and now it seams to work just fine!!!

Thank you sooo much for helping me with this!!

Cheers!!!

 

[bastanu]

You're very welcome!

Cheers,

 

[IbrBadri]

I think what you need to do is to set the row source of the cboModel combo to be unfiltered so it shows the value for all the records, then in its GotFocus (or Enter) event change it to be filtered (like you have it now, I don't know if you really need all that new sub, I would just set the row source, then in LostFocus (or Exit) event reset it to all:

Private Sub cboModel_GotFocus()
    Me.cboModel.RowSource="SELECT Mod_ID, Mod_Bezeichnung FROM tbl_Modell WHERE Mod_Lief_IDRef=" & Me.cboLieferant
    Me.cboModel.DropDown
End Sub
Private Sub cboModel_LostFocus()
    Me.cboModel.RowSource="SELECT Mod_ID, Mod_Bezeichnung FROM tbl_Modell"
    Me.Refresh
End Sub

Cheers,

Dear @bastanu
I searched and find your solution solved my problem in a single Subform Combo-Box.

Thanks a lot for helping all of us.