By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,853 Members | 1,563 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,853 IT Pros & Developers. It's quick & easy.

Combo box behavior on continuous form

P: n/a
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.

Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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.

<jc*************@yahoo.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.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.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.

Nov 13 '05 #2

P: n/a
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.frmOrdersDesignSubForm![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?

Nov 13 '05 #3

P: n/a
Try:
Me.frmOrdersDesignSubForm.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.

<jc*************@yahoo.com> wrote in message
news:11*********************@l41g2000cwc.googlegro ups.com...
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.frmOrdersDesignSubForm![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?

Nov 13 '05 #4

P: n/a
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?

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.