[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Printing Reports from Visual Basic

Posted on 1999-12-13
29
Medium Priority
?
350 Views
Last Modified: 2013-11-25
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.
0
Comment
Question by:clckwrkorange
  • 14
  • 13
  • +1
29 Comments
 
LVL 4

Expert Comment

by:abaldwin
ID: 2277521
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
 

Author Comment

by:clckwrkorange
ID: 2277743
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
 

Expert Comment

by:pspecketer
ID: 2277784
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 4

Expert Comment

by:abaldwin
ID: 2277806
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
 
LVL 1

Expert Comment

by:prem_kumar_25
ID: 2279031
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
 

Author Comment

by:clckwrkorange
ID: 2280026
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
 

Author Comment

by:clckwrkorange
ID: 2280108
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
 
LVL 4

Expert Comment

by:abaldwin
ID: 2280205
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
 
LVL 4

Expert Comment

by:abaldwin
ID: 2280219
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
 

Author Comment

by:clckwrkorange
ID: 2280365
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
 
LVL 4

Expert Comment

by:abaldwin
ID: 2280449
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
 

Author Comment

by:clckwrkorange
ID: 2280528
That works except I get an error that says "object required", but I have the correct path inserted. Any idea?
0
 
LVL 4

Expert Comment

by:abaldwin
ID: 2280575
Can you tell me on what line of code the error is generated from?
0
 

Author Comment

by:clckwrkorange
ID: 2280598
Sure, it's:

cryReport.ReportFileName = "n:\projects\cja\cja.rpt"
0
 
LVL 4

Expert Comment

by:abaldwin
ID: 2280660
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
 

Author Comment

by:clckwrkorange
ID: 2280869
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
 
LVL 4

Expert Comment

by:abaldwin
ID: 2281060
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
 

Author Comment

by:clckwrkorange
ID: 2281187
How do I: "Make sure that the Crystal Report control is Named in the properties window of the VB IDE as cryReport."
0
 
LVL 4

Expert Comment

by:abaldwin
ID: 2281210
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
 

Author Comment

by:clckwrkorange
ID: 2281245
How do I: "Make sure that the Crystal Report control is Named in the properties window of the VB IDE as cryReport."
0
 

Author Comment

by:clckwrkorange
ID: 2281340
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
 
LVL 4

Expert Comment

by:abaldwin
ID: 2281353
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
 

Author Comment

by:clckwrkorange
ID: 2281368
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
 
LVL 4

Expert Comment

by:abaldwin
ID: 2281425
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
 

Author Comment

by:clckwrkorange
ID: 2281470
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
 

Author Comment

by:clckwrkorange
ID: 2281494
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
 
LVL 4

Expert Comment

by:abaldwin
ID: 2281564
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
 

Author Comment

by:clckwrkorange
ID: 2281581
Thank you very much!!!
0
 
LVL 4

Accepted Solution

by:
abaldwin earned 800 total points
ID: 2281652
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question