How can I get list box data to appear on a report?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Katherine - 08 Mar 2007 22:03 GMT

I have a query that is drawing information from two tables,
MailingList and Donations. Within the Donations table, there are two
fields that are list boxes that get their information from a table
named DonationTypes. Donations.Field1 lets the user pick from 6
options from DonationTypes column 2, and the options available in
Donations.Field2 are dependent on what the user has selected in
Donations.Field1.

My users now want to create a report from this query that will display
a number of things from MailingList and Donations, but also display
the data chosen in Donations.Field1 and Donations.Field2.

The information from Field1 and Field2 show up in tables, queries,
forms, subforms, everywhere but in a report. In the reports I have
attempted, they are always blank even if elsewhere they contain data.

Is there a way to get this data to display in a report?
Am I going about this the wrong way?

I'm open to suggestions.

Thank you for your help!

Reply to this Message

Duane Hookom - 09 Mar 2007 03:03 GMT

If you have two donation fields, you may need to add the DonationTypes table
to the report's record source twice.

IMHO lookup fields in tables are a mis-feature and shouldn't be used. Also,
if you have 2 similar donation fields in the same table, this might be a
normalization issue.

Signature

Duane Hookom
Microsoft Access MVP

> I have a query that is drawing information from two tables,
> MailingList and Donations. Within the Donations table, there are two
[quoted text clipped - 18 lines]
>
> Thank you for your help!

Reply to this Message

Katherine - 09 Mar 2007 16:21 GMT

On Mar 8, 9:03 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:

> If you have two donation fields, you may need to add the DonationTypes table
> to the report's record source twice.
[quoted text clipped - 29 lines]
>
> > Thank you for your help!

I tried adding the DonationTypes table to the record source once and
then twice, and it fails to work either way.

Since you think lookup fields in tables are a bad idea, could you
recommend another way of setting up these tables without lookup
fields? I'd still like them to have the features I described above,
but I'm open to ways of doing it better.

Thanks!

Reply to this Message

Duane Hookom - 10 Mar 2007 17:07 GMT

"it fails to work" ...? Is there anything else you might be able to provide?

You should use combo boxes and list boxes on forms for easy and consistant
data entry. You should not define these in your table and you should
generally not do data entry directly into tables.

Signature

Duane Hookom
Microsoft Access MVP

> On Mar 8, 9:03 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
> wrote:
[quoted text clipped - 41 lines]
>
> Thanks!

Reply to this Message

Chuck - 11 Mar 2007 01:23 GMT

>"it fails to work" ...? Is there anything else you might be able to provide?
>
>You should use combo boxes and list boxes on forms for easy and consistant
>data entry. You should not define these in your table and you should
>generally not do data entry directly into tables.

I started to write down the steps you have to go through.
It was beginning to look like the Great American Novel.
So here is the essence, but not the details.
Put all the data for one of your lookup fields in a single field table.
Make a query of that table to sort the data just so as to make it easy to find
values in the combo box you are going to use.
In the table where you want the data to appear, make the control be the combo
box.
In the table, set properties, both General and Lookup, for the combo box as
required.

Duane is absolutely correct. Never enter data directly into a table. If you
do , then you are just going out of your way looking for trouble. Use forms
for data entry and data editing. The Combo Box from the table should be on
your forms.

This is all possible without writing a lick of code. I know because I don't
write code.

Just a wizard prodder,
Chuck
--

Reply to this Message

Duane Hookom - 11 Mar 2007 04:18 GMT

It is the opinion of most of the more mature and experienced Access
developers that creating lookup fields in tables is a bad idea
http://www.mvps.org/access/lookupfields.htm.

Signature

Duane Hookom
Microsoft Access MVP

> >"it fails to work" ...? Is there anything else you might be able to provide?
> >
[quoted text clipped - 23 lines]
> Just a wizard prodder,
> Chuck

Reply to this Message

Chuck - 11 Mar 2007 16:00 GMT

>It is the opinion of most of the more mature and experienced Access
>developers that creating lookup fields in tables is a bad idea
>http://www.mvps.org/access/lookupfields.htm.

Why? What problems does it cause? Should I re-do the databases that have this
situation?

Tables are easy to make, easy to edit, and pulling data out of a table with a
query takes care of sorting. Of course it is one more table, one more query
and neither truly *linked* to anything else. Is it just a volume (bytes)
problem? Or is it a 'dangling *Particle*' ?

From your ref: "Any query that uses that lookup field to sort by that company
name won't work."

I am a member of a local computer club. We maintain a club directory. We have
fewer than 400 members, so the database is quite small. The vast majority of
our members live in housing areas with "Sub Division" names. We routinely pull
out member names based only on the subdivision where they live.

You are correct, the *pasted* lookup data can be overwritten. However, most
anything in a database can be messed up if folks who don't understand and don't
care have access to enter/edit data.

The club database has a table of: Zip Code (5+4), City, State. Zip Code is a
primary Key. A second table has names and Zip Code. The two tables are linked
1 to many. The Zip code in the main table is a lookup in a combo box. in a form
for entering data into the main table. Since the tables are linked, does this
make it a different situation?

Back in the early 1950s I was working on a job and my boss walked over to see
what I wad doing. He said "That's pretty good". I answered "Good H***! This
thing is perfect. It's fool proof". He answered, "Yes, that's what is wrong
with it. If you don't make it D*** Fool Proof, pretty soon you'll find one!"
He was absolutely 110 percent correct.

Just a wizard prodder
Chuck
--

Reply to this Message

Duane Hookom - 11 Mar 2007 18:49 GMT

The problem caused by lookup fields is that people don't understand them.
They make assumptions based on what they see on the screen which aren't
necessarily true to what is being stored in the underlying tables. Quite
often a "Green Eggs with Ham" is displayed while the number 17 is stored in
the table. When the novice user attempts to sort by this field, "Green Eggs
and Ham" comes before "Cat in the Hat" since Cat in the Hat is actually
number 23.

These news groups are regularly visited by people who have used the Lookup
Field mis-feature.

I never use lookup fields and I can't remember the last time I used a "Value
List" for a combo box or list box.

Signature

Duane Hookom
Microsoft Access MVP

> >It is the opinion of most of the more mature and experienced Access
> >developers that creating lookup fields in tables is a bad idea
[quoted text clipped - 33 lines]
> Just a wizard prodder
> Chuck

Reply to this Message

Chuck - 11 Mar 2007 23:03 GMT

>The problem caused by lookup fields is that people don't understand them.
>They make assumptions based on what they see on the screen which aren't
[quoted text clipped - 9 lines]
>I never use lookup fields and I can't remember the last time I used a "Value
>List" for a combo box or list box.

Very interesting. Is this feature because a relational database absolutely
must have an index for every entry in every field? Or is it just Access that
thinks everything has to have an *index* whether you assign one or not? The
only *indexes* I have are when I assign a field as a primary key. I don't ever
use auto numbers for any reason.

Chuck
--

Reply to this Message

Duane Hookom - 12 Mar 2007 20:18 GMT

I might be lazy but nearly every table I create has a primary key of
autonumber type. I will always create other indexes and some of these might
be unique. My foriegn key fields then are almost always long integers. This
is a higly debateable subject but I have found that my solution has worked
flawlessly for me in every application I create.

Signature

Duane Hookom
Microsoft Access MVP

> >The problem caused by lookup fields is that people don't understand them.
> >They make assumptions based on what they see on the screen which aren't
[quoted text clipped - 17 lines]
>
> Chuck

Reply to this Message

Chuck - 12 Mar 2007 23:03 GMT

>I might be lazy but nearly every table I create has a primary key of
>autonumber type. I will always create other indexes and some of these might
>be unique. My foriegn key fields then are almost always long integers. This
>is a higly debateable subject but I have found that my solution has worked
>flawlessly for me in every application I create.

Writing any program in any language is as much art as science. You can give
the same problem to 5 good programmers. They will each produce programs that
run flawlessly and produce identical rersults. And no two programs will be
alike.

Chuck
--

Reply to this Message