Printing Reports from Visual Basic

I am using Visual Basic's DBGrid to access a Microsoft Access database. I have a simple flat database design, and I want to be able to allow a user to print a report in which they could choose a FROM date and a TO date. The Report would then print out the records from the FROM and TO dates.
clckwrkorangeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

abaldwinCommented:
What information do you want on the report?  I suggest using the Crystal report control, create teh report and reference it from the application.  You can also send the recordselection criteria to the report.  If this seems like the route you wish to go then I can send you some code to demo the recorselection and printing the crystal report.
0
clckwrkorangeAuthor Commented:
That sounds like what I'm looking for.
My Database is broken into a series of records, all records have a date that is associated with them, so what I need to do is to be able to print a report from a user selected date range.

The main help I need is how to query the database and send only that information to Visual Basic (5), then how to get the report to print through Visual Basic. Since it's not hard, but time consuming, I'll up the points
0
pspecketerCommented:
If you can upgrade, this is one of the new cool toys!

NOTE: Starting with Visual Basic 6.0, Microsoft has included a new report generator called the Microsoft Data Report Designer. You may use either the new Report Designer or Crystal Reports. For more information about the new Report Designer, please search the MSDN Library included within Visual Studio 6.0 on the phrase "Microsoft Data Report Designer."

This is from msdn.  I've used the data report designer and like it.  it allows you to create a report like in Access and pass the recordset at runtime.

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

abaldwinCommented:
In this particular instance I have a report form that has one Crystal Report control on it.  The control is named cryReport.  In the following illustration I am using the same crystal control for each report that I want to open hence the need to set the reportFileName property of the control at run time.

After designing the Report in Crystal make sure you remove any Selection Criteria from the report designer or you may get an OR statement taking place.

I have two text boxes  mskStartDt and mskEndDt  These are where the end user will enter the beginning date and ending date in the oter.  I added some basic (probably need more) checking to see that they entered a good date.  Probably ought to add the fact that one date is lower than the other.

In the crystal control I have the Destination Property set to crptToWindow which will "Print Preview" the Report, And set the DiscardSavedData to True.  This will force the report to use the criteria we are sending it from the code.

See if this gets you going and holler if you need any more help.
Laterdays


    'Prints the Return Code Analysis report
    Screen.MousePointer = vbHourglass
    If IsDate(mskStartDt.Text) And IsDate(mskEndDt.Text) Then
        cryReport.ReportFileName = "<Insert path name to the Crystal Report file here>"
        cryReport.SelectionFormula = "{DateField} in Date (" & Year(mskStartDt.Text) & ", " & Month(mskStartDt.Text) & ", " & Day(mskStartDt.Text) & ") to Date (" & Year(mskEndDt.Text) & ", " & Month(mskEndDt.Text) & ", " & Day(mskEndDt.Text) & ")"
mskStartDt.Text & " and " & mskEndDt.Text
        cryReport.WindowState = crptMaximized
        cryReport.Action = 1
    Else
        MsgBox "You must enter a valid date in both the Start and End date boxes to enable the code analysis reports to run.", vbCritical, "INVALID DATES"
    End If
    Screen.MousePointer = vbNormal
0
prem_kumar_25Commented:
Create a temporary Record set Then get the from date and to date in two text boxes , then filter the information from the master Recordset to the temorary Recordset using if condition .
then design the Crystal report from the temporary record set.

NB make sure that the temporary record set is cleared always while loading the form or at the begining of the event
0
clckwrkorangeAuthor Commented:
That doesn't really help me with my whole problem Prem.

ABaldwin, the code you put that populates the date field in the Crystal reports, does that also serve as a query in order to only show the dates in which the user picks?
0
clckwrkorangeAuthor Commented:
abaldwin
the line of code:

cryReport.SelectionFormula = "{DateField} in Date (" & Year(mskStartDt.Text) & ", " & Month(mskStartDt.Text) & ", " & Month(mskendDt.Text) & ", " & Day(mskendDt.Text) & ")" mskStartDt.Text & " and " & mskEndDt.Text

gives me a: Compile Error, Expected: end of statement
0
abaldwinCommented:
What is the compile error?  If it is variable unknown and highlights the cryReport word in the code then the Crystal control on the form is not named cryReport.  Either change teh control name or modify the code with the actual name of the control.
I actually took that section of code out of an application we use for Service.  The code is behind one of the Report Command buttons on the report menu and works fine.  What some of my programmers acutally do with crystal is to create the criteria in the record selector in crystal, then edit the criteria so they can see the whole thing.  You can then simply past it into VB and with a few modificiations to the "'s and {'s you have a working criteria.   I have done this a lot when working with SQL and Access when I first started.  USE the DBE grid in Access to create the SQL, then view the SQL and copy it to VB.

0
abaldwinCommented:
Sorry did not see the Expected end of Statement at the end of your post.

The code I gave does a between statement for two entered dates.  the last half of the selection criteria is left off then the code will fail.  Also during run time both dates have to be present.
0
clckwrkorangeAuthor Commented:
Is there something that I need to add to make it complete. Both dates are present and I get the error typping in the code, and not at runtime.
0
abaldwinCommented:
Remove the following part from the very end of the Selection formula in my original code.

mskStartDt.Text & " and " & mskEndDt.Text

Somehow in my copying and pasting I got and extra part.

0
clckwrkorangeAuthor Commented:
That works except I get an error that says "object required", but I have the correct path inserted. Any idea?
0
abaldwinCommented:
Can you tell me on what line of code the error is generated from?
0
clckwrkorangeAuthor Commented:
Sure, it's:

cryReport.ReportFileName = "n:\projects\cja\cja.rpt"
0
abaldwinCommented:
Dont mean for this to sound like a dumb question buttttt.....Have you created the report in crystal yet?   And if so is that report saved to

N:\projects\cja\cja.rpt

Also on the crystal report control on the form  make sure that the ReportFilename property is blank.  this way the code will set it at run time.  Have had this happen to me in the past where the code for one reason or another would not change the crystal prpoperty.  I think it was something with the registered vs unregistered dll's



0
clckwrkorangeAuthor Commented:
I have already created the report, and when you say to make sure that the ReportFilename property is blank do you mean a ReportFilename = "" statement. That doesn't seem to work either.
0
abaldwinCommented:
Make sure that the Crystal Report control is Named in the properties window of the VB IDE as cryReport

In the properties of the crystal report itself on the form. (in the vb IDE)  Make sure that the reportfilename is blank.  

Is the N Drive a network mapped drive?

IF so make sure you are connected to it.

If it is a network drive you can try to use the UNC path instead.  For instance
cryReport.ReportFileName = "\\<ServerName>\<ShareName>\<Folder>\<SubFolder>\<SubFolder>\cja.rpt"

IF you need help with the UNC get your IS department to tell you the name or look in My Computer and let me know what server it is on.

In your case somethign like
cryReport.ReportFileName = "\\<ServerName>\Projects\cja\cja.rpt"
0
clckwrkorangeAuthor Commented:
How do I: "Make sure that the Crystal Report control is Named in the properties window of the VB IDE as cryReport."
0
abaldwinCommented:
Open the VB project that contains this form.  Activate the Crystal Report Control you laid on the form earlier.

In the properties window (usually to the right of the displayed form) will be displayed the properties of the control you have activated.  the crystal report control has a name property.  Click in the name property and give it a name of cryReport.

this is the same place that you give names, fonts, tab indexes, etc etc etc to the other controls on your form.

If the properties window is not visible you can make it visible through the View Menue or simply hitting the F4 key while in design time.

Once the control has the right name the code should work like a charm.
0
clckwrkorangeAuthor Commented:
How do I: "Make sure that the Crystal Report control is Named in the properties window of the VB IDE as cryReport."
0
clckwrkorangeAuthor Commented:
ahh the crystal reports control. That took care of that, but now I get the error:

Run-time error '20515':

error in formula <Record_Selection>.
'{Datefield} in Date (1999, 10, 12, 20)'
This field name is not known.
0
abaldwinCommented:
Replace the word "Datefield" inside the "{}" with the actual field name from you table.

Not knowing all your table layouts i would also include the table name

{Table1.FieldName}  ex
{Contacts.ContactDate}  where Contacts is the table name and ContactDate is the Field name that hold the date of contact.
0
clckwrkorangeAuthor Commented:
Hopefully the last thing. Another error:

Run-time error '20515':

                 error in formula <Record_Selection>.
                 '{main.date} in Date (1998, 10, 12, 25)'
                 Too many arguments have been given to this function.
0
abaldwinCommented:
It is not a very good idea to use reserved words for field names.   I.E. DATE.  That can cause problems especially in code.
In a previous post here I stated I copied something wrong in the Selection formula.  I inadvertantly copied too much code to past to you

You need to remove the extra piece from the selection formula line.  I believe it is
mskStartDt.Text & " and " & mskEndDt.Text  
at the very end.

If you look at the error.  Crystal is saying that the date is in (1998, 10, 12,25)  Here let me recopy the code and I will modify it to fit your table and Date fields.  BTW  I really wish we had a wider space to type in   sorry if this looks strange in the reply.

cryReport.SelectionFormula = "{Main.Date} in Date (" & Year(mskStartDt.Text) & ", " & Month(mskStartDt.Text) & ", " & Day(mskStartDt.Text) & ") to Date (" & Year(mskEndDt.Text) & ", " & Month(mskEndDt.Text) & ", " & Day(mskEndDt.Text) & ")"

Remember that I had two text boxes on the form for them to enter their range in.  One is called mskStartDt and one is called mskEndDt.  If you copy the portion of code above and place it in your project you should have it.

Any more question just holler.
Andy


0
clckwrkorangeAuthor Commented:
That did it. THANK YOU!!!, and the last part: That allows me to preview the report. How do I print the report once it is called up?
0
clckwrkorangeAuthor Commented:
That did it. THANK YOU!!!, and the last part: That allows me to preview the report. How do I print the report once it is called up?
0
abaldwinCommented:
In the properties of the Crystal Report (on the vb form)  There are some properties near the bottom that start with the word "Window"
i.e.
WindowShowPrintBtn - Makes the print report button visible after display
WindowShowPrintSetup - Makes the Printer setup and selection button visible
If you set both of these to true then the report will preview with these buttons visible in the tool bar.

On the other hand if you want the report to print directly to a printer and not preview then you have some other settings to make.

Destination 1-crptToPrinter
Printer Name -Choose the windows name of your printer
PrinterPort - The port from the details tab of the windows printer setup
PrinterDriver - not usually needed but sometimes needed for some printers.  (Have never used this)

0
clckwrkorangeAuthor Commented:
Thank you very much!!!
0
abaldwinCommented:
Not a problem.  I will propose this as the anser and then you just have to accept the answer and grade it.  I rather enjoy seeing people learn.  I had a lot of weird questions when I was starting.  W/O questions there is no learning.
Laterdays
Andy
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.