tables not relating properly

[MeredithS]

Hi, Bruce. I reorganized my table(s) so that all referral info is in one:
Resource ID, Resource Type, Referral Source Label. Then I re-inserted most of
your original code into 2 combo boxes, including the new On Event procedure
as follows:

I think the 1st box works well -- all the categories show up, including
"All", but the 2nd box is still asking for a value as a parameter query...
and the event procedure isn't working. See following:

SELECT DISTINCT ReferralSourcesAll.[Resource Type] FROM ReferralSourcesAll
UNION SELECT "(All)" as Bogus FROM ReferralSourcesAll
ORDER BY ReferralSourcesAll.[Resource Type];

Combo Box #1 named Referral



Private Sub Referral_AfterUpdate()
Me.cboReferral Source.Requery
End Sub

(when I try to run this I get an error message to the effect that it can’t
locate a named object)

SELECT ReferralSourcesAll.[Referral Source Label],
ReferralSourcesAll.[Resource Type]
FROM ReferralSourcesAll
WHERE (((IIf([Forms]![frmIntake Worksheet2]![cboReferral
Type]<>"All","",[Referral Source Label]=[Forms]![frmIntake
Worksheet2]![cboReferral Type]))=True))
ORDER BY ReferralSourcesAll.[Referral Source Label];

Combo Box #2 named Referral Source

Thanks!!

Meredith

Good idea using the one table. A lookup table is fine for holding a list of
values (such as referral type, or on a larger scale, cities or states) from
which the user chooses. The chosen value is stored in the main table (or it
may be linked, but with a single field there is probably little to be gained
by doing that). In your case, a list of referral types can be held in a
ReferralType table, but that value ends up being stored in individual
records in the ReferralSources table, just as city or state typically is
stored with the rest of a person's contact information.

Thanks, Bruce. Let me see what I've got and I'll get back to you, probably
tomorrow. The first combo box does work -- the list shows up and I can
select
an item from it. I think the easiest way to fix what's wrong, for
starters,
is to do what I should have done in the beginning and combine all the
fields
into one table. Not sure why I had 2 tables anyway. I'll work from that
angle...

Meredith

Hi, Bruce -- Here's what I have. I think, at present, the only thing
not
working right is the All feature -- I've probably scavenged/guessed
enough
to
screw up the original code which might have worked exactly as it was.
The
difference seemed to be that I had 2 tables I was drawing from and the
Referral one didn't have the Referral Type as a text name, which was
what
I
wanted to show up in the 1st combo box. So, I was trying to pull from
both
and I think that's what got screwed up. If this doesn't work, as I'm
doing
it, I can simply add a field to Table #2 and get everything in one
table --
probably what I should have done in the 1st place?

2 tables: Resource ID Table (Resource ID, Resource Type) and
ReferralSourcesAll (Resource ID, Referral Source Label)

What is the Row Source for the first combo box (the ReferralType one)?
SELECT DISTINCT [Resource Type] FROM [Resource ID Table] UNION SELECT
"(All)" as Bogus FROM [Resource ID Table]
ORDER BY [Resource Type];

This should work, assuming the fields and table names are correct. To
test,
create a new query in design view. Don't select any tables, and click
View
SQL. Enter the code:

SELECT DISTINCT [Resource Type]
FROM [Resource ID Table]
UNION
SELECT "(All)" as Bogus
FROM [Resource ID Table]
ORDER BY [Resource Type];

The line breaks are just to make it easier to read and follow. Switch to
datasheet view. Do you see the list? If not, create a query in design
view. Add Resource ID Table, click OK, and add just the ResourceType
field.
Switch to datasheet view. If it is OK, switch to SQL view and note
exactly
what you see there.

This combo box is unbound, as I see it.

I can't quite make out the purpose of ReferralSourcesAll. I woud imagine
Referral data something like this:

tblReferralSourcesAll
RefSourceID (autonumber primary key)
ResourceType (Agency, etc.)
Resource (Name of referral)
Phone, Address, etc.

RefSourceID ResourceType Resource
1 Agency CPS
2 General Dr. Welby
3 General Dr. Jekyll

I have left out Phone, etc., as they don't matter for this, although you
may
need them.

The second combo box (I will call it cboSource; note the name, for I will
refer to it again soon) would have as its Row Source:

SELECT tblReferralSourcesAll.Resource
FROM tblReferralSourcesAll
WHERE (((IIf([Forms]![IntakeWorksheet2]![ResourceType]<>"All","",
[Resource] = [Forms]![IntakeWorksheet2]![ResourceType]))=True))
ORDER BY tblReferralSourcesAll.Resource;

This assumes that you are storing the value Agency, etc. from [Resource
ID
Table]. The way you could put that value into tblReferralSourcesAll is
to
make a form based on tblReferralSourcesAll, with a combo box bound to the
ResourceType field (that is, ResourceType is its ControlSource, which
means
that values entered into the combo box are stored in the ResourceType
field). That combo box, with a column count of 1 and a column width of,
say, 1", would have as its Row Source the same Row Source code as
described
for your first combo box. Select the ResourceType, and it will be
inserted
into the field.

Back to your first combo box. In its After Update event you would have
Me.cboSource.Requery. To add this code, open the form's property sheet:
Click the combo box to select it, click View > Properties, and click the
Event tab. Click After Update, click the three dots on the right, and
insert Me.cboSource.Requery between Private Sub and End Sub. What this
does
is to requery the Row Source for cboSource. That is to say, it runs the
Row
Source code, with the selected value as the parameter. If you selected
Agency, then [Forms]![IntakeWorksheet2]![ReferralType] contains the value
"Agency". The Row Source looks for all Resource records that have
"Agency"
as the ResourceType.

You will probably want to put Me.cboSource.Requery into the form's
Current
event, so that when you arrive at a record the Resource text box will
have
the correct list.

I hope this gets you closer to where you need to be.




What is the Row Source for the second combo box (the Referral one)?
Again,
any code?

SELECT [Resource ID Table].[Resource ID], ReferralSourcesAll.[Referral
Source Label], [Resource ID Table].[Resource Type]
FROM [Resource ID Table] INNER JOIN ReferralSourcesAll ON [Resource ID
Table].[Resource ID] = ReferralSourcesAll.[Resource ID]
WHERE ((([Resource ID Table].[Resource Type])=[Forms]![Intake
Worksheet2]![ReferralType]));



Thanks!!

Meredith


:

What is the Row Source for the first combo box (the ReferralType one)?
Does
it have any After Update code or other code? I'm not sure where you
are
in
your Access vocabulary, so at the risk of saying something you already
know,
click the combo box to select it, click View > Properties, and Click
the
Event tab. If any of the Events has [Event Procedure], click the
three
dots
to the right of the row to view the code. The code will go something
like:

Private Sub cboReferralType_After Update

{Code here}

End Sub


What is the Row Source for the second combo box (the Referral one)?
Again,
any code?

In describing the Row Source, remember that I can't see you database,
so
you
should describe any tables and fields that appear if if is not obvious
by
context. For instance, I can assume that Referral is the name of the
Referral, but other things may not be as clear.

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.

 

[BruceM]

Hi, Bruce. I reorganized my table(s) so that all referral info is in one:
Resource ID, Resource Type, Referral Source Label. Then I re-inserted most
of
your original code into 2 combo boxes, including the new On Event
procedure
as follows:

I think the 1st box works well -- all the categories show up, including
"All", but the 2nd box is still asking for a value as a parameter query...
and the event procedure isn't working. See following:

SELECT DISTINCT ReferralSourcesAll.[Resource Type] FROM ReferralSourcesAll
UNION SELECT "(All)" as Bogus FROM ReferralSourcesAll
ORDER BY ReferralSourcesAll.[Resource Type];

Combo Box #1 named Referral



Private Sub Referral_AfterUpdate()
Me.cboReferral Source.Requery
End Sub

(when I try to run this I get an error message to the effect that it can't
locate a named object)

cboReferralSource is the second combo box (the one from which you select the
specific referral entity). Below you have referred to it as Referral
Source. Use your actual combo box name for the requery code.

By the way, you will be doing yourself a favor if you restrict Access names
(tables, forms, fields, controls, etc.) to alphanumeric characters and the
underscore. Surrounding the name in square brackets should eliminate the
problems that can result from spaces, but why give yourself the extra
hassle? Also, I think you will find the shortest possible descriptive name
for a form, field, etc. is the most convenient in code. Finally, giving a
control a different name than the field will eliminate some puzzling error
messages that can appear if Access gets confused.

No need to change anything in your current project. These are just ideas
for the future.

SELECT ReferralSourcesAll.[Referral Source Label],
ReferralSourcesAll.[Resource Type]
FROM ReferralSourcesAll
WHERE (((IIf([Forms]![frmIntake Worksheet2]![cboReferral
Type]<>"All","",[Referral Source Label]=[Forms]![frmIntake
Worksheet2]![cboReferral Type]))=True))
ORDER BY ReferralSourcesAll.[Referral Source Label];

Combo Box #2 named Referral Source

Thanks!!

Meredith

Good idea using the one table. A lookup table is fine for holding a list
of
values (such as referral type, or on a larger scale, cities or states)
from
which the user chooses. The chosen value is stored in the main table (or
it
may be linked, but with a single field there is probably little to be
gained
by doing that). In your case, a list of referral types can be held in a
ReferralType table, but that value ends up being stored in individual
records in the ReferralSources table, just as city or state typically is
stored with the rest of a person's contact information.

Thanks, Bruce. Let me see what I've got and I'll get back to you,
probably
tomorrow. The first combo box does work -- the list shows up and I can
select
an item from it. I think the easiest way to fix what's wrong, for
starters,
is to do what I should have done in the beginning and combine all the
fields
into one table. Not sure why I had 2 tables anyway. I'll work from that
angle...

Meredith


:

Hi, Bruce -- Here's what I have. I think, at present, the only thing
not
working right is the All feature -- I've probably scavenged/guessed
enough
to
screw up the original code which might have worked exactly as it
was.
The
difference seemed to be that I had 2 tables I was drawing from and
the
Referral one didn't have the Referral Type as a text name, which was
what
I
wanted to show up in the 1st combo box. So, I was trying to pull
from
both
and I think that's what got screwed up. If this doesn't work, as I'm
doing
it, I can simply add a field to Table #2 and get everything in one
table --
probably what I should have done in the 1st place?

2 tables: Resource ID Table (Resource ID, Resource Type) and
ReferralSourcesAll (Resource ID, Referral Source Label)

What is the Row Source for the first combo box (the ReferralType
one)?
SELECT DISTINCT [Resource Type] FROM [Resource ID Table] UNION
SELECT
"(All)" as Bogus FROM [Resource ID Table]
ORDER BY [Resource Type];

This should work, assuming the fields and table names are correct. To
test,
create a new query in design view. Don't select any tables, and click
View
SQL. Enter the code:

SELECT DISTINCT [Resource Type]
FROM [Resource ID Table]
UNION
SELECT "(All)" as Bogus
FROM [Resource ID Table]
ORDER BY [Resource Type];

The line breaks are just to make it easier to read and follow. Switch
to
datasheet view. Do you see the list? If not, create a query in
design
view. Add Resource ID Table, click OK, and add just the ResourceType
field.
Switch to datasheet view. If it is OK, switch to SQL view and note
exactly
what you see there.

This combo box is unbound, as I see it.

I can't quite make out the purpose of ReferralSourcesAll. I woud
imagine
Referral data something like this:

tblReferralSourcesAll
RefSourceID (autonumber primary key)
ResourceType (Agency, etc.)
Resource (Name of referral)
Phone, Address, etc.

RefSourceID ResourceType Resource
1 Agency CPS
2 General Dr. Welby
3 General Dr. Jekyll

I have left out Phone, etc., as they don't matter for this, although
you
may
need them.

The second combo box (I will call it cboSource; note the name, for I
will
refer to it again soon) would have as its Row Source:

SELECT tblReferralSourcesAll.Resource
FROM tblReferralSourcesAll
WHERE (((IIf([Forms]![IntakeWorksheet2]![ResourceType]<>"All","",
[Resource] = [Forms]![IntakeWorksheet2]![ResourceType]))=True))
ORDER BY tblReferralSourcesAll.Resource;

This assumes that you are storing the value Agency, etc. from
[Resource
ID
Table]. The way you could put that value into tblReferralSourcesAll
is
to
make a form based on tblReferralSourcesAll, with a combo box bound to
the
ResourceType field (that is, ResourceType is its ControlSource, which
means
that values entered into the combo box are stored in the ResourceType
field). That combo box, with a column count of 1 and a column width
of,
say, 1", would have as its Row Source the same Row Source code as
described
for your first combo box. Select the ResourceType, and it will be
inserted
into the field.

Back to your first combo box. In its After Update event you would
have
Me.cboSource.Requery. To add this code, open the form's property
sheet:
Click the combo box to select it, click View > Properties, and click
the
Event tab. Click After Update, click the three dots on the right, and
insert Me.cboSource.Requery between Private Sub and End Sub. What
this
does
is to requery the Row Source for cboSource. That is to say, it runs
the
Row
Source code, with the selected value as the parameter. If you
selected
Agency, then [Forms]![IntakeWorksheet2]![ReferralType] contains the
value
"Agency". The Row Source looks for all Resource records that have
"Agency"
as the ResourceType.

You will probably want to put Me.cboSource.Requery into the form's
Current
event, so that when you arrive at a record the Resource text box will
have
the correct list.

I hope this gets you closer to where you need to be.




What is the Row Source for the second combo box (the Referral one)?
Again,
any code?

SELECT [Resource ID Table].[Resource ID],
ReferralSourcesAll.[Referral
Source Label], [Resource ID Table].[Resource Type]
FROM [Resource ID Table] INNER JOIN ReferralSourcesAll ON [Resource
ID
Table].[Resource ID] = ReferralSourcesAll.[Resource ID]
WHERE ((([Resource ID Table].[Resource Type])=[Forms]![Intake
Worksheet2]![ReferralType]));



Thanks!!

Meredith


:

What is the Row Source for the first combo box (the ReferralType
one)?
Does
it have any After Update code or other code? I'm not sure where
you
are
in
your Access vocabulary, so at the risk of saying something you
already
know,
click the combo box to select it, click View > Properties, and
Click
the
Event tab. If any of the Events has [Event Procedure], click the
three
dots
to the right of the row to view the code. The code will go
something
like:

Private Sub cboReferralType_After Update

{Code here}

End Sub


What is the Row Source for the second combo box (the Referral one)?
Again,
any code?

In describing the Row Source, remember that I can't see you
database,
so
you
should describe any tables and fields that appear if if is not
obvious
by
context. For instance, I can assume that Referral is the name of
the
Referral, but other things may not be as clear.

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.

 

[MeredithS]

Hi, Bruce -- I read your comments and I don't think I understand which combo
box the After Update Event procedure is associated with. My understanding is
that the procedure will requery the selected value of the 1st combo box; so,
to me, that implies that the After Update would be associated with the 2nd
combo box, but refer to the 1st. Is that correct?

I changed the name of the 2nd box to ReferralSource; 1st name is Referral.
Therefore, the code would be (associated w/the 2nd box):
Me.cboReferral.Requery

Is that right?

You also said to put this code into the form's Current Event, but the
referral source info is just part of a much larger form so would that still
be true??

Many thanks,

Meredith

Hi, Bruce. I reorganized my table(s) so that all referral info is in one:
Resource ID, Resource Type, Referral Source Label. Then I re-inserted most
of
your original code into 2 combo boxes, including the new On Event
procedure
as follows:

I think the 1st box works well -- all the categories show up, including
"All", but the 2nd box is still asking for a value as a parameter query...
and the event procedure isn't working. See following:

SELECT DISTINCT ReferralSourcesAll.[Resource Type] FROM ReferralSourcesAll
UNION SELECT "(All)" as Bogus FROM ReferralSourcesAll
ORDER BY ReferralSourcesAll.[Resource Type];

Combo Box #1 named Referral



Private Sub Referral_AfterUpdate()
Me.cboReferral Source.Requery
End Sub

(when I try to run this I get an error message to the effect that it can't
locate a named object)

cboReferralSource is the second combo box (the one from which you select the
specific referral entity). Below you have referred to it as Referral
Source. Use your actual combo box name for the requery code.

By the way, you will be doing yourself a favor if you restrict Access names
(tables, forms, fields, controls, etc.) to alphanumeric characters and the
underscore. Surrounding the name in square brackets should eliminate the
problems that can result from spaces, but why give yourself the extra
hassle? Also, I think you will find the shortest possible descriptive name
for a form, field, etc. is the most convenient in code. Finally, giving a
control a different name than the field will eliminate some puzzling error
messages that can appear if Access gets confused.

No need to change anything in your current project. These are just ideas
for the future.

SELECT ReferralSourcesAll.[Referral Source Label],
ReferralSourcesAll.[Resource Type]
FROM ReferralSourcesAll
WHERE (((IIf([Forms]![frmIntake Worksheet2]![cboReferral
Type]<>"All","",[Referral Source Label]=[Forms]![frmIntake
Worksheet2]![cboReferral Type]))=True))
ORDER BY ReferralSourcesAll.[Referral Source Label];

Combo Box #2 named Referral Source

Thanks!!

Meredith

Good idea using the one table. A lookup table is fine for holding a list
of
values (such as referral type, or on a larger scale, cities or states)
from
which the user chooses. The chosen value is stored in the main table (or
it
may be linked, but with a single field there is probably little to be
gained
by doing that). In your case, a list of referral types can be held in a
ReferralType table, but that value ends up being stored in individual
records in the ReferralSources table, just as city or state typically is
stored with the rest of a person's contact information.


Thanks, Bruce. Let me see what I've got and I'll get back to you,
probably
tomorrow. The first combo box does work -- the list shows up and I can
select
an item from it. I think the easiest way to fix what's wrong, for
starters,
is to do what I should have done in the beginning and combine all the
fields
into one table. Not sure why I had 2 tables anyway. I'll work from that
angle...

Meredith


:

Hi, Bruce -- Here's what I have. I think, at present, the only thing
not
working right is the All feature -- I've probably scavenged/guessed
enough
to
screw up the original code which might have worked exactly as it
was.
The
difference seemed to be that I had 2 tables I was drawing from and
the
Referral one didn't have the Referral Type as a text name, which was
what
I
wanted to show up in the 1st combo box. So, I was trying to pull
from
both
and I think that's what got screwed up. If this doesn't work, as I'm
doing
it, I can simply add a field to Table #2 and get everything in one
table --
probably what I should have done in the 1st place?

2 tables: Resource ID Table (Resource ID, Resource Type) and
ReferralSourcesAll (Resource ID, Referral Source Label)

What is the Row Source for the first combo box (the ReferralType
one)?
SELECT DISTINCT [Resource Type] FROM [Resource ID Table] UNION
SELECT
"(All)" as Bogus FROM [Resource ID Table]
ORDER BY [Resource Type];

This should work, assuming the fields and table names are correct. To
test,
create a new query in design view. Don't select any tables, and click
View
SQL. Enter the code:

SELECT DISTINCT [Resource Type]
FROM [Resource ID Table]
UNION
SELECT "(All)" as Bogus
FROM [Resource ID Table]
ORDER BY [Resource Type];

The line breaks are just to make it easier to read and follow. Switch
to
datasheet view. Do you see the list? If not, create a query in
design
view. Add Resource ID Table, click OK, and add just the ResourceType
field.
Switch to datasheet view. If it is OK, switch to SQL view and note
exactly
what you see there.

This combo box is unbound, as I see it.

I can't quite make out the purpose of ReferralSourcesAll. I woud
imagine
Referral data something like this:

tblReferralSourcesAll
RefSourceID (autonumber primary key)
ResourceType (Agency, etc.)
Resource (Name of referral)
Phone, Address, etc.

RefSourceID ResourceType Resource
1 Agency CPS
2 General Dr. Welby
3 General Dr. Jekyll

I have left out Phone, etc., as they don't matter for this, although
you
may
need them.

The second combo box (I will call it cboSource; note the name, for I
will
refer to it again soon) would have as its Row Source:

SELECT tblReferralSourcesAll.Resource
FROM tblReferralSourcesAll
WHERE (((IIf([Forms]![IntakeWorksheet2]![ResourceType]<>"All","",
[Resource] = [Forms]![IntakeWorksheet2]![ResourceType]))=True))
ORDER BY tblReferralSourcesAll.Resource;

This assumes that you are storing the value Agency, etc. from
[Resource
ID
Table]. The way you could put that value into tblReferralSourcesAll
is
to
make a form based on tblReferralSourcesAll, with a combo box bound to
the
ResourceType field (that is, ResourceType is its ControlSource, which
means
that values entered into the combo box are stored in the ResourceType
field). That combo box, with a column count of 1 and a column width
of,
say, 1", would have as its Row Source the same Row Source code as
described
for your first combo box. Select the ResourceType, and it will be
inserted
into the field.

Back to your first combo box. In its After Update event you would
have
Me.cboSource.Requery. To add this code, open the form's property
sheet:
Click the combo box to select it, click View > Properties, and click
the
Event tab. Click After Update, click the three dots on the right, and
insert Me.cboSource.Requery between Private Sub and End Sub. What
this
does
is to requery the Row Source for cboSource. That is to say, it runs
the
Row
Source code, with the selected value as the parameter. If you
selected
Agency, then [Forms]![IntakeWorksheet2]![ReferralType] contains the
value
"Agency". The Row Source looks for all Resource records that have
"Agency"
as the ResourceType.

You will probably want to put Me.cboSource.Requery into the form's
Current
event, so that when you arrive at a record the Resource text box will
have
the correct list.

I hope this gets you closer to where you need to be.




What is the Row Source for the second combo box (the Referral one)?
Again,
any code?

SELECT [Resource ID Table].[Resource ID],
ReferralSourcesAll.[Referral
Source Label], [Resource ID Table].[Resource Type]
FROM [Resource ID Table] INNER JOIN ReferralSourcesAll ON [Resource
ID
Table].[Resource ID] = ReferralSourcesAll.[Resource ID]
WHERE ((([Resource ID Table].[Resource Type])=[Forms]![Intake
Worksheet2]![ReferralType]));



Thanks!!

Meredith


:

What is the Row Source for the first combo box (the ReferralType
one)?
Does
it have any After Update code or other code? I'm not sure where
you
are
in
your Access vocabulary, so at the risk of saying something you
already
know,
click the combo box to select it, click View > Properties, and
Click
the
Event tab. If any of the Events has [Event Procedure], click the
three
dots
to the right of the row to view the code. The code will go
something
like:

Private Sub cboReferralType_After Update

{Code here}

End Sub


What is the Row Source for the second combo box (the Referral one)?
Again,
any code?

In describing the Row Source, remember that I can't see you
database,
so
you
should describe any tables and fields that appear if if is not
obvious
by
context. For instance, I can assume that Referral is the name of
the
Referral, but other things may not be as clear.

news:[email protected]...

 

[BruceM]

No, the other way around with the After Update. First, we need to get some
names straight. I am not looking at your database, and am having trouble
keeping combo boxes and fields straight, so I will be referring to your
combo boxes as cboReferralType (for selecting Agency, General, Specific, and
(All) and cboReferral (for selecting the actual referral). The form on
which all of this happens is IntakeWorksheet2. IntakeWorksheet2 is bound to
a table that presumably has other information for date, etc. This table
must also have a field to store the Referral (the actual name). Actually,
you should probably be storing the ReferralID rather than the name, since
ReferralID is an unchanging number field (that is, the agency name may
change, but its number won't, just as your SS # does not change).
More names: I will leave the table name as ReferralSourcesAll. Here are
its fields:

ReferralID (autonumber primary key)
ReferralType (Agency, etc.)
Referral (CPS, etc.)

Here is the code for cboReferralType:
SELECT DISTINCT ReferralSourcesAll.[ReferralType]
FROM ReferralSourcesAll
UNION SELECT "(All)" as Bogus
FROM ReferralSourcesAll
ORDER BY ReferralSourcesAll.[ReferralType];

After choosing a ReferralType in cboReferral (the first combo box),
cboReferral is updated. When that happens the After Update event runs.
Let's say you selected "Agency". cboReferral says to the combo box
ReferralSource "OK, 'Agency' is my selected value. Plug 'Agency' into your
Row Source code, and run the code again."

Back to the cboReferralSource, with changes based on the naming things I
mentioned.
SELECT ReferralSourcesAll.Referral
FROM ReferralSourcesAll
WHERE (((IIf([Forms]![IntakeWorksheet2]![cboReferralType]<>"All","",
[Referral] = [Forms]![IntakeWorksheet2]![cboReferralType]))=True))
ORDER BY ReferralSourcesAll.Referral;

Breaking it down:

SELECT ReferralSourcesAll.Referral
FROM ReferralSourcesAll
As is probably clear, this is the instruction to select the Referral field
from ReferralSourcesAll

WHERE
What follows are restrictions on the number of records.

IIf([Forms]![IntakeWorksheet2]![cboReferralType]<>"All"
This refers to cboReferralType. That is where "All" may appear in some
cases, or else the ReferralType.
This is saying "If the selection in cboReferralType is "All"...(we'll get to
what needs to happen if this is true or if it is false).

NOTE: I changed <> to =. I got it backward the first time.

Back to the full IIf statement:
IIf([Forms]![IntakeWorksheet2]![cboReferralType] = "All","",
[Referral] = [Forms]![IntakeWorksheet2]![cboReferralType])

If cboReferralType = "All" then (first comma) do nothing, otherwise (second
comma) Referral (the table field in ReferralSourcesAll) = the selection in
cboReferral. "Agency" is the selection in cboReferral, so the WHERE part of
the Row Source amounts to:
WHERE ReferralType = Agency

The full Row Source for the combo box cboReferralSource, in vernacular:
SELECT the Referral field (all records)
FROM tblReferralSourcesAll
WHERE Referral = Agency
ORDER BY (that is, sort by) the Referral field

If the selection in cboReferralType is (All), look at the IIf statement
again:
IIf([Forms]![IntakeWorksheet2]![cboReferralType] = "All","",
[Referral] = [Forms]![IntakeWorksheet2]![cboReferralType])

cboReferralType equals (All), so the first condition ("") applies. Since ""
is an empty string, there are no restrictions on the records (no WHERE
condition):
SELECT the Referral field (all records)
FROM tblReferralSourcesAll
WHERE Referral = nothing
ORDER BY (that is, sort by) the Referral field

The extra parentheses in the IIf statement, and the = True part, are things
that the code needs. To be honest, I built most of the query with the query
design grid, then looked at the SQL afterwards.

So the After Update event (Me.cboReferral.Requery) of cboReferralType is
telling cboReferral to limit its list of records to just the records with
Agency (in my first example) in the ReferralType field.

Forget about the code in the Current event. It's probably not needed, since
for existing records the Referral has already been selected.

Hi, Bruce -- I read your comments and I don't think I understand which
combo
box the After Update Event procedure is associated with. My understanding
is
that the procedure will requery the selected value of the 1st combo box;
so,
to me, that implies that the After Update would be associated with the 2nd
combo box, but refer to the 1st. Is that correct?

I changed the name of the 2nd box to ReferralSource; 1st name is Referral.
Therefore, the code would be (associated w/the 2nd box):
Me.cboReferral.Requery

Is that right?

You also said to put this code into the form's Current Event, but the
referral source info is just part of a much larger form so would that
still
be true??

Many thanks,

Meredith

Hi, Bruce. I reorganized my table(s) so that all referral info is in
one:
Resource ID, Resource Type, Referral Source Label. Then I re-inserted
most
of
your original code into 2 combo boxes, including the new On Event
procedure
as follows:

I think the 1st box works well -- all the categories show up, including
"All", but the 2nd box is still asking for a value as a parameter
query...
and the event procedure isn't working. See following:

SELECT DISTINCT ReferralSourcesAll.[Resource Type] FROM
ReferralSourcesAll
UNION SELECT "(All)" as Bogus FROM ReferralSourcesAll
ORDER BY ReferralSourcesAll.[Resource Type];

Combo Box #1 named Referral



Private Sub Referral_AfterUpdate()
Me.cboReferral Source.Requery
End Sub

(when I try to run this I get an error message to the effect that it
can't
locate a named object)

cboReferralSource is the second combo box (the one from which you select
the
specific referral entity). Below you have referred to it as Referral
Source. Use your actual combo box name for the requery code.

By the way, you will be doing yourself a favor if you restrict Access
names
(tables, forms, fields, controls, etc.) to alphanumeric characters and
the
underscore. Surrounding the name in square brackets should eliminate the
problems that can result from spaces, but why give yourself the extra
hassle? Also, I think you will find the shortest possible descriptive
name
for a form, field, etc. is the most convenient in code. Finally, giving
a
control a different name than the field will eliminate some puzzling
error
messages that can appear if Access gets confused.

No need to change anything in your current project. These are just ideas
for the future.

SELECT ReferralSourcesAll.[Referral Source Label],
ReferralSourcesAll.[Resource Type]
FROM ReferralSourcesAll
WHERE (((IIf([Forms]![frmIntake Worksheet2]![cboReferral
Type]<>"All","",[Referral Source Label]=[Forms]![frmIntake
Worksheet2]![cboReferral Type]))=True))
ORDER BY ReferralSourcesAll.[Referral Source Label];

Combo Box #2 named Referral Source

Thanks!!

Meredith


:

Good idea using the one table. A lookup table is fine for holding a
list
of
values (such as referral type, or on a larger scale, cities or states)
from
which the user chooses. The chosen value is stored in the main table
(or
it
may be linked, but with a single field there is probably little to be
gained
by doing that). In your case, a list of referral types can be held in
a
ReferralType table, but that value ends up being stored in individual
records in the ReferralSources table, just as city or state typically
is
stored with the rest of a person's contact information.


Thanks, Bruce. Let me see what I've got and I'll get back to you,
probably
tomorrow. The first combo box does work -- the list shows up and I
can
select
an item from it. I think the easiest way to fix what's wrong, for
starters,
is to do what I should have done in the beginning and combine all
the
fields
into one table. Not sure why I had 2 tables anyway. I'll work from
that
angle...

Meredith


:

Hi, Bruce -- Here's what I have. I think, at present, the only
thing
not
working right is the All feature -- I've probably
scavenged/guessed
enough
to
screw up the original code which might have worked exactly as it
was.
The
difference seemed to be that I had 2 tables I was drawing from
and
the
Referral one didn't have the Referral Type as a text name, which
was
what
I
wanted to show up in the 1st combo box. So, I was trying to pull
from
both
and I think that's what got screwed up. If this doesn't work, as
I'm
doing
it, I can simply add a field to Table #2 and get everything in
one
table --
probably what I should have done in the 1st place?

2 tables: Resource ID Table (Resource ID, Resource Type) and
ReferralSourcesAll (Resource ID, Referral Source Label)

What is the Row Source for the first combo box (the ReferralType
one)?
SELECT DISTINCT [Resource Type] FROM [Resource ID Table] UNION
SELECT
"(All)" as Bogus FROM [Resource ID Table]
ORDER BY [Resource Type];

This should work, assuming the fields and table names are correct.
To
test,
create a new query in design view. Don't select any tables, and
click
View
SQL. Enter the code:

SELECT DISTINCT [Resource Type]
FROM [Resource ID Table]
UNION
SELECT "(All)" as Bogus
FROM [Resource ID Table]
ORDER BY [Resource Type];

The line breaks are just to make it easier to read and follow.
Switch
to
datasheet view. Do you see the list? If not, create a query in
design
view. Add Resource ID Table, click OK, and add just the
ResourceType
field.
Switch to datasheet view. If it is OK, switch to SQL view and note
exactly
what you see there.

This combo box is unbound, as I see it.

I can't quite make out the purpose of ReferralSourcesAll. I woud
imagine
Referral data something like this:

tblReferralSourcesAll
RefSourceID (autonumber primary key)
ResourceType (Agency, etc.)
Resource (Name of referral)
Phone, Address, etc.

RefSourceID ResourceType Resource
1 Agency CPS
2 General Dr. Welby
3 General Dr. Jekyll

I have left out Phone, etc., as they don't matter for this,
although
you
may
need them.

The second combo box (I will call it cboSource; note the name, for
I
will
refer to it again soon) would have as its Row Source:

SELECT tblReferralSourcesAll.Resource
FROM tblReferralSourcesAll
WHERE (((IIf([Forms]![IntakeWorksheet2]![ResourceType]<>"All","",
[Resource] = [Forms]![IntakeWorksheet2]![ResourceType]))=True))
ORDER BY tblReferralSourcesAll.Resource;

This assumes that you are storing the value Agency, etc. from
[Resource
ID
Table]. The way you could put that value into
tblReferralSourcesAll
is
to
make a form based on tblReferralSourcesAll, with a combo box bound
to
the
ResourceType field (that is, ResourceType is its ControlSource,
which
means
that values entered into the combo box are stored in the
ResourceType
field). That combo box, with a column count of 1 and a column
width
of,
say, 1", would have as its Row Source the same Row Source code as
described
for your first combo box. Select the ResourceType, and it will be
inserted
into the field.

Back to your first combo box. In its After Update event you would
have
Me.cboSource.Requery. To add this code, open the form's property
sheet:
Click the combo box to select it, click View > Properties, and
click
the
Event tab. Click After Update, click the three dots on the right,
and
insert Me.cboSource.Requery between Private Sub and End Sub. What
this
does
is to requery the Row Source for cboSource. That is to say, it
runs
the
Row
Source code, with the selected value as the parameter. If you
selected
Agency, then [Forms]![IntakeWorksheet2]![ReferralType] contains the
value
"Agency". The Row Source looks for all Resource records that have
"Agency"
as the ResourceType.

You will probably want to put Me.cboSource.Requery into the form's
Current
event, so that when you arrive at a record the Resource text box
will
have
the correct list.

I hope this gets you closer to where you need to be.




What is the Row Source for the second combo box (the Referral
one)?
Again,
any code?

SELECT [Resource ID Table].[Resource ID],
ReferralSourcesAll.[Referral
Source Label], [Resource ID Table].[Resource Type]
FROM [Resource ID Table] INNER JOIN ReferralSourcesAll ON
[Resource
ID
Table].[Resource ID] = ReferralSourcesAll.[Resource ID]
WHERE ((([Resource ID Table].[Resource Type])=[Forms]![Intake
Worksheet2]![ReferralType]));



Thanks!!

Meredith


:

What is the Row Source for the first combo box (the ReferralType
one)?
Does
it have any After Update code or other code? I'm not sure where
you
are
in
your Access vocabulary, so at the risk of saying something you
already
know,
click the combo box to select it, click View > Properties, and
Click
the
Event tab. If any of the Events has [Event Procedure], click
the
three
dots
to the right of the row to view the code. The code will go
something
like:

Private Sub cboReferralType_After Update

{Code here}

End Sub


What is the Row Source for the second combo box (the Referral
one)?
Again,
any code?

In describing the Row Source, remember that I can't see you
database,
so
you
should describe any tables and fields that appear if if is not
obvious
by
context. For instance, I can assume that Referral is the name
of
the
Referral, but other things may not be as clear.

message
news:[email protected]...

 

[MeredithS]

Bruce, I don't know how your brain keeps going with this stuff like it does;
mine hurts You've been very patient and I really appreciate it. My
nonprofit psychiatric hospital for children and adolescents appreciates it --
or would, if they realized how much you've personally contributed to their
efficiency and financial wellbeing. Seriously, thank you for all your help
with this. Surely I can forge ahead from here on my own? If not, I'll wait a
week or so and re-post, much further along. Hopefully won't happen.

Meredith

No, the other way around with the After Update. First, we need to get some
names straight. I am not looking at your database, and am having trouble
keeping combo boxes and fields straight, so I will be referring to your
combo boxes as cboReferralType (for selecting Agency, General, Specific, and
(All) and cboReferral (for selecting the actual referral). The form on
which all of this happens is IntakeWorksheet2. IntakeWorksheet2 is bound to
a table that presumably has other information for date, etc. This table
must also have a field to store the Referral (the actual name). Actually,
you should probably be storing the ReferralID rather than the name, since
ReferralID is an unchanging number field (that is, the agency name may
change, but its number won't, just as your SS # does not change).
More names: I will leave the table name as ReferralSourcesAll. Here are
its fields:

ReferralID (autonumber primary key)
ReferralType (Agency, etc.)
Referral (CPS, etc.)

Here is the code for cboReferralType:
SELECT DISTINCT ReferralSourcesAll.[ReferralType]
FROM ReferralSourcesAll
UNION SELECT "(All)" as Bogus
FROM ReferralSourcesAll
ORDER BY ReferralSourcesAll.[ReferralType];

After choosing a ReferralType in cboReferral (the first combo box),
cboReferral is updated. When that happens the After Update event runs.
Let's say you selected "Agency". cboReferral says to the combo box
ReferralSource "OK, 'Agency' is my selected value. Plug 'Agency' into your
Row Source code, and run the code again."

Back to the cboReferralSource, with changes based on the naming things I
mentioned.
SELECT ReferralSourcesAll.Referral
FROM ReferralSourcesAll
WHERE (((IIf([Forms]![IntakeWorksheet2]![cboReferralType]<>"All","",
[Referral] = [Forms]![IntakeWorksheet2]![cboReferralType]))=True))
ORDER BY ReferralSourcesAll.Referral;

Breaking it down:

SELECT ReferralSourcesAll.Referral
FROM ReferralSourcesAll
As is probably clear, this is the instruction to select the Referral field
from ReferralSourcesAll

WHERE
What follows are restrictions on the number of records.

IIf([Forms]![IntakeWorksheet2]![cboReferralType]<>"All"
This refers to cboReferralType. That is where "All" may appear in some
cases, or else the ReferralType.
This is saying "If the selection in cboReferralType is "All"...(we'll get to
what needs to happen if this is true or if it is false).

NOTE: I changed <> to =. I got it backward the first time.

Back to the full IIf statement:
IIf([Forms]![IntakeWorksheet2]![cboReferralType] = "All","",
[Referral] = [Forms]![IntakeWorksheet2]![cboReferralType])

If cboReferralType = "All" then (first comma) do nothing, otherwise (second
comma) Referral (the table field in ReferralSourcesAll) = the selection in
cboReferral. "Agency" is the selection in cboReferral, so the WHERE part of
the Row Source amounts to:
WHERE ReferralType = Agency

The full Row Source for the combo box cboReferralSource, in vernacular:
SELECT the Referral field (all records)
FROM tblReferralSourcesAll
WHERE Referral = Agency
ORDER BY (that is, sort by) the Referral field

If the selection in cboReferralType is (All), look at the IIf statement
again:
IIf([Forms]![IntakeWorksheet2]![cboReferralType] = "All","",
[Referral] = [Forms]![IntakeWorksheet2]![cboReferralType])

cboReferralType equals (All), so the first condition ("") applies. Since ""
is an empty string, there are no restrictions on the records (no WHERE
condition):
SELECT the Referral field (all records)
FROM tblReferralSourcesAll
WHERE Referral = nothing
ORDER BY (that is, sort by) the Referral field

The extra parentheses in the IIf statement, and the = True part, are things
that the code needs. To be honest, I built most of the query with the query
design grid, then looked at the SQL afterwards.

So the After Update event (Me.cboReferral.Requery) of cboReferralType is
telling cboReferral to limit its list of records to just the records with
Agency (in my first example) in the ReferralType field.

Forget about the code in the Current event. It's probably not needed, since
for existing records the Referral has already been selected.

Hi, Bruce -- I read your comments and I don't think I understand which
combo
box the After Update Event procedure is associated with. My understanding
is
that the procedure will requery the selected value of the 1st combo box;
so,
to me, that implies that the After Update would be associated with the 2nd
combo box, but refer to the 1st. Is that correct?

I changed the name of the 2nd box to ReferralSource; 1st name is Referral.
Therefore, the code would be (associated w/the 2nd box):
Me.cboReferral.Requery

Is that right?

You also said to put this code into the form's Current Event, but the
referral source info is just part of a much larger form so would that
still
be true??

Many thanks,

Meredith

Hi, Bruce. I reorganized my table(s) so that all referral info is in
one:
Resource ID, Resource Type, Referral Source Label. Then I re-inserted
most
of
your original code into 2 combo boxes, including the new On Event
procedure
as follows:

I think the 1st box works well -- all the categories show up, including
"All", but the 2nd box is still asking for a value as a parameter
query...
and the event procedure isn't working. See following:

SELECT DISTINCT ReferralSourcesAll.[Resource Type] FROM
ReferralSourcesAll
UNION SELECT "(All)" as Bogus FROM ReferralSourcesAll
ORDER BY ReferralSourcesAll.[Resource Type];

Combo Box #1 named Referral



Private Sub Referral_AfterUpdate()
Me.cboReferral Source.Requery
End Sub

(when I try to run this I get an error message to the effect that it
can't
locate a named object)

cboReferralSource is the second combo box (the one from which you select
the
specific referral entity). Below you have referred to it as Referral
Source. Use your actual combo box name for the requery code.

By the way, you will be doing yourself a favor if you restrict Access
names
(tables, forms, fields, controls, etc.) to alphanumeric characters and
the
underscore. Surrounding the name in square brackets should eliminate the
problems that can result from spaces, but why give yourself the extra
hassle? Also, I think you will find the shortest possible descriptive
name
for a form, field, etc. is the most convenient in code. Finally, giving
a
control a different name than the field will eliminate some puzzling
error
messages that can appear if Access gets confused.

No need to change anything in your current project. These are just ideas
for the future.


SELECT ReferralSourcesAll.[Referral Source Label],
ReferralSourcesAll.[Resource Type]
FROM ReferralSourcesAll
WHERE (((IIf([Forms]![frmIntake Worksheet2]![cboReferral
Type]<>"All","",[Referral Source Label]=[Forms]![frmIntake
Worksheet2]![cboReferral Type]))=True))
ORDER BY ReferralSourcesAll.[Referral Source Label];

Combo Box #2 named Referral Source

Thanks!!

Meredith


:

Good idea using the one table. A lookup table is fine for holding a
list
of
values (such as referral type, or on a larger scale, cities or states)
from
which the user chooses. The chosen value is stored in the main table
(or
it
may be linked, but with a single field there is probably little to be
gained
by doing that). In your case, a list of referral types can be held in
a
ReferralType table, but that value ends up being stored in individual
records in the ReferralSources table, just as city or state typically
is
stored with the rest of a person's contact information.


Thanks, Bruce. Let me see what I've got and I'll get back to you,
probably
tomorrow. The first combo box does work -- the list shows up and I
can
select
an item from it. I think the easiest way to fix what's wrong, for
starters,
is to do what I should have done in the beginning and combine all
the
fields
into one table. Not sure why I had 2 tables anyway. I'll work from
that
angle...

Meredith


:

Hi, Bruce -- Here's what I have. I think, at present, the only
thing
not
working right is the All feature -- I've probably
scavenged/guessed
enough
to
screw up the original code which might have worked exactly as it
was.
The
difference seemed to be that I had 2 tables I was drawing from
and
the
Referral one didn't have the Referral Type as a text name, which
was
what
I
wanted to show up in the 1st combo box. So, I was trying to pull
from
both
and I think that's what got screwed up. If this doesn't work, as
I'm
doing
it, I can simply add a field to Table #2 and get everything in
one
table --
probably what I should have done in the 1st place?

2 tables: Resource ID Table (Resource ID, Resource Type) and
ReferralSourcesAll (Resource ID, Referral Source Label)

What is the Row Source for the first combo box (the ReferralType
one)?
SELECT DISTINCT [Resource Type] FROM [Resource ID Table] UNION
SELECT
"(All)" as Bogus FROM [Resource ID Table]
ORDER BY [Resource Type];

This should work, assuming the fields and table names are correct.
To
test,
create a new query in design view. Don't select any tables, and
click
View
SQL. Enter the code:

SELECT DISTINCT [Resource Type]
FROM [Resource ID Table]
UNION
SELECT "(All)" as Bogus
FROM [Resource ID Table]
ORDER BY [Resource Type];

The line breaks are just to make it easier to read and follow.
Switch
to
datasheet view. Do you see the list? If not, create a query in
design
view. Add Resource ID Table, click OK, and add just the
ResourceType

 

[BruceM]

I learn too while I'm thinking about things like this. I can't tell you how
often I end up using something I discovered while studying somebody else's
challenges.
I think you would be doing yourself a favor if you use prefixes such as cbo,
txt, etc. for controls such as combo boxes, text boxes, and so forth. It
will help you identify things when you look at the project again in the
future, and it will help in these postings. Best of luck with your project.

Bruce, I don't know how your brain keeps going with this stuff like it
does;
mine hurts You've been very patient and I really appreciate it. My
nonprofit psychiatric hospital for children and adolescents appreciates
it --
or would, if they realized how much you've personally contributed to their
efficiency and financial wellbeing. Seriously, thank you for all your help
with this. Surely I can forge ahead from here on my own? If not, I'll wait
a
week or so and re-post, much further along. Hopefully won't happen.

Meredith

No, the other way around with the After Update. First, we need to get
some
names straight. I am not looking at your database, and am having trouble
keeping combo boxes and fields straight, so I will be referring to your
combo boxes as cboReferralType (for selecting Agency, General, Specific,
and
(All) and cboReferral (for selecting the actual referral). The form on
which all of this happens is IntakeWorksheet2. IntakeWorksheet2 is bound
to
a table that presumably has other information for date, etc. This table
must also have a field to store the Referral (the actual name).
Actually,
you should probably be storing the ReferralID rather than the name, since
ReferralID is an unchanging number field (that is, the agency name may
change, but its number won't, just as your SS # does not change).
More names: I will leave the table name as ReferralSourcesAll. Here are
its fields:

ReferralID (autonumber primary key)
ReferralType (Agency, etc.)
Referral (CPS, etc.)

Here is the code for cboReferralType:
SELECT DISTINCT ReferralSourcesAll.[ReferralType]
FROM ReferralSourcesAll
UNION SELECT "(All)" as Bogus
FROM ReferralSourcesAll
ORDER BY ReferralSourcesAll.[ReferralType];

After choosing a ReferralType in cboReferral (the first combo box),
cboReferral is updated. When that happens the After Update event runs.
Let's say you selected "Agency". cboReferral says to the combo box
ReferralSource "OK, 'Agency' is my selected value. Plug 'Agency' into
your
Row Source code, and run the code again."

Back to the cboReferralSource, with changes based on the naming things I
mentioned.
SELECT ReferralSourcesAll.Referral
FROM ReferralSourcesAll
WHERE (((IIf([Forms]![IntakeWorksheet2]![cboReferralType]<>"All","",
[Referral] = [Forms]![IntakeWorksheet2]![cboReferralType]))=True))
ORDER BY ReferralSourcesAll.Referral;

Breaking it down:

SELECT ReferralSourcesAll.Referral
FROM ReferralSourcesAll
As is probably clear, this is the instruction to select the Referral
field
from ReferralSourcesAll

WHERE
What follows are restrictions on the number of records.

IIf([Forms]![IntakeWorksheet2]![cboReferralType]<>"All"
This refers to cboReferralType. That is where "All" may appear in some
cases, or else the ReferralType.
This is saying "If the selection in cboReferralType is "All"...(we'll get
to
what needs to happen if this is true or if it is false).

NOTE: I changed <> to =. I got it backward the first time.

Back to the full IIf statement:
IIf([Forms]![IntakeWorksheet2]![cboReferralType] = "All","",
[Referral] = [Forms]![IntakeWorksheet2]![cboReferralType])

If cboReferralType = "All" then (first comma) do nothing, otherwise
(second
comma) Referral (the table field in ReferralSourcesAll) = the selection
in
cboReferral. "Agency" is the selection in cboReferral, so the WHERE part
of
the Row Source amounts to:
WHERE ReferralType = Agency

The full Row Source for the combo box cboReferralSource, in vernacular:
SELECT the Referral field (all records)
FROM tblReferralSourcesAll
WHERE Referral = Agency
ORDER BY (that is, sort by) the Referral field

If the selection in cboReferralType is (All), look at the IIf statement
again:
IIf([Forms]![IntakeWorksheet2]![cboReferralType] = "All","",
[Referral] = [Forms]![IntakeWorksheet2]![cboReferralType])

cboReferralType equals (All), so the first condition ("") applies. Since
""
is an empty string, there are no restrictions on the records (no WHERE
condition):
SELECT the Referral field (all records)
FROM tblReferralSourcesAll
WHERE Referral = nothing
ORDER BY (that is, sort by) the Referral field

The extra parentheses in the IIf statement, and the = True part, are
things
that the code needs. To be honest, I built most of the query with the
query
design grid, then looked at the SQL afterwards.

So the After Update event (Me.cboReferral.Requery) of cboReferralType is
telling cboReferral to limit its list of records to just the records with
Agency (in my first example) in the ReferralType field.

Forget about the code in the Current event. It's probably not needed,
since
for existing records the Referral has already been selected.

Hi, Bruce -- I read your comments and I don't think I understand which
combo
box the After Update Event procedure is associated with. My
understanding
is
that the procedure will requery the selected value of the 1st combo
box;
so,
to me, that implies that the After Update would be associated with the
2nd
combo box, but refer to the 1st. Is that correct?

I changed the name of the 2nd box to ReferralSource; 1st name is
Referral.
Therefore, the code would be (associated w/the 2nd box):
Me.cboReferral.Requery

Is that right?

You also said to put this code into the form's Current Event, but the
referral source info is just part of a much larger form so would that
still
be true??

Many thanks,

Meredith


:

Hi, Bruce. I reorganized my table(s) so that all referral info is in
one:
Resource ID, Resource Type, Referral Source Label. Then I
re-inserted
most
of
your original code into 2 combo boxes, including the new On Event
procedure
as follows:

I think the 1st box works well -- all the categories show up,
including
"All", but the 2nd box is still asking for a value as a parameter
query...
and the event procedure isn't working. See following:

SELECT DISTINCT ReferralSourcesAll.[Resource Type] FROM
ReferralSourcesAll
UNION SELECT "(All)" as Bogus FROM ReferralSourcesAll
ORDER BY ReferralSourcesAll.[Resource Type];

Combo Box #1 named Referral



Private Sub Referral_AfterUpdate()
Me.cboReferral Source.Requery
End Sub

(when I try to run this I get an error message to the effect that it
can't
locate a named object)

cboReferralSource is the second combo box (the one from which you
select
the
specific referral entity). Below you have referred to it as Referral
Source. Use your actual combo box name for the requery code.

By the way, you will be doing yourself a favor if you restrict Access
names
(tables, forms, fields, controls, etc.) to alphanumeric characters and
the
underscore. Surrounding the name in square brackets should eliminate
the
problems that can result from spaces, but why give yourself the extra
hassle? Also, I think you will find the shortest possible descriptive
name
for a form, field, etc. is the most convenient in code. Finally,
giving
a
control a different name than the field will eliminate some puzzling
error
messages that can appear if Access gets confused.

No need to change anything in your current project. These are just
ideas
for the future.


SELECT ReferralSourcesAll.[Referral Source Label],
ReferralSourcesAll.[Resource Type]
FROM ReferralSourcesAll
WHERE (((IIf([Forms]![frmIntake Worksheet2]![cboReferral
Type]<>"All","",[Referral Source Label]=[Forms]![frmIntake
Worksheet2]![cboReferral Type]))=True))
ORDER BY ReferralSourcesAll.[Referral Source Label];

Combo Box #2 named Referral Source

Thanks!!

Meredith


:

Good idea using the one table. A lookup table is fine for holding
a
list
of
values (such as referral type, or on a larger scale, cities or
states)
from
which the user chooses. The chosen value is stored in the main
table
(or
it
may be linked, but with a single field there is probably little to
be
gained
by doing that). In your case, a list of referral types can be held
in
a
ReferralType table, but that value ends up being stored in
individual
records in the ReferralSources table, just as city or state
typically
is
stored with the rest of a person's contact information.


Thanks, Bruce. Let me see what I've got and I'll get back to you,
probably
tomorrow. The first combo box does work -- the list shows up and
I
can
select
an item from it. I think the easiest way to fix what's wrong, for
starters,
is to do what I should have done in the beginning and combine all
the
fields
into one table. Not sure why I had 2 tables anyway. I'll work
from
that
angle...

Meredith


:

message
Hi, Bruce -- Here's what I have. I think, at present, the only
thing
not
working right is the All feature -- I've probably
scavenged/guessed
enough
to
screw up the original code which might have worked exactly as
it
was.
The
difference seemed to be that I had 2 tables I was drawing from
and
the
Referral one didn't have the Referral Type as a text name,
which
was
what
I
wanted to show up in the 1st combo box. So, I was trying to
pull
from
both
and I think that's what got screwed up. If this doesn't work,
as
I'm
doing
it, I can simply add a field to Table #2 and get everything in
one
table --
probably what I should have done in the 1st place?

2 tables: Resource ID Table (Resource ID, Resource Type) and
ReferralSourcesAll (Resource ID, Referral Source Label)

What is the Row Source for the first combo box (the
ReferralType
one)?
SELECT DISTINCT [Resource Type] FROM [Resource ID Table] UNION
SELECT
"(All)" as Bogus FROM [Resource ID Table]
ORDER BY [Resource Type];

This should work, assuming the fields and table names are
correct.
To
test,
create a new query in design view. Don't select any tables, and
click
View
SQL. Enter the code:

SELECT DISTINCT [Resource Type]
FROM [Resource ID Table]
UNION
SELECT "(All)" as Bogus
FROM [Resource ID Table]
ORDER BY [Resource Type];

The line breaks are just to make it easier to read and follow.
Switch
to
datasheet view. Do you see the list? If not, create a query in
design
view. Add Resource ID Table, click OK, and add just the
ResourceType