Form or Report ??

  • Thread starter Thread starter Alan F
  • Start date Start date
A

Alan F

Not sure which to post this one to. I have a button on a form that opens a
report. Is there any way that I can specify which query the report gets it
data from at time of clicking the button.
 
Not sure which to post this one to. I have a button on a form that opens a
report. Is there any way that I can specify which query the report gets it
data from at time of clicking the button.
Click to expand...

Set the Report's RecordSource property to the name of the query.
 
Sorry did not explain my problem very well. At the moment I have a report
that gets it data from a query. I have 10 copies of the same report each
getting its data from a different query, what I would like to do is just
have one copy of the report and specify which query the report gets its data
from each time I open it using a command button on a form. Thanks, Alan
 
Sorry did not explain my problem very well. At the moment I have a report
that gets it data from a query. I have 10 copies of the same report each
getting its data from a different query, what I would like to do is just
have one copy of the report and specify which query the report gets its data
from each time I open it using a command button on a form. Thanks, Alan
Click to expand...

How do the ten queries differ? This seems a strange way to do this! If
you have different *criteria* for the ten queries, maybe you could use
just one Parameter query.

If you really do need ten unrelated queries then you will need to use
VBA in the Form's open event to look at the Form and identify which
query is needed. How do you "specify" which query is needed?
 
Hi, thanks for the reply.

I just specify the name of the query in the reports record source.

Have 10 sets of tables one set for each customer having thousands of address
in each set of tables.

Have 10 forms one for each customer with a command button on it to print one
of the 10 reports.

The database is split front end on 6 computers with the back end on a
server, we find by keeping the customers properties in separate tables the
database is very fast over the network.

We have to tender for the customers work each year and if we do not get it
that year then the data is not mixed up with all the other customers.

Hope this explains how we use it at the moment.
 
Hi, thanks for the reply.

I just specify the name of the query in the reports record source.

Have 10 sets of tables one set for each customer having thousands of address
in each set of tables.
Click to expand...

A better design - a FASTER, and MORE EFFICIENT design as well - would
be to have one table of addresses, with an indexed CustomerID field.
Having the ID field keeps the customers' data separate; you might have
some redundancy if the same address appears in two or more customers'
data, but no more redundancy than you have now.
Have 10 forms one for each customer with a command button on it to print one
of the 10 reports.
Click to expand...

If you really, really need to do it this way (and I'd recommend using
ONE form with a changable recordsource, not ten forms!), you'll need
VBA code in the Report's Open event to set the report's Recordsource
appropriately. With ten forms you can't easily refer to the calling
form in this code - what you might need to do is set a global variable
in the code that opens the form, and use the report's Open event to
read that variable.
 
Hi John,

Thanks for the advice, I think I will make a new database and import all the
tables into one large one as you suggest then filter on customer ID and give
it a try to see how fast it is. I may run the two along side each other
until I decide which is best.

One last thing if I combine all the tables then open a form filtering using
the Customer ID will this just send that customers records over the network
or will they all come over and then be filtered in the front end.

Many Thanks, Alan
 
One last thing if I combine all the tables then open a form filtering using
the Customer ID will this just send that customers records over the network
or will they all come over and then be filtered in the front end.
Click to expand...

The query optimizer will use the Index - you won't get any records
except the ones which match the CustomerID (and which additionally
match any other criteria you apply, provided the other criteria fields
are also indexed). Be sure there's a nonunique Index on CustomerID of
course!

I'll be interested to hear the comparison. If performance is
unacceptable with the single table solution (and ok with the ten
tables) then you may be stuck with the added complexity!
 
Back
Top