Technique Questions

[Bob Alston]

Recently I have been helping a nonprofit modify a system built in
Access, that they acquired from another nonprofit. I am doing this as a
volunteer.

I would like your perspective on two techniques I found in use as
compared to the technique I typically use.

Report Parameters
----------------
One thing I noticed is that the developer used a reports parameter form,
like I typically do, however, he saved the data to a single record table
and then included the appropriate fields necessary for selection for
each individual report as part of each report record. that is, he
included the user parm record in the query definition without any
relation to any other tables. He included the specific fields needed in
the query results.

This way he can specify criteria for the query using criteria data from
the record.

What I typically do is specify the criteria as referring to the field
name on the parameter form- FORMS!<FORMNAME >!<CONTROLNAM E>

Any opinions on which is a better technique?

Any one have an even better technique?

Description of numerically coded data in a report.
-------------------------------------------------

this author used various numeric codes to define types of people, status
of people, etc. A common technique. Typically there is a reference
table with the code and the description. the value is selected from a
drop down list box which shows the description but only the code is
stored in the record.

When printing report records, this author used a lookup process in the
definition of the description field. Example, using a field
FILLEDBYID the row source is

SELECT tblContact.Cont actID, FirstName &" " & LastName FROM tblContact
INNER JOIN tblContactType ON tblContact.Cont actID =
tblContactType. ContactID WHERE (((tblContactTy pe.ContactType) in(3,6)));

I tend to do a query relating the primary reporting table with the
contact master record, where the key of the contact record can be
related to the value in the primary table and then add the names to the
query for use in the report.

Any opinions on which is a better technique?

Any one have an even better technique?

[Allen Browne]

Regarding 'Report parameters'
=============== ======
My preferred approach is to use an unbound form where the user can enter the
criteria they want, and I build a WhereCondition string to filter the
report. The query itself does not refer to the text boxes on the form.

Why? Much more flexible and efficient. If you provide lots of boxes, and
several are left null, you just ignore them when you build the filter
string.

There are cases where this doesn't work well (such as for filtering a
subreport), so the parameters in the query itself is better.

Saving the parameters into a table makes no sense to me, unless you need to
be able to run the same report again later.

Regarding 'numeric key fields'
=============== ======
Could you use a combo box, where the bound column is zero-width? That way it
has the numeric value, but displays the text the user needs to see.

For simple lookup fields (such as types or categories), the category name
*is* unique, so is a perfect natural primary key. I don't see the point of
using an artifical key (such as AutoNumber) when a natural key exits.

If neither of those can work for you, you could include the lookup table in
the query that feeds the report, so you can filter on the text.

DLookup() is fine if you have to go that way.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Bob Alston" <bo********@yah oo.comwrote in message
news:Eg******** ****@newsfe01.i ad...

Recently I have been helping a nonprofit modify a system built in Access,
that they acquired from another nonprofit. I am doing this as a
volunteer.

I would like your perspective on two techniques I found in use as compared
to the technique I typically use.

Report Parameters
----------------
One thing I noticed is that the developer used a reports parameter form,
like I typically do, however, he saved the data to a single record table
and then included the appropriate fields necessary for selection for each
individual report as part of each report record. that is, he included the
user parm record in the query definition without any relation to any other
tables. He included the specific fields needed in the query results.

This way he can specify criteria for the query using criteria data from
the record.

What I typically do is specify the criteria as referring to the field name
on the parameter form- FORMS!<FORMNAME >!<CONTROLNAM E>

Any opinions on which is a better technique?

Any one have an even better technique?

Description of numerically coded data in a report.
-------------------------------------------------

this author used various numeric codes to define types of people, status
of people, etc. A common technique. Typically there is a reference table
with the code and the description. the value is selected from a drop down
list box which shows the description but only the code is stored in the
record.

When printing report records, this author used a lookup process in the
definition of the description field. Example, using a field FILLEDBYID
the row source is

SELECT tblContact.Cont actID, FirstName &" " & LastName FROM tblContact
INNER JOIN tblContactType ON tblContact.Cont actID =
tblContactType. ContactID WHERE (((tblContactTy pe.ContactType) in(3,6)));

I tend to do a query relating the primary reporting table with the contact
master record, where the key of the contact record can be related to the
value in the primary table and then add the names to the query for use in
the report.

Any opinions on which is a better technique?

Any one have an even better technique?

[Tony Toews [MVP]]

Bob Alston <bo********@yah oo.comwrote:

>Report Parameters
----------------
One thing I noticed is that the developer used a reports parameter form,
like I typically do, however, he saved the data to a single record table
and then included the appropriate fields necessary for selection for
each individual report as part of each report record. that is, he
included the user parm record in the query definition without any
relation to any other tables. He included the specific fields needed in
the query results.

This way he can specify criteria for the query using criteria data from
the record.

What I typically do is specify the criteria as referring to the field
name on the parameter form- FORMS!<FORMNAME >!<CONTROLNAM E>

Any opinions on which is a better technique?

Like Allen I use an unbound reports parameter form. See
http://www.granite.ab.ca/accsrprt.htm for what I do including screen shot and some
sample VBA code.

However I use the filter property of the report in the reports Open event as that is
required when using Lebans PDF file utility and such.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/