In the Enter event of the combo in the subform, set its RowSource to a SQL
statement. Something like this:
Dim strSql As String
With Me.Parent![MyMainFormCombo]
strSql = "SELECT Field1, Field2 FROM MyCombosTable WHERE " & _
IIf(IsNull(.Value), "(False)", "SomeID = " & .Value) & ";"
End With
Me.[MySubformCombo].RowSource = strSql
That should be all you need unless the combo's bound column is zero-width.
In that case you would need to do the same in the Current event of the main
form as well, so that the other values show up in the combo as well.
That's not very different from what you are doing. But assigning the SQL
directly may be quicker than going through a query. If there are thousands
of records to load into the combo, it might be worthwhile checking whether
it needs to be reloaded, i.e.:
With Me.[MySubformCombo]
If .RowSource <> strSql then
.RowSource = strSql
End If
End With
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
<jcazmail-groups@yahoo.com> wrote in message
news:1112900442.768034.183790@o13g2000cwo.googlegr oups.com...[color=blue]
>I have a child form that has a combo box whose underlying query needs
> to be filtered by a value from a combo box on the parent form.
>
> I have succeeded in doing this by putting the following SQL in the
> rowsource of the combobox:
>
> SELECT tblForms.idForms, tblForms.FormNumber, tblForms.FormName,
> tblForms.idCustomers FROM tblForms ORDER BY tblForms.FormNumber;
>
> Then I adding the following to the GotFocus event of the combo box and
> the Current event of the form:
>
> cboidForms.RowSource = "qryDesignOrderDropDown"
> cboidForms.Requery
>
> This works but is real kludgy. Whenever I change parent records, it has
> a noticeable delay while it requeries for each record in the child
> form.
>
> Surely there's a better way to have a child form's combobox query be
> filtered based on a value from a combobox on the parent form.[/color]
