Show N/A like default in combo box

I guess I was (?am) confused by the use of "comboboxes from forms on
reports".

If you had to explain what (data/information) you wanted to see on the
(printed) report, without using any descriptions of other Access objects
(such as forms, comboboxes, junction tables, etc.), how would you describe
the report?

Imagine you were describing a report (i.e., a printed page) to an 80
year-old grandmother. She's not likely to know Access, so you'll need to
tell her about, say (on one piece of paper ... imagine handwriting this!), a
list of meetings coming up next month, each with a list of the equipment
needed, the room set-up, and the food/beverages arrangements.

This is only an example. Please describe your "grandmother report" in your
words...g

Sorry for the round-about way of looking at this...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Ron Weaver" wrote in message
...
Jeff
My report is based on the Northwind database. Besides a 'Customers' table
I
have three tables set up just like the 'Products' table. These three many
to
many tables go thru junction tables, like 'Product Details'. These three
tables each have their own subform on the 'Orders' form. Setting these
three
forms to Continuous Forms, I am able to select multiple records using a
combo
box on each form. This database is used to book meetings. Each meeting can
require multiple Equipment Items, a variety of Food and Beverages, and a
variety of Room Set-Ups. This why I need three many to many tables, three
subforms for my 'Orders' form and report.
My report is based on a query. My combo boxes are on forms-Not in the
tables.

Jeff, I already have the report, which has been working fine for a year. This
report has information on it as follows: Customer Name, Todays Date, Order
Date, End Date, Order Time, Start Time, End Time, the selected Equipment
items (Microphone,Amplfier and Cable), selected Food and Beverage Items
(Coffee,Orange Juice and Danish), selected Room Set-Ups (Classroom, U-Shaped
and Podium), Notes. All I wanted was a default "N/A" to display under
Equipment, Food and Beverage , or Room Set-Ups if no items are needed in any
catagory.

"Jeff Boyce" wrote:

I guess I was (?am) confused by the use of "comboboxes from forms on
reports".

If you had to explain what (data/information) you wanted to see on the
(printed) report, without using any descriptions of other Access objects
(such as forms, comboboxes, junction tables, etc.), how would you describe
the report?

Imagine you were describing a report (i.e., a printed page) to an 80
year-old grandmother. She's not likely to know Access, so you'll need to
tell her about, say (on one piece of paper ... imagine handwriting this!), a
list of meetings coming up next month, each with a list of the equipment
needed, the room set-up, and the food/beverages arrangements.

This is only an example. Please describe your "grandmother report" in your
words...g

Sorry for the round-about way of looking at this...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Ron Weaver" wrote in message
...
Jeff
My report is based on the Northwind database. Besides a 'Customers' table
I
have three tables set up just like the 'Products' table. These three many
to
many tables go thru junction tables, like 'Product Details'. These three
tables each have their own subform on the 'Orders' form. Setting these
three
forms to Continuous Forms, I am able to select multiple records using a
combo
box on each form. This database is used to book meetings. Each meeting can
require multiple Equipment Items, a variety of Food and Beverages, and a
variety of Room Set-Ups. This why I need three many to many tables, three
subforms for my 'Orders' form and report.
My report is based on a query. My combo boxes are on forms-Not in the
tables.

Ron

Since I'm not there, I'm not privy to the existing report layout or the
underlying data or all of the stuff you already know so well. My 80-year
old grandmother approach was to try to separate the "how" you are doing this
from the "what".

So I'm envisioning a report that has three subreports on it. The main
report has customer info and Notes, the first subreport has Equipment, the
second has Food/Beverage, and the third has Room Set-up.

Or, if your query pulls any/all related records from the three underlying
tables, you could be using a GroupBy in the report to get three apparent
sections.

(Note: I'm not there, so I don't know which of these approaches you've used
in your report.)

If you are using the query approach, have you looked into using the Nz()
function to leave a "N/A" value in a field if there are no records?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Ron Weaver" wrote in message
...
Jeff, I already have the report, which has been working fine for a year.
This
report has information on it as follows: Customer Name, Todays Date, Order
Date, End Date, Order Time, Start Time, End Time, the selected Equipment
items (Microphone,Amplfier and Cable), selected Food and Beverage Items
(Coffee,Orange Juice and Danish), selected Room Set-Ups (Classroom,
U-Shaped
and Podium), Notes. All I wanted was a default "N/A" to display under
Equipment, Food and Beverage , or Room Set-Ups if no items are needed in
any
catagory.

"Jeff Boyce" wrote:

I guess I was (?am) confused by the use of "comboboxes from forms on
reports".

If you had to explain what (data/information) you wanted to see on the
(printed) report, without using any descriptions of other Access objects
(such as forms, comboboxes, junction tables, etc.), how would you
describe
the report?

Imagine you were describing a report (i.e., a printed page) to an 80
year-old grandmother. She's not likely to know Access, so you'll need to
tell her about, say (on one piece of paper ... imagine handwriting
this!), a
list of meetings coming up next month, each with a list of the equipment
needed, the room set-up, and the food/beverages arrangements.

This is only an example. Please describe your "grandmother report" in
your
words...g

Sorry for the round-about way of looking at this...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Ron Weaver" wrote in message
...
Jeff
My report is based on the Northwind database. Besides a 'Customers'
table
I
have three tables set up just like the 'Products' table. These three
many
to
many tables go thru junction tables, like 'Product Details'. These
three
tables each have their own subform on the 'Orders' form. Setting these
three
forms to Continuous Forms, I am able to select multiple records using a
combo
box on each form. This database is used to book meetings. Each meeting
can
require multiple Equipment Items, a variety of Food and Beverages, and
a
variety of Room Set-Ups. This why I need three many to many tables,
three
subforms for my 'Orders' form and report.
My report is based on a query. My combo boxes are on forms-Not in the
tables.

Thanks
Ron

"Jeff Boyce" wrote:

Ron

Since I'm not there, I'm not privy to the existing report layout or the
underlying data or all of the stuff you already know so well. My 80-year
old grandmother approach was to try to separate the "how" you are doing this
from the "what".

So I'm envisioning a report that has three subreports on it. The main
report has customer info and Notes, the first subreport has Equipment, the
second has Food/Beverage, and the third has Room Set-up.

Or, if your query pulls any/all related records from the three underlying
tables, you could be using a GroupBy in the report to get three apparent
sections.

(Note: I'm not there, so I don't know which of these approaches you've used
in your report.)

If you are using the query approach, have you looked into using the Nz()
function to leave a "N/A" value in a field if there are no records?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Ron Weaver" wrote in message
...
Jeff, I already have the report, which has been working fine for a year.
This
report has information on it as follows: Customer Name, Todays Date, Order
Date, End Date, Order Time, Start Time, End Time, the selected Equipment
items (Microphone,Amplfier and Cable), selected Food and Beverage Items
(Coffee,Orange Juice and Danish), selected Room Set-Ups (Classroom,
U-Shaped
and Podium), Notes. All I wanted was a default "N/A" to display under
Equipment, Food and Beverage , or Room Set-Ups if no items are needed in
any
catagory.

"Jeff Boyce" wrote:

I guess I was (?am) confused by the use of "comboboxes from forms on
reports".

If you had to explain what (data/information) you wanted to see on the
(printed) report, without using any descriptions of other Access objects
(such as forms, comboboxes, junction tables, etc.), how would you
describe
the report?

Imagine you were describing a report (i.e., a printed page) to an 80
year-old grandmother. She's not likely to know Access, so you'll need to
tell her about, say (on one piece of paper ... imagine handwriting
this!), a
list of meetings coming up next month, each with a list of the equipment
needed, the room set-up, and the food/beverages arrangements.

This is only an example. Please describe your "grandmother report" in
your
words...g

Sorry for the round-about way of looking at this...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Ron Weaver" wrote in message
...
Jeff
My report is based on the Northwind database. Besides a 'Customers'
table
I
have three tables set up just like the 'Products' table. These three
many
to
many tables go thru junction tables, like 'Product Details'. These
three
tables each have their own subform on the 'Orders' form. Setting these
three
forms to Continuous Forms, I am able to select multiple records using a
combo
box on each form. This database is used to book meetings. Each meeting
can
require multiple Equipment Items, a variety of Food and Beverages, and
a
variety of Room Set-Ups. This why I need three many to many tables,
three
subforms for my 'Orders' form and report.
My report is based on a query. My combo boxes are on forms-Not in the
tables.

Ron:

I haven't read through your exchanges with Jeff in detail , but perhaps I
could make one or two general points as I do use N/A as a value from time to
time.

The first point I'd make is that N/A is a value whereas Null is not, i.e.
it’s a statement of the fact that some particular attribute is not applicable
in the row in question, whereas the meaning of a Null is ambiguous.

A more practical point, however, is that in some situations a Null is ruled
out. Perhaps I can illustrate this with a real life example. A contact of
mine who works for an intergovernmental organisation based in France sought
my advice on a database of members of the organisation in countries
throughout the world. He had tables Countries, Regions (Départments in
France, States in the USA, Counties in the UK etc) and Cities and wanted to
select first a country, then a region then a town or city in correlated combo
boxes. This is easy enough to set up in a Members form with a bound City
combo box and unbound Country and Region combo boxes. However, some small
countries don't have any regional structure so if the RegionID column in
Cities were left Null then the city would not be mapped to any country via
the relationships. The answer is to have in the Regions table a value N/A in
however many rows are necessary to reference those countries with no regional
structure. In the Cities table each row for a city in one of those countries
references the relevant row in Regions with an N/A value. When the user
selects a country with no regional structure the regions combo box lists only
the one row, N/A and the cities combo box lists the cities which reference
that row in the Regions table.

With a county which has no regional structure N/A will be the only value
available for the region, but in the UK it would be an option in addition to
a County as the major urban conurbations here are not in any administrative
county (They are 'unitary authorities') so there would need to be a row with
N/A referencing the row in Countries for the UK as well as rows for each
county. In a case like that I'd make N/A the first row in the combo box's
list, with the counties listed alphabetically below it.

All this really amounts to is that when you want to be able to select N/A
then there should be a row for it in the table from which the combo box draws
its list. Because N/A is a data value it must be stored at a column position
in a row in a table because the relational model requires this (Chris Date's
'Information Principal' which he states as: 'The entire information content
of the database is represented in one and only one way, namely as explicit
values in column positions in rows in tables').

Ken Sheridan
Stafford, England

"Ron Weaver" wrote:

Is it possible when opening an order entry form, to to show 'N/A" as a
default in the combo boxes? I didn't see that option in the properties box.
Thanks
Ron

Hi Ken,
Thanks for your response. My database isn't that complex. If a combo box is
missed on order entry, that catagory is simply left blank on the report. I
was thinking of automating the N/A, but may just go agead and add it to the
list in the table and let the user select it. If he doesn't select anything,
it won't hurt anything.
Thanks again!
Ron

"Ken Sheridan" wrote:

Ron:

I haven't read through your exchanges with Jeff in detail , but perhaps I
could make one or two general points as I do use N/A as a value from time to
time.

The first point I'd make is that N/A is a value whereas Null is not, i.e.
it’s a statement of the fact that some particular attribute is not applicable
in the row in question, whereas the meaning of a Null is ambiguous.

A more practical point, however, is that in some situations a Null is ruled
out. Perhaps I can illustrate this with a real life example. A contact of
mine who works for an intergovernmental organisation based in France sought
my advice on a database of members of the organisation in countries
throughout the world. He had tables Countries, Regions (Départments in
France, States in the USA, Counties in the UK etc) and Cities and wanted to
select first a country, then a region then a town or city in correlated combo
boxes. This is easy enough to set up in a Members form with a bound City
combo box and unbound Country and Region combo boxes. However, some small
countries don't have any regional structure so if the RegionID column in
Cities were left Null then the city would not be mapped to any country via
the relationships. The answer is to have in the Regions table a value N/A in
however many rows are necessary to reference those countries with no regional
structure. In the Cities table each row for a city in one of those countries
references the relevant row in Regions with an N/A value. When the user
selects a country with no regional structure the regions combo box lists only
the one row, N/A and the cities combo box lists the cities which reference
that row in the Regions table.

With a county which has no regional structure N/A will be the only value
available for the region, but in the UK it would be an option in addition to
a County as the major urban conurbations here are not in any administrative
county (They are 'unitary authorities') so there would need to be a row with
N/A referencing the row in Countries for the UK as well as rows for each
county. In a case like that I'd make N/A the first row in the combo box's
list, with the counties listed alphabetically below it.

All this really amounts to is that when you want to be able to select N/A
then there should be a row for it in the table from which the combo box draws
its list. Because N/A is a data value it must be stored at a column position
in a row in a table because the relational model requires this (Chris Date's
'Information Principal' which he states as: 'The entire information content
of the database is represented in one and only one way, namely as explicit
values in column positions in rows in tables').

Ken Sheridan
Stafford, England

"Ron Weaver" wrote:

Is it possible when opening an order entry form, to to show 'N/A" as a
default in the combo boxes? I didn't see that option in the properties box.
Thanks
Ron