Many Combo's of Data, one report

  • Thread starter Thread starter Ivan
  • Start date Start date
I

Ivan

I inherited a database that I would like to clean up and
make it easier to maintain.

Right now it as a bunch of mailing label reports. Each
one is the same except for the restriction of the input
records. The user will select a "tag" that they want a
mailing label print from. A "tag" is a group of folks
with like interests or ???. The users would like to also
select multiple "tags" and have the system sort them and
delete the duplicates.

My best guess is:
1. Create a temp table that would get populated based on
which tags they select.
2. Create a form (that would be modified every time a
new "tag" is created unless someone out there in has a
suggestion on how to do it better)
3. Have a query that is based on the temp table with
SELECT DISTINCT.
4. Create the Mailing Label report based on query in #3
above.

That way at least I would only have to maintain a few
forms based on the data they want rather than 4 forms for
each "tag" type (of which there are about 82 right now).

Any and all ideas welcome.

tia


Ivan
 
Actually, what you do is make ONE NICE label report.

You then prompt, or ask or whatever to the user as to what group you want to
print.

A nice form with a combo box is usually the best bet here.

You then place a button on that nice form that asks for the group, and that
button will launch your report.

The code simple passes what is called a "where" clause to the report. This
will thus restrict the report to a particular set of data.

The "where" clause is a simple sql "where" clause without the word where.

So, there is likely little need to create temp table, as you have a whole
sql engine that can retrieve the records and send them to a report any way
you like.

The only real issue here is how the "tag" field is setup. Is it a standard
"classic" one to many relationship between two tables, or was the designer
such that each new category ("tag") requited the additional of a new field
to the main table? (bad design).

Regardless, if we are talking about one table, and simply selecting records
based on a condition, then using the openreport with a "where" clause is the
way to go.

The code to launch a report behind the button would look like:


dim strWhere as string


strWhere = "YourtagFieldName = '" & cboBox & "'"

docmd.OpenForm "your label Report",acViewPreview,,strWhere

YouTagFieldName is of course going to be whatever the name of the field is
in the query that the label report is based on. cboBox is simply your text
box, or combo box placed on a UN-Bound form. The user would select/enter the
value in the text box, and that would restrict the labels in the report. I
use the above code idea all the time. Here is some screen shots of such
forms made just for prompting the users for reports:

http://www.attcanada.net/~kallal.msn/ridesrpt/ridesrpt.html


Note also, that you can dump the use of a "promt" form, and simply put
conditions into your queries, and the query will prompt you also.
 
Back
Top