Report Record Source

[Lionel Fridjhon]

I have a report called "rptInventory" that I need to be
able to print from any one of 9 identical tables. The
tables are named "tblInventory1" through "tblInventory9".

I have procedure that runs on the "On Format" event that
I think can be used to write another line of code
setting "Record Source" for the report.

How do I do this?

Lionel

 

[Steve Schapel]

Lionel,

I think you'll find that a report's recordsource can only be changed
on the On Open event of the report.
Me.Recordsource = "tblInventory"

As a side issue, the existence of "9 identical tables" would almost
certainly indicate a design flaw in the database, which is probably
making life more difficult than necessary for you. If you are
interested in some advice with this aspect, post back with some
details of these tables, either here or in the tablesdbdesign
newsgroup.

- Steve Schapel, Microsoft Access MVP

 

[Lionel Fridjhon]

Steve
Thanks for all the input.

I now have the piece of code that you sent me to show the
Windows print dialog box. It looks like this:
=====================
Private Sub cmdRunReport_Click()
Dim Response As Integer
On Error Resume Next
Response = MsgBox("Do you want a preview
before sending to the printer", vbYesNo, "PREVIEW?")
If Response <> 7 Then
DoCmd.OpenReport ("rptInventory"),
acViewPreview
Else
DoCmd.OpenReport ("rptInventory"),
acViewNormal
End If
DoCmd.SelectObject
acReport, "rptInventory"
DoCmd.RunCommand acCmdPrint
DoCmd.Close acReport, "rptInventory"
End Sub
======================================
If I press "No" the print spools straight to my default
printer, and then offers me the dialog box for selecting
the printer I want, which then prints not hte report, but
the form on which the command button is residing.
If I press "Yes" everything works fine, but there is
apparently so much work for the computer to assemble
first the preview,and then spool to the printer that it
does not complete the insertion of all the images, aznd
prints with half of them missing.

This is also the reason I have broken up the database
into nine tableswith identical design, with different
data for each page of the printout.

I agree that there must be a serious design flaw in my
database, and would welcome some input from you.

Can I send you the whole database without the images?
It is about 465 Kb.

 

[Steve Schapel]

Lionel,

May I suggest you simplify the process here by giving the user 2
command buttons, one for Print and one for Preview?

The code I suggested before was not for Preview, it was for Print. It
opens the report in preview, and gives you the print dialog before
sending to the printer. Not much point in trying to select the
printer after it has been printed.

To give the user the option to Preview *and* select print options, the
only way I know of to do this is make a custom menu bar, with a Print
command on it. So the code on the Preview button on your form is
simply
DoCmd.OpenReport ("rptInventory"), acViewPreview
.... and then you make a public subroutine in a module with the code
DoCmd.RunCommand acCmdPrint
.... which you assign to the item on the custom menu bar, and then put
that menu bar in the Menu Bar property of the report.

With your 9 tables, I assume that the data in them is distinguished
one from another because they are different categories or groupings or
some such. Is this right? There would be no advantage whatsoever in
separating them into different tables, and many disadvantages. Put it
all in one table. Then, if you want just one of the 9 groups, use a
Query to retrieve that data from the table.

It is possible that there are system resource issues (e.g. printer
memory) that may be contributing to your difficulties. But if you
simply print the report straight to the printer, does it come through
correctly, with all the graphics etc?

- Steve Schapel, Microsoft Access MVP

 

[Lionel Fridjhon]

Steve
I gave the user 2 buttons as suggested.

"Report Preview" button works perfectly as it has only
the one line of code:
DoCmd.OpenReport ("rptInventory"), acViewPreview

"Run the Report" button first produces a preview before
offering me the print dialog box. When I send to the
preinter, teh last one or two of thet 12 images on the
page do not print.

If I run the printing procedure through the Windows
File/Print... menu, I get the print dialog box, and then
straight to the printer, without preview and everything
works fine.

It seems to me that the extra step of first producing a
preview, and THEN spooling to the printer, uses up too
much of the resources of the computer or the printer.
So the question remains - how do I through the command
buttons, persuade the system to skip the preview that is
apparently inherent in opening the report, and get
straight to tthe print dialog box?

This is basically the reason I have broken up the table
into small managable pieces.

Thanks again for all your help and I look forward to
another genius solution from you.

Lionel

 

[Steve Schapel]

Lionel,

"Report Preview" button works perfectly as it has only
the one line of code:
DoCmd.OpenReport ("rptInventory"), acViewPreview

Fine... except that this doesn't provide the same functionality as you
were trying to get before, i.e. the print dialog if you want to print
from the preview.

"Run the Report" button first produces a preview before
offering me the print dialog box. When I send to the
preinter, teh last one or two of thet 12 images on the
page do not print.

This is a difficult problem to diagnose, but I agree that it is likely
to do with system resources.

If I run the printing procedure through the Windows
File/Print... menu, I get the print dialog box, and then
straight to the printer, without preview and everything
works fine.

You could replicate this behaviour by the following code. However,
this assumes your application is set up with access permitted to the
database window, which in my experience is unusual.

DoCmd.SelectObject acReport, "rptInventory", True
DoCmd.RunCommand acCmdPrint

It seems to me that the extra step of first producing a
preview, and THEN spooling to the printer, uses up too
much of the resources of the computer or the printer.
So the question remains - how do I through the command
buttons, persuade the system to skip the preview that is
apparently inherent in opening the report, and get
straight to tthe print dialog box?

The preview isn't inherent in opening the report. It's just that the
printing function refers to the active object/window, and one way to
make the report the active window is to open it in preview.

If you need to have programmatic control over the options the users
will have, you can use something like...
Dim P1 As Integer
Dim Px As Integer
Dim Pq As Integer
Dim strPrinter As String
Dim UsualDefault As String
UsualDefault = GetDefaultPrinter
strPrinter = InputBox("Enter name of Printer to use", "Printer")
SetDefaultPrinter strPrinter
P1 = CInt(InputBox("Enter first page number", "From:"))
Px = CInt(InputBox("Enter last page number", "To:"))
Pq = CInt(InputBox("Enter the number of copies to print...",
"Copies", 2))
DoCmd.OpenReport "rptInventory"
DoCmd.PrintOut acPages, P1, Px, acHigh, Pq
SetDefaultPrinter UsualDefault

The default printer functions in the above code are based on code
written by Albert Kallal, and available at
http://www.attcanada.net/~kallal.msn/msaccess/msaccess.html

This is basically the reason I have broken up the table
into small managable pieces.

As I tried to intimate before, this thinking is misguided. Breaking
your data up into multiple tables will not affect this problem in any
way, not even to the most miniscule extent.

Thanks again for all your help and I look forward to
another genius solution from you.

- Steve Schapel, Microsoft Access MVP

 

[Lionel Fridjhon]

Steve
Well, with your help I got the program going.
I used the following code to get around the fact that my
application is not set up with access permitted to the
database window:
'====================================================
Private Sub cmdRunReport2_Click()
Dim Response As Integer
DoCmd.OpenReport ("rptInventory2"),
acViewDesign
DoCmd.RunCommand acCmdPrint
DoCmd.Close acReport, "rptInventory2"
End Sub
'=======================================================
By having the first DoCmd bring up the design mode of the
report, I ensured that the object was selected, while
avcoiding the preview from coming up when you open the
report. Anyway, whether my reasoning is correct or not -
it works.
However, I was unable to get past the fact that my system
is unable to assemble more than about 25 images at one
printing, I am still living with four independent tables
and reports. Maybe, when I have time, I will make one
table and have the report have a query as its record
source. Might be simpler, but I am now behind with
delivery of the program so I am shelving that for the
time being.
Steve, thanks again for all your help - you were a great
crutch to lean on.

Lionel