473,321 Members | 1,877 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

Technique Questions

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>!<CONTROLNAME>

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.ContactID, FirstName &" " & LastName FROM tblContact
INNER JOIN tblContactType ON tblContact.ContactID =
tblContactType.ContactID WHERE (((tblContactType.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?
Sep 10 '08 #1
2 1633
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********@yahoo.comwrote in message
news:Eg************@newsfe01.iad...
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>!<CONTROLNAME>

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.ContactID, FirstName &" " & LastName FROM tblContact
INNER JOIN tblContactType ON tblContact.ContactID =
tblContactType.ContactID WHERE (((tblContactType.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?
Sep 10 '08 #2
Bob Alston <bo********@yahoo.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>!<CONTROLNAME>

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/
Sep 11 '08 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Raptor | last post by:
I know it's bordeline off topic, but this is a subject which concerns many and is not frequently discussed. How about: bid the project at your usual rate, but bid a much lower rate beyond the...
3
by: Jack Klein | last post by:
I'm looking for opinions on a C technique I, and others, have used successfully in the past. While some people swear by, apparently others swear at it. Assume a part of a program too large to...
0
by: deathyam | last post by:
Hi, I am writing an application in Excel 97 in which the users click a button and data is saved/read to and from an Access 97 database on the LAN. I am concerned about performance because there...
2
by: Niklas Norrthon | last post by:
I want to share a technique I recently have found to be useful to get around some obstacles that data protection can raise. Consider the following class: // foo.h #ifndef H_FOO #define H_FOO...
18
by: xahlee | last post by:
Last year, i've posted a tutorial and commentary about Python and Perl's sort function. (http://xahlee.org/perl-python/sort_list.html) In that article, i discussed a technique known among...
8
by: VB6Newbie | last post by:
Hi, I have just created an educational children's VB6 quiz program which has 10 questions and plays a sound file for each question. It has a counter to show the question number and the current...
3
by: pragy | last post by:
Hey, can any one help me for writing a program of naive gauss elimintaion technique? It's a technique to solve system of simultaneous linear equations using matrix. thanks
15
by: Man-wai Chang | last post by:
<form name="frmTest" type="submit" method="post"> <input type="textbox" name="txtA" size=10 value=""> <select name="cboB" value=""> <option value="1">1 <option value="2">1 </select> <input...
1
by: Ben Bacarisse | last post by:
cri@tiac.net (Richard Harter) writes: <snip> I too was going to mention the technique until I saw Eric's reply because in your sketch you said: | we have definitions like | | struct...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.