Combo box behavior on continuous form

[jcazmail-groups@yahoo.com]

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.idForm s, tblForms.FormNu mber, tblForms.FormNa me,
tblForms.idCust omers FROM tblForms ORDER BY tblForms.FormNu mber;

Then I adding the following to the GotFocus event of the combo box and
the Current event of the form:

cboidForms.RowS ource = "qryDesignOrder DropDown"
cboidForms.Requ ery

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.

[Allen Browne]

Re: Combo box behavior on continuous form

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(.Val ue), "(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.co m> wrote in message
news:1112900442 .768034.183790@ o13g2000cwo.goo glegroups.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.idForm s, tblForms.FormNu mber, tblForms.FormNa me,
> tblForms.idCust omers FROM tblForms ORDER BY tblForms.FormNu mber;
>
> Then I adding the following to the GotFocus event of the combo box and
> the Current event of the form:
>
> cboidForms.RowS ource = "qryDesignOrder DropDown"
> cboidForms.Requ ery
>
> 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]

[jcazmail-groups@yahoo.com]

Re: Combo box behavior on continuous form

Thanks for the tip. Unfortunately, the code isn't quite fully
functional.

As you guessed, my combo's bound column is zero length so at your
suggestion, I duplicated the code in the parent's Current event.
Naturally I had to include a reference to the subform because the combo
on the subform is not part of the parent. So, here's the offending
line:

Me.frmOrdersDes ignSubForm![cboidForms].RowSource = strSQL

If I open the parent from Design mode, it works perfectly. However,
when I try to open the parent form from the database window, I get an
error message about an invalid reference, presumably because the link
between parent and child has not yet been established (though IMHO, it
should be)

So the question is, how do I get the parent to hold off on the
assignment of the SQL statement until the subform is loaded?

[Allen Browne]

Re: Combo box behavior on continuous form

Try:
Me.frmOrdersDes ignSubForm.Form ![cboidForms].RowSource = strSQL

Explanation of the ".Form" bit:

http://allenbrowne.com/casu-04.html

--
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.co m> wrote in message
news:1112945115 .148697.89450@l 41g2000cwc.goog legroups.com...[color=blue]
> Thanks for the tip. Unfortunately, the code isn't quite fully
> functional.
>
> As you guessed, my combo's bound column is zero length so at your
> suggestion, I duplicated the code in the parent's Current event.
> Naturally I had to include a reference to the subform because the combo
> on the subform is not part of the parent. So, here's the offending
> line:
>
> Me.frmOrdersDes ignSubForm![cboidForms].RowSource = strSQL
>
> If I open the parent from Design mode, it works perfectly. However,
> when I try to open the parent form from the database window, I get an
> error message about an invalid reference, presumably because the link
> between parent and child has not yet been established (though IMHO, it
> should be)
>
> So the question is, how do I get the parent to hold off on the
> assignment of the SQL statement until the subform is loaded?[/color]

[jcazmail-groups@yahoo.com]

Re: Combo box behavior on continuous form

Thanks, again, but unfortunately, something must be wrong with my
system. I made the change you suggested but I'm still getting:

runtime error: 2455
you entered an expression that has an invalid reference t the property
Form/Report.

I read the link you provided and I even went to the MS knowledge base:

http://support.microsoft.com/kb/q113352/


That page shows what you suggested, namely, to type:

Forms![main form name]![subform control name].Form![control name]

But I still get the error message. So...something must be wrong with my
MS Access installation.

Thoughts?