Generating A report in Access

[chris lynch]

I want to generate a report on access using vb is this possible. I found some SQL that said i can dump data into an excel file using insert into. Ant thoughs on the best approach,

[Malim]

Yes, you can using vb with an access database. It might be helpful if you gave more information about what you are trying to do. I'm not sure why you would want bring excel into the equation.

[chris lynch]

The excel is just a though (not a very good one but a thought). Ok what i want to do is call a report from MS Access and either print it, or show it on the screen.

In my database i have studentnames and several class, i want to print reports in specific classes or students.
Do you need more info?
Hope i gave enough

[Malim]

Yes, you can do what you are describing. Are you using VB6 or VB.net? My suggestion would be that you try to figure out doing this on your own, by searching this forum, or MSDN. There is tons of information on how to do what you are asking on the internet. Since this is pretty basic, you'll be much better off, and you'll learn more, if you try to start this project on your own, and then when you have specific problems or errors post them and you'll get plenty of help. Good luck. :)

[chris lynch]

Thanks for that but i'm way ahead of ya this is what i've found, I'm using VB6. This should do what i want as far as i know i found bits and pieces form different sites, but nothing happens not even an error which is strange for me

Dim AccessApp As Access.Application
On Error Resume Next
Set AccessApp = New Access.Application

With AccessApp
.OpenCurrentDatabase App.Path & "D:\Documents and Settings\christopher.lynch\Desktop\Manual Time And attendance\Manualrecords.mdb"
.RunCommand acCmdWindowHide
.RunCommand acCmdAppMaximize
.DoCmd.OpenReport "Manual", acViewPreview
End With

[Malim]

Nothing happens because you have On Error Resume Next. You need an error_handler. Or take the On Error Resume Next out and see what happens. I haven't worked in VB 6 in so long, I can't help you much here. Try Catch blocks in vb.net are awesome!

[chris lynch]

Catch are in Vb.net you said YA, Can i use them in Vb6?

[Malim]

No. You have to use an error_handler. Did you try taking On Error Resume Next out?

[chris lynch]

ya i got an error saying user defined type not defined. This is really bugging me am I heading in the right direction?

[chris lynch]

Wait a second i have that part sorted now i'll be back later if it still gives errors

[chris lynch]

Ok this is waht i have so far. My code brings up a dialog box asking for parameters, which i have no idea what parameters there are looking for, This is my first time working with databases so bare with me if i don't understand wha\t you say ok. This is the code i have can you please explain what exactly is happening.

[Highlight=VB]
Dim AccessApp As Access.Application

Set AccessApp = New Access.Application


AccessApp.OpenCurrentDatabase App.Path & "\Manualrecords.mdb"
AccessApp.RunCommand acCmdWindowHide
AccessApp.RunCommand acCmdAppMaximize
AccessApp.DoCmd.OpenReport "Manual", acViewPreview
[\vbcode]

[Malim]

It looks like you are opening a query that has been written in Access. Open Access, then open your Manualrecords.mdb database, look at the query tab. There is probably a query named Manual that prompts for parameters. Open the query in design view and look in the criteria field and you should be able to see which fields the parameter is associated with.

[chris lynch]

I went back into access but this is my first time using access (or databases ever for that matter,) so i'm sorry but i have no clue what you mean, I went back into the manualrecords database i created and looked up the help on queries but that didn't help!!do you know if theres any Access expert in this forum? If not can you help me a bit more?

[Malim]

It sounds like your vb code is executing a query. What version of access are you using. Open the .mdb file. Click on Queries. There should be one called "Manual". If not, it might be under the Report section of your access database. Click on it to highligth it and open it in Design mode. You should see under the criteria section of your query which section is asking for parameters.

[chris lynch]

No not a quer, its a report. I solved it i had no AppAccess.Visible = True so this is my code now and it shows the report


Dim AppAccess As Access.Application

Set AppAccess = New Access.Application
AppAccess.Visible = True

With AppAccess
.OpenCurrentDatabase "D:\Documents and Settings\christopher.lynch\Desktop\Manual Time And attendance\\Manualrecords.mdb"
.RunCommand acCmdWindowHide
.RunCommand acCmdAppMaximize
.DoCmd.OpenReport "Manual", acViewPreview


End With


How can i only show the report and not the access background and is it possible to send the report to a printer aswell

[Malim]

Basically what you did was invoke the Access Application through VB code. You can return the recordset to a windows form or use Crystal Reports instead of Access. There is plenty of help on the internet and on this forum for you to figure this out. MSDN is a good place to start. Ultimately, the report has a record source which is a either a query, a table or a sql statement.