Show N/A like default in combo box

R

Ron Weaver

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
 
J

Jeff Boyce

Ron

Why do you want to show something (additional) in a combo box?

Would it be sufficient to just disable (i.e., "gray-out") the combo box so
it cannot be used? This is a fairly commonly-used user interface practice,
leaving a grayed-out control to show that it is available, but N/A in the
current situation...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
R

Ron Weaver

I want it to show up on a report just in case nothing is entered or it's
missed in the order entry process.
 
R

Ron Weaver

I got it! For some reason Default Value wasn't showing. I closed the program
and re-opened it and there it was? [ComboBoxName].[ItemData](RowNumber)
Thanks
Ron
 
J

Jeff Boyce

Ron

A combobox on a report?! Combobox implies a choice, reports are printed ...
your user/reader may be confused!

You could use a text control to display the value selected (or N/A).

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
R

Ron Weaver

Jeff
The combo box is in a subform on the 'Orders' form, and has multible
selections with the subform set to Continuous Forms. With this subform on the
report, the multi selected items are displayed. Is there a way to use code,
so the combo box will show "N/A" when there are no selections made? This
would automatically show that nothing was needed from that catagory. There
are three catagories (combo boxes) to choose from. I hope this makes better
sense to you.
Ron
 
J

Jeff Boyce

Ron

From your description, you've had Access create a report directly from your
form (e.g., "this subform on the report").

I'd recommend that you treat forms (and sub-forms) as on-screen displays,
and reports as to-be-printed displays. Were I faced with your situation,
I'd create a report "from scratch". This would give me considerably more
control over the appearance of data (or N/As) on the report.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
R

Ron Weaver

Sorry Jeff'
I guess I'm just having trouble explaining it to you. The report was created
from scratch and was based on tables in the database. All of the fields,
customer information, dates and times, rooms, etc. were created from related
tables. The combo boxes are on subforms in the 'Orders' form. The combo box
forms, one for each catagory, which include two fields each are dragged into
the report. One of these, for example, will show three different pieces of
equipment with quanity and number of days needed for each piece. When the
report is previewed or printed, it will show the three pieces of equipment,
quanity, and number of days needed for each catagory. Three different
catagories, three different forms, dragged to the report and previewed,
printed, and emailed to the customer as an Order Confirmation. I hope this
gives you a better idea of what I'm doing.
Thanks
Ron
 
J

Jeff Boyce

Ron

Are you saying that your underlying tables have "combobox" fields? Created
using the lookup wizard?

If so, this may be adding to the confusion.

Could you explain a bit more what you mean by "The combo box forms, one for
each category, ... are dragged onto the report."?

A more common (and flexible, and powerful) way to create a report is to
first create a query, then base the report on the query, not directly on the
underlying table.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
R

Ron Weaver

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

Jeff Boyce

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
 
R

Ron Weaver

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

Jeff Boyce

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
 
R

Ron Weaver

Thanks
Ron

Jeff Boyce said:
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
Click to expand...
 
K

Ken Sheridan

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
 
R

Ron Weaver

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
 
Top