Controling Values in a Combobox

[chrisjrice]

I have two combo box's. The data in each combo box is loaded by seperate querys.

The are 2 seperate lists of data that i want to load into combobox two, and I want to control which list is loaded into combobox two by the value selected in combobox one. (There are only 2 values loaded into combo box one).

The query that loads combobox has an expression on one of the fields that says where value is equal to form.combobox one). The This works fine when you first load the form. Combobox two displays the list corresponding to the initial value in combobox one. If you then change combobox one, combobox two has no data. I have tried a me.refresh and a me.requery in the combobox on update event, this does not work. Can anyone help???

 

[Rich]

Are the two lists from the same query or are you trying to switch the record source of combo two?

 

[chrisjrice]

One query loads both lists.

Basically the query is over a file which has a Code and Description.
There are 2 codes: 'A' and 'B'. Each code has several descriptions. The query has a condition on the code field. The query selects the records depending on the code. So if code A is passed into the condition then all the descriptions for code A are selected by the query.

The condition in the query uses the value retrieved from Combo Box one.

Combo box one is initially set to 'A' when you open the form. So combo box two displays all the descriptions for code 'A'.

When you select code 'B' in combo box one, Combo box two becomes empty. I cannot get it to re-load the descriptions for code 'B'.

If you run the query manually it selects records for both.

Hope that makes more sense.

Chris

 

[Rich]

Try combo2.Requery, change the name to suit

 

[chrisjrice]

Nice idea, i thought you had it sussed then. Unfortunatly that did not work. I have also tried setting the record source type and record source within the VB code, still no luck.

Thanks

Chris Rice

 

[Emohawk]

Just to get this straight.

The second query results (for Combo2) are dependant on the current value of the first combo box (Combo1)

Try putting the requery in the On_change event. I.e.

Sub Combo1_Change
Combo2.Requery
End Sub

If that doesn't work post your code here so we can get a better look.

 

[chrisjrice]

Yep that's right.

I will put the code up, but I will need some time to right the reply.
One thing I'd better clear up is that the table the query is over is a linked table and it is using an ODBC connection to our AS\400. Since doing what you have said I have been recieving an ODBC Failed error. I am not sure why I am getting this. Maybe the connection needs refreshing and I am not sure how to do that yet.

Thanks for all your time, I will stick the code in ASAP.

Thanks

Chris

 

[Alexandre]

3. If combo 1 is bound to a field, you must requery combo2 from the form OnCurrent event

(to make sure that combo2 still display the right values after switching from a record to another)