[RESOLVED]printing access reports using vb.net, you be da man robdog

It's like the title said: how can I print a certain page from a report from Access using visual basic?

I hope it's called report, in dutch it's called like that anyways.
Thanks for any feedback.

Welcome to the Forums.

So are you automating Access using COM?

im using vb.net, nothing like COM i guess

Well if you have a reference set to use Access xx.0 Object Library then thats using COM, so just to be sure we are on the same page.

Usually you just reference Access and printout a report but are you shouwing the user the Access report first as in a prrint preview?

I have no idea what you're talking about, this is what I have for now: a report, a database, a database connection, a vb project which needs to print reports, and preview them first.

I have no idea whatsoever how to print, and I need all the help I can get with that...

Ok, thats what I needed to know that you were displaying access and/or the report(s).

Have you added a reference to Access via the COM tab and instanciated an Access Application object variable?
This is the VBA format:

vb Code:

expression.DoCmd.OpenReport "Report1", acViewPreview

So is that in vb .net or in vba? I'm confused.

Thank god that you're helping me, I'm a real dummy in this program when it comes to using it for external programs like access...

Its universal, so to speak, but depending on how you are integrating your db will dictate how the rest of the code will go.

Can you answer the previous questions for me and then I can help with more.

Quote:

---

Have you added a reference to Access via the COM tab and instanciated an Access Application object variable?

---

Do you have msn because I have no idea what you're talking about. If you don't mind, my msn is [email protected]

Project menu item > Add Reference ... > COM tab > select Microsoft Acces xx.0 Object Library > click OK.

Sorry, busy writting an article and after that I need to do some painting :(

Ok, I did ok after the COM tab, now what do I have to do? because I don't see anything anywhere.

np btw, do your things first; I got a month.

I'm out for today, you do your thing, I'll be back tomorrow or tuesday.

hf doing whatever you do :)

Gee, Thanks. lol. I always have fun slaving away working on home repairs. :D

I have a FAQ on Access for .NET. The code example is 2003 but if you concentrate on the button1click event, its almost all in there and the same for 2005.

It will get your db connected and ready to display the report with...
moApp.DoCmd.OpenReport("Report1", Microsoft.Office.Interop.Access.AcView)

http://vbforums.com/showthread.php?t=402075

2 errors: "name 'moApp' is not declared." and "'AcView' is a type in 'Access' and cannot be used as an expression."

as what do i have to declare moApp? and what do i have to do to get rid of the 2nd error?

Did you even look at the code example I posted in the link?Everything is in there as I posted. ;)

I looked at it

That was pretty much it.

No, I tried to understand, but it was about interaction between excel and access so what does that have to do with viewing and printing data from access? Or is the code very very very simular to it?

edit: I knew you loved doing work around the house, just for that, you get from me 2 thumbs up!! :thumb: :thumb:

No, the button1 click event shows how to initialize and connect to your db and then use the docmd method.

Here, I'll post a copy from my thread.

vb Code:

Public moApp As Access.Application
 
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            moApp = DirectCast(GetObject(, "Access.Application"), Access.Application)
        Catch ex As Exception
            If TypeName(moApp) = "Nothing" Then
                moApp = DirectCast(CreateObject("Access.Application"), Access.Application)
            Else
                MessageBox.Show(ex.Message, "VB/Office Guru™ Access Demo", _
                MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            End If
        End Try
        moApp.Visible = True
        moApp.OpenCurrentDatabase("D:\RobDog888.mdb")
        moApp.RunCommand(Access.AcCommand.acCmdAppMaximize)
        moApp.DoCmd.Maximize()
        moApp.DoCmd.SetWarnings(False)
 
'Then add the other line I posted earlier.
moApp.DoCmd.OpenReport "Report1", acViewPreview 
'...
'...

what do i have to declare everything like?
I get errors of "not declared ... on these which I can't figure out what to make of:
MoApp, Access.Application, Access, AcViewPreview

I did find the mbKillMe to be a boolean :)
I'll go check the other code for declarations I think .. I'll edit if I got em all

edit: I can't declare as access.anything.At.All

How about I send you my current program + the database? I'll add in the mail what exactly the program more needs, and then you can help me, but pointing me in the direction is (should) be enough, if you do it all the way for me I don't learn anything :'(.

I edited my post and trimmed it down. It was a copy from my othe post as I was saying to get the logic out of it as that is what you need. So now as it is should work for you but I dont have time to write programs for members, just when I take breaks or ? from working. ;)

When you take breaks is fine for me.

Are you allowed to take "breaks"? :p

edit: I'm going to do this next year http://www.digitalartsandentertainment.be/

So I'll be needing C#, but I don't know anything about it yet (well, declaring but that's the same in almost all OOP languages) so you happen to know some free online "C# for dummies" ?

I've looked around in this place, but it's not really the basics, and that's what I need.

So, I type what code after the COM thing? because I still get errors like this:
"not declared ... on MoApp, Access.Application, Access, AcViewPreview

Quote:

---

Originally Posted by frederik

When you take breaks is fine for me.

Are you allowed to take "breaks"? :p

edit: I'm going to do this next year http://www.digitalartsandentertainment.be/

So I'll be needing C#, but I don't know anything about it yet (well, declaring but that's the same in almost all OOP languages) so you happen to know some free online "C# for dummies" ?

I've looked around in this place, but it's not really the basics, and that's what I need.

---

that place looks like it teaches C++, not C#

Do you have the reference added to Access xx.0 Object Library? Do you have the Office Primary Interop Assemblies installed? The code I posted earlier is valid if you have the two requirements I just typed

Office primary interop assemblies?
How, where, what?

Link in my signature.

intresting.

edit: I'll repost tomorrow or in the weekend if I have problems, me sleepy now, 23:40 here right now.

Gl, Hf.;)

Ok, I'm having problems: how do I install the pias stuff when I got both office and .net installed?
Or do I just have to do the COM reference? because it looks quite the same to me.

ok, it looks like I got the pias thing, so I'm guessing I did something wrong in the COM reference, going to do it again, going to try your code again, and I'll post later to let you know what happens.

Ok, I tried the COM thing again, but it doesn't do anything: I do as you say, I go to project> add reference> COM> Microsoft Access 11.0 Object Library

Then I click Select, do Ok, but nothing happens/changes after that.
What did I do wrong?

btw: I checked the pias stuff in my assembly map, and it's there (microsoft.office.interop.access)

Add the imports....

Imports Microsoft.Office.Interop

"imports statements must procede any declarations"

I'll still have to edit this code for my own report, but what's wrong with the imports... ?

Code:

---

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Imports Microsoft.Office.Interop
        Dim mbKillMe As Boolean
        Dim moapp As Access.Appliaction

        Try
            moApp = DirectCast(GetObject(, "Access.Application"), Access.Application)
            mbKillMe = False
        Catch ex As Exception

            If TypeName(moApp) = "Nothing" Then
                moApp = DirectCast(CreateObject("Access.Application"), Access.Application)
                mbKillMe = True
            Else
                MessageBox.Show(ex.Message, "VB/Office Guru™ Access Demo", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            End If
        End Try

        If mbKillMe = False Then
            If moApp.CurrentDb.Name <> "D:\RobDog888.mdb" Then
                moApp.OpenCurrentDatabase("D:\RobDog888.mdb")
            End If
        Else
            moApp.Visible = True
            moApp.OpenCurrentDatabase("D:\RobDog888.mdb")
        End If

        moApp.RunCommand(Access.AcCommand.acCmdAppMaximize)
        moApp.DoCmd.Maximize()
        moApp.DoCmd.SetWarnings(False)

        expression.DoCmd.OpenReport("Report1", acViewPreview)
    End Sub

---

Goes at the top of your file but right below the "Option Explicit On" or "Option Strict On" statement(s).

what statements?
sorry if I'm such a crappy vb'er, but I've never done what you're saying before. Also never seen option strict/explicit yet.

You should be using them. They help to require variable definition and validate casting between data types.

vb Code:

Option Explicit On
 
Imports Microsoft.Office.Interop
 
Public Class Form1
 
    Private Sub Button1_Click(Blah, blah...

what do i have to declare the expression like?

Code:

---

expression.DoCmd.OpenReport("Report1", acViewPreview)

---

this one.

Post #17.

Yes, I found it. (even before you posted it WOW)

I debugged it, (changed the database pad and report name ofc), but after clicking the button I get an error on the moApp: "An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in printen.exe

Additional information: The expression contains an invalid * to the attribute Visible."

In dutch the word is "verwijziging", but I don't know how to translate it ...
Like ... it points/directs to something else.

Take the "moApp.Visible = True" line out by commenting it. Some versions of Access may not support it I think.

YES, ITS ALIVE, ITS ALIVE

Now, how can I select a certain page from the report? Because it's 676 pages long, and I don't think the guy giving me work(what's that in english again...) wants to print 676 pages every time he wants a report.

edit: This is the actual thing: need to be able to search a certain record out of a datagrid, and all that data: meaning the report made of that record.

But I think it's quite impossible to do that (unless you prove me wrong) so is it possible to show the report in a visual basic window, choosing a report there and printing that one?

There should be a print to and print from argument of the .Print method. Or how are you invoking the print as of now?

invoking the print?

this is all my code, maybe that tells you what you need:

Code:

---

Option Explicit On
Option Strict On
Imports Microsoft.Office.Interop


Public Class Form1
    Inherits System.Windows.Forms.Form



#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer. 
    'Do not modify it using the code editor.
    Friend WithEvents btnAfdrukken As System.Windows.Forms.Button
    Friend WithEvents Button1 As System.Windows.Forms.Button
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.btnAfdrukken = New System.Windows.Forms.Button
        Me.Button1 = New System.Windows.Forms.Button
        Me.SuspendLayout()
        '
        'btnAfdrukken
        '
        Me.btnAfdrukken.Location = New System.Drawing.Point(96, 24)
        Me.btnAfdrukken.Name = "btnAfdrukken"
        Me.btnAfdrukken.TabIndex = 0
        Me.btnAfdrukken.Text = "Afdrukken"
        '
        'Button1
        '
        Me.Button1.Location = New System.Drawing.Point(264, 24)
        Me.Button1.Name = "Button1"
        Me.Button1.TabIndex = 1
        Me.Button1.Text = "Button1"
        '
        'Form1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(680, 502)
        Me.Controls.Add(Me.Button1)
        Me.Controls.Add(Me.btnAfdrukken)
        Me.Name = "Form1"
        Me.Text = "Form1"
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub btnAfdrukken_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAfdrukken.Click

        Dim moApp As Access.Application
        Dim acViewPreview As Access.AcView

        Try
            moApp = DirectCast(GetObject(, "Access.Application"), Access.Application)
        Catch ex As Exception
            If TypeName(moApp) = "Nothing" Then
                moApp = DirectCast(CreateObject("Access.Application"), Access.Application)
            Else
                MessageBox.Show(ex.Message, "VB/Office Guru™ Access Demo", _
                MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            End If
        End Try

        'moApp.Visible = True
        moApp.OpenCurrentDatabase("C:\Documents and Settings\Joris\Bureaublad\Nieuwe map\GGIP\BierDatabase.mdb")
        moApp.RunCommand(Access.AcCommand.acCmdAppMaximize)
        moApp.DoCmd.Maximize()
        moApp.DoCmd.SetWarnings(False)


        moApp.DoCmd.OpenReport("rptBier", acViewPreview)
        moApp.CloseCurrentDatabase()

    End Sub
End Class

---

Well you are previewing the report so if the user clicks print from the Access toolbar then it will print all pages. If they use the Print... from the File manu then they can designate the pages to print.

Actually, it's not previewing anything, not even before I added the CloseCurrentDatabase.

It all has to work out of the program, this fatso is too much of a lazy bum to learn how to use access, so I gotta make it in vb for him. So no file menu for me I guess..

I'm out for today, it's getting kinda late.
I'll see you tomorrow I hope :).

My personal vb hero ^^

Np.

There are two things more you can do.

One: filter the reportsource query to only produce the final printed recordset needed and then .OpenReport passing the Normal view argument which is print.

Two: call the printout method but that will print all the pages.

and how do I make that filter?

Add a where clause to the sql query that is your reportsource.

I'll see what I can do.

Questions to come later :)

Ok, maybe in a new thread as its a bit of a different topic. Plus, other members may be more inclined to help in a new thread instead of reading through tweo pages of the original topic.

Ok, but still in the end thisis what the program is supposed to do, just so we're on the same page.

Start up a self-made program, view the database in datagrids and be able to edit the database (already done).

Be able to search something inside the same program using terms like "bee".
Then the program should search everything which corresponds to *bee*.

Be able to print the record which was looked up by the search function in the form of a report. Or either just be able to view the reports one by one in the program and print one to your own choice out.

Last one: if you click "go back" in a window of the program after you made changes to the database, but didn't click on update there should be a pop-up window asking wether you want to quit, or go back and save the changes.
Ofc if you click go back to save changes, you should go back and be able to save the changes.

This is just what my program has to do in the end. Is it possible?

Second to last item, if they are to select a record out of the report then it should not be in a report (report records are not selectable in preview mode). Create a secondary form to display the query results and then what ever they check you can pass to the report for printing.

As long as it all happens inside the program, it's good.

Em like??
the report corresponding to the record you selected in the datagrid I mean.. not a record out of a report. I'm not that stupid.

I think its not checkable under the previous Access versions but test it just in case. 2007 you can check a checkbox in a report in preview view.

Ps, didnt mean it that way. ;)

Well, I'm going to come back at this on monday, after I saw my teacher. She might give a hint.

Ok, no hints :'(
Is there a way to view the report inside the program?

EVERYTHING HAS TO HAPPEN INSIDE THE PROGRAM, just to stay on the same page ;)

So you are saying that you can not display Access at all?

Yes, that's what I'm saying.

I think I got a crap work for school btw, I didn't choose to do this.