Trying to limit contents in combo box on subform
Bob A.
using a combo box (it is a lookup wizard) in a sub form. Its Row Source is
now set to a query. I want to limit the combo box "choices" based on the
main form's data.
When running the query, by itself, it will ask for the parameter (my QBE
criteria), to which I am trying to limit the choices, from the main form.
Once the parameter is entered the query will display the correct data.
When opening the sub form, by itself, the parameter request prompts (that's
okay). Then, all of the sub form rows come up (sure), but only the records
having data matching the parameter will display a value (correctly
functioning?). I can accept that, if necessary, since this sub form is not
meant to be worked by itself. The combo box pull down only displays the
proper selections - very good.
When opening the main form, the parameter request does not prompt. That is
as expected (I wouldn't want to have to enter it). The sub form will display
the proper items. However, in my combo box, records matching only one
parameter will display, so the rest are blank. The combo box value don't
change based on the query parameter. What could be wrong?
Douglas J. Steele
be the one described in http://www.fmsinc.com/free/tips.html#tip16foraccess?
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"Bob A." <a...@me.com> wrote in message
news:I7MJf.35102$H71....@newssvr13.news.prodigy.com...
Bob A.
suppose this example shows how to use a Page Header on a form (with
DLOOKUP). I think my trouble may be poor table design, at least. I was
hoping to stay with a combo box on my sub form - one that would filter the
available selections offered based on a control on the parent form. I will
continue studying this example. I might be missing the point. ;-)
Bob
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:%23RkIExP...@tk2msftngp13.phx.gbl...
Bob A.
to explain it?
Bob A.
"Bob A." <a...@me.com> wrote in message
news:dG%Lf.61004$PL5....@newssvr11.news.prodigy.com...
Douglas J. Steele
one.
It's fairly complicated to explain (this is actually part of my February,
2006 "Access Answers" column in Pinnacle Publication's "Smart Access".
Unfortunately, under the terms of my contract with them, I'm not allowed to
reprint articles until 6 months after they print them...)
The basic problem is that while you may think there are multiple combo boxes
on the form, Access only maintains a single recordset for all of the combo
boxes on the form, rather than one recordset for each combo box. If the
underlying recordset isn’t relevant to the specific record being looked at,
the combo box will not contain the necessary record, and you’ll get blanks
displayed instead of values.
There is a fairly simple work-around, though.
Recognize that there are two reasons for the combo box. One reason, of
course, is to allow the user to select from a list that’s customized based
on other selections made. If you look in the accompanying database, you’ll
see that even though not all of the values are being shown in the form, it’s
still possible to use the combo box to select a value for a particular row.
The other reason for the combo box is to handle the fact that the underlying
table only contains a foreign key pointing to the desired entry in some
other table. The combo box allows you to translate that foreign key to
something a little more meaningful. This is the function that’s failing in
the continuous view.
What you can do to get around this problem is ensure that the underlying
recordset of the form contains not only the foreign key value, but also the
desired description. You do this by creating a query that joins together the
underlying table and whatever other table(s) contain the description, and
using that query as the form’s recordset.
Once you’ve got the description available to you in the form, the question
is how to display it. The “trick” is to have a bound text box that displays
the description, and place it right on top of the combo box on your form.
Make the text box marginally narrower than the combo box (I find that a
difference of 250 twips, which is about 0.17” or 0.44 cm, works well). Put
some code in the text box so that when the user enters the text box, the
combo box is refreshed (to ensure that it’s based on the correct recordset)
and set focus to it:
Private Sub txtProductName_GotFocus()
' When the user enters the text box that contains the product name,
' focus will be moved to the underlying combo box instead.
Me.cboProducts.SetFocus
End Sub
Hope this is enough to get you going.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"Bob A." <a...@me.com> wrote in message
news:mo0Mf.39798$H71....@newssvr13.news.prodigy.com...