Re: tables not relating properly

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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


"BruceM" wrote:

"MeredithS" <MeredithS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:12EFAEC8-9C79-4591-BFCB-E0D560CB02AC@xxxxxxxxxxxxxxxx
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


"BruceM" wrote:

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.

"MeredithS" <MeredithS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6D54F31B-9BA6-4BDF-806E-4F84B8FC152B@xxxxxxxxxxxxxxxx
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


"BruceM" wrote:

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" <MeredithS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:59809DF5-8AF5-401E-904D-77F03FC269FE@xxxxxxxxxxxxxxxx
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


"BruceM" wrote:

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.
.

Relevant Pages

  • Re: tables not relating properly
    ... Resource ID Table ... What is the Row Source for the first combo box (the ReferralType one)? ... There are techniques for managing truly unwieldy lists consisting ...
    (microsoft.public.access.tablesdbdesign)
  • Re: tables not relating properly
    ... Resource ID, Resource Type, Referral Source Label. ... What is the Row Source for the first combo box (the ReferralType one)? ... FROM tblReferralSourcesAll ...
    (microsoft.public.access.tablesdbdesign)
  • Re: tables not relating properly
    ... Resource ID Table ... What is the Row Source for the first combo box (the ReferralType one)? ... There are techniques for managing truly unwieldy lists ...
    (microsoft.public.access.tablesdbdesign)
  • Re: tables not relating properly
    ... Resource ID, Resource Type, Referral Source Label. ... What is the Row Source for the first combo box (the ReferralType ... FROM tblReferralSourcesAll ...
    (microsoft.public.access.tablesdbdesign)
  • Re: tables not relating properly
    ... What is the Row Source for the first combo box (the ReferralType one)? ... a query based on tblReferral, ... There are techniques for managing truly unwieldy lists consisting of ...
    (microsoft.public.access.tablesdbdesign)