Updating second combo box via first one

[lck092580]

Hi,

I have two combo boxes on my form and basically what I want it to do is so that when I select a value in the first combo box, it takes that value and initiates a query to fill in the second combo box's RowSource with a query.

I got that going with After_Update. However, what's happening now is that if I change the value of the first combo box again, it will update the second combo box's list but won't clear out the existing value selected.

Is there a way for me to clear the value in there? THanks.

 

[Remou]

Have you seen Combo Box - dependant on another Combo Box
faq702-4289
Specifically, the bit about requerying.

 

[lck092580]

I tried using the .requery but it doesn't clear the combo box listing so if I were to:

Select something in combo box 1, then something in combo box 2.

Then go back to change the value in combo box 1, the previous selection is still in there.

 

[Remou]

Ok, so you have Combo1 set to, say,

[tt]ID Part
1 A
2 X [/tt]

And Combo2 SQL set to:

[tt]Select ID, Desc From tblDesc Where ID = Forms!frmForm!Combo1[/tt]

And you have code in the After Update event of Combo1 and the Current event of the form, if appropriate, like this:
[tt]Private Sub Combo1_AfterUpdate()
Me.Combo2.Requery
End Sub[/tt]

Is the above true?

 

[lck092580]

Sort of. However, what I did to update the second combo box is put an event procedure into the after_update field in combo2 to have:

Private Sub combo2_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT ID from someTable where someValue = " & me.combo1 & " ;"
Me.combo2.RecordSource = strSQL
me.combo2.requery
End Sub

Thanks.

 

[Remou]

I do not think that the above will work out. Change the code to the After Update event of Combo1.

 

[lck092580]

Ok, I've changed it and got rid of the initial after_update code I had. I only have combo2.requery in there now.

The form works probably with the Forms!frmForm!Combo1 but it still doesn't appear to be getting rid of the value that was previously selected. When you hit the drop down list, you get the updated stuff but the combo box still shows what was selected last.

Is there a method to force combo2 to blank out the value? I tried setting it to "" but it doesn't work either.

Thanks.

 

[Remou]

I think that combo2 must be a bound combo. Check the properties of the field to which it is bound, the field may allow Nulls, if it does not allow zero length strings ("").

 

[Mack2]

Within the query that the second combo box is based on, go to the criteria line and point to the value of the first combo box ( [form].[fieldname].value )