Subsequent Combo Boxes- how to automatically update?

[add39]

I've been able to get a selection from the 'Division' combo box (cboDiv) to change the options available in the subsequent 'Initiative' combo box (cboInit). The problem is that the initiative box doesn't keep updating everytime I choose a different division...it only changes once I close out of the form and open it up again.

I added the procedure: cboInit.Requery in the After_Update event of cboDiv, but that didn't work.

Here is the row source for cboDiv:
SELECT [qryDiv].[Division] FROM qryDiv;

Here is the row source for cboInit:
SELECT [tblInit].[Initiative] FROM tblInit WHERE ((([tblInit].[Division])=[forms]![frmActual]![cboDiv]));

Just as a side note...is there a better way to accomplish this combo box task?

Thanks,

Av

 

[scriverb]

In the AfterUpdate event procedure of the Division combobox perform a requery of the Initiative combobox:

Me![cboInit].requery

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Rolliee]

Your after-update is not 'firing' Put a msgbox "I fired the requery" in to see that.

I like to reset the record source of a control and then requery it.

rollie@bwsys.net

p.s. need a sample email me and describe what you want


rollie@bwsys.net

 

[add39]

Bob,

I tried adding that procedure and its still not working.

 

[scriverb]

Rollie: I am curious about your comment about reseting the RowSource of the combobox. I haven't ever had the need to do this in a combobox, but I am haunted by why I have to do this to requery a subform where a query is selecting a new set of records based upon a selection from a combobox. Can't get it to refresh the subquery without the reseting of the RecordSource property. I am just reset it with the current query that is already there and perform the subform control .requery command and it all works property.

This has been driving nuts for a long time and I can't find any documention concerning why this happens. Ideas???

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[add39]

Rollie,
I don't really understand what you're saying...exactly what should I do to reset the record source of the control?

Thanks

 

[Rolliee]

I understood that 'add'wanted to populate cbo2 based on the results of cbo1. To do that create the SQL in the click event of cbo1 and set the source of cbo2. Then requery cbo2. That may be an extra step but I have had to use it when the cbo2 apparently did not know it had been changed.

That help???

Rollie E

 

[scriverb]

I don't think the SQL has to be changed at all. It is the same no matter what the selection is. But, the records being returned in the cboInit needs to be reset/requeried with the reference in the Where clause to the new value of cboDiv.

A simple Me![cboInit].Requery in the AfterUpdate Event Procedure should refresh the cboInit combo with appropriate records.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[add39]

The Me![cboInit].Requery procedure isn't solving the problem. I still have to close out of the form and open it again before the update happens.

 

[Rolliee]

I'm out!

 

[add39]

Rolliee, I tried your idea and it worked out! I added to the click event and it worked...

Thanks!

 

[scriverb]

Let's follow Rollie's advise. Put the following into the AfterUpdate of the cboDiv:

Me![cboInit].RowSource = "SELECT [tblInit].[Initiative] FROM tblInit WHERE ((([tblInit].[Division])=[forms]![frmActual]![cboDiv])); "
Me![cboInit].Requery



Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???