Cascading Comboboxes on Form Close (1 Viewer)

[Matty]

Hi,

On my form, I have a set of three comboboxes -- Manufacturer, EquipType and Model. Model is dependent on the two previous combos, so I've made it a cascading combobox. Here's the rowsource of cboModelID:

SELECT [dbo_Models].[ModelID], [dbo_Models].[Model] FROM dbo_Models WHERE ((([dbo_Models].[ManufacturerID])=[Forms]![frmRA]![cboManufacturerID]) And (([dbo_Models].[EquipTypeID])=[Forms]![frmRA]![cboEquipTypeID]));

It works wonderfully, but I hit problems when I close my form. At the DoCmd.Close line in my code, it asks for the value in the two comboboxes as parameter popup boxes. It almost looks like cboModelID is trying to requery, but the two other combos have already closed, therefore it's not able to grab their values.

Has anyone else had this problem? If so, do you have any solutions I could try?

 

[adam_fleck]

Not sure but maybe this might help

It's a bit of a fuss to do this, but it might be worth it.

Put code on the After Update events of the Manufacturer and EquipType combos.

The code on the AfterUpdate event for the EquipType combo might look something like this:

Private Sub EquipType_AfterUpdate()

    Dim str_SQL As String
    
    str_SQL = "SELECT [dbo_Models].[ModelID], [dbo_Models].[Model]" & _
              " FROM dbo_Models" & _
              " WHERE [dbo_Models].[ManufacturerID])=" & Me.cboManufacturerID & _
              " And   [dbo_Models].[EquipTypeID])=" & Me.cboEquipTypeID & ";"

    Me.cboModelID.RowSource = str_SQL

End Sub

This way, the rowsource for cboModelID will be changed only when the user changes the EquipType, so there won't be any requery when the Form closes.

 

[Matty]

Thanks for the response.

I actually had considered that, but I also agreed that it was a bit of a fuss. What I had done was populate the combo's recordsource on the Form_Activate event and set it to "" just before the DoCmd.close command. It seemed to work, but it still was a pretty dirty fix.

It turns out I'm going to design this database like the last one I did -- rather than loading/unloading forms all the time, I'm going to load all the major forms at the start and make them visible/invisible when buttons/labels get clicked. It'll solve my problem, since I'm not using code to close the form each time, just making it invisible. I've used cascading comboboxes on that previous database, so I'm hoping it'll work just as nicely on this one.