Hi, Bruce -- I'm working to implement your suggested combo boxes
w/criteria
but am having some coding problems, apparently -- Can you help with
that?
If
so, what should I post?? The 2nd combo box is appearing as a parameter
query
for the 1st and not picking up the values ...
Thanks,
Meredith
:
A combo box list (Row Source) may be either a Value List
("Excellent","Good","Fair","Poor") or a Table/Query. Assuming that
ReferralType is a field in a referral table (tblReferral), you could
create
a query (qryRefType) based on tblReferral, with just the field
ReferralType.
Right click the top of the query in design view (above the grid), and
set
Unique Values to Yes.
A query uses code known as SQL behind the scenes. You can see a
query's
SQL
by opening a query and clicking View > SQL. You can also use SQL
directly
as a Row Source. Here is an excerpt from this web page:
http://www.mvps.org/access/forms/frm0043.htm
I have adapted it to your situation.
SELECT DISTINCT ReferralType FROM tblReferral UNION Select "(All)" as
Bogus
FROM tblReferral ORDER BY ReferralType;
There are no line breaks, although some may appear in your newsreader
window.
This should give you a list of ReferralTypes, with (All) at the top of
the
list. The DISTINCT in the code means that even though a ReferralType
appears in many records in tblReferral, it appears only once in the
list.
The combo box column count would be 1, and the column width about 1".
You
can set the Default Value of the combo box to "(All)" so that (All)
appears
even if no choice is made.
You could use a Value List for the combo box Row Source, with "(All)"
as
one
of the choices, but by using the SQL you can change or add the list of
Referral Types without changing the code.
The Row Source for the combo box from which you select the actual
referral
could be something like:
SELECT tblReferral.Referral
FROM tblReferral WHERE
(((IIf([Forms]![frmReferral]![cboReferral]<>"All","",
[Referral] = [Forms]![frmReferral]![cboReferral]))=True))
ORDER BY tblReferral.Referral;
I have tested this code by adapting it from one of my own projects. I
cannot say for sure that it will work in your project since I don't
know
all
of the details of what you have done.
One of my assumptions is that tblReferral contains fields for Referral
(CPS,
etc.) and ReferralType, along with whatever else you need.
For a two-column combo box (with both columns visible), use a
two-column
Row
Source query. Set the column count to 2 and the column widths to
something
like 1";1"
However, I don't think that is what you really need here.
Thank you, Bruce. This seems like a reasonable approach. I haven't
created
a
combo box that would allow choices by a particular field? Not sure
what
that
is, even ... I may take the simple way out and just put the whole
list
into
one box as you also suggested. I'd still include the categories in
the
underyling table for future reporting/marketing purposes, of course.
If you have time, could you point me in the right direction for
creating a
multi-option combo box? Maybe I do know and I don't realize I know.
Ha!
Meredith
:
One possibility is to have a Referral table with a field for
ReferralType.
In ReferralType you could limit the choice to General, Agency, or
Specific.
The user could select one of those categories from a combo box,
which
would
limit the list to just the Referrals in that category. The combo
box
could
have another category for All. If this approach sounds useful,
here
is
some
information about adding (All) as a choice in the combo box. If
the
user
knows the category, they can have a filtered list; if not, they
choose
(All)
and end up with the full list.
There are techniques for managing truly unwieldy lists consisting
of
thousands of records, but for just a few hundred you should do OK
just
by
setting the combo box Auto Expand property to Yes. If the user
types
"M"
they will see the beginning of the entries that start with "M".
Typing
"Me"
may bring them to "Melinda", and then it is a simple matter to
scroll
down
to "Meredith". Or they can keep typing until they see the entry
they
are
seeking.
The real trick here, it seems to me, is data integrity in the
ReferralType
list. You need to guard against the duplicate entries "Child
Protective
Services", "CPS", "C.P.S.", "Child Protective Svcs.", and so forth.
You
may
need to enter more information into the referral table than you
think
may
be
needed, and to check for duplication in the address or phone number
fields,
or an ID number, or probably some combination of fields in order to
assure
there is a single entry for Child Protective Services. A monthly
review
of
the full list will help keep things in order. A few hundred
choices
won't
be a problem, but alternate names or categories for the same entity
could
be.
I understand what you're saying about the importance of the early
design
and
setting relationships, which is why I haven't gone ahead and
created
the
tables or the form until I understand the underpinnings. But,
part
of
designing the database has to take into account its ultimate
purpose,
so
I'm
also thinking about that -- and I don't think cascading boxes
will
work
because they require the user to know which category an item
belongs
to
upfront. That still leaves me with the dilemma of how to make it
easy
on
the
user to put in/choose a referral source and also design the
tables
with
an
eye toward being able to query them intelligently later --
Thanks,
Meredith
:
Meredith
So, from an ease-of-use standpoint, a user could select CPS and
never
know
what category and subcategory CPS belongs to. That makes sense.
But
what
about the reverse situation, when the way to limit the possible
"agencies"
is driven by a choice of categories.
One approach might be to have a pair of "cascading combo boxes",
where
the
user picks a category ("Agency") first, which then limits the
choices
in
a
second combobox to only agencies (e.g., CPS, American Cancer
Society,
...).
With Access, to get the best use of the relationally-oriented
features
and
functions, you really need to get the data nailed down first.
After
you
have "entities" and "relationships" designed, then you can
figure
out
how
to
use queries, forms, and reports to interact with the users.
Good luck!
Regards
Jeff Boyce
Microsoft Office/Access MVP
message
Thanks, Jeff -- I'm still thinking about this, but what I hear
you
suggesting
is similar to what I'm wanting to do. It's logical and easy to
do
as
you
suggest, but what I'm uncertain of is how to 'replicate' this
structure
in
an
input/intake form. The intake person will hear a specific
source
name,
e.g.,
Child Protective Services, and as you said, they don't need to
know
that
CPS
is in a particular category or sub-category. But, there are
probably
200+
such specific sources, and it seems unwieldy to me to put them
all
into
a
drop-down box on the intake form...
Is that what you're saying, or am I missing something?
Many thanks for your help, Jeff,
Meredith
:
Meredith
Let me see if I can paraphrase your data (which seems to only
be
focused
on
the referral aspects at the moment):
* You have Patients
* Patients are Referred
* A Patient Referral comes from a single Source (e.g.,
John
Doe,
