jasveer

I am using VB with an Access Relational Database

I have several forms with command buttons allowing records to be added etc....


What I want to happen when I click a command button called Print Shift Report on the employee shift form is to run a Query already created and saved in Access.

Can this be done and how much is involved in completing such a task...??

Any help would be greatly appreciated...

sachin

Hi
Running any query when u click the command button is not a problem. but how you will format the report???
please ellaborate your question.

sachin

jasveer

OK i understand what your saying...Can you give me any ideas as to how i could do it.

At the moment I am using VB to add/update records in the access database but am not sure how i could create a similar report in visual basic which would run from a command click.

I am kind of new to VB....

any help would be greatly appreciated...

thanks in advance

wadair

Have you tried setting the command button to run an Access report based on the Query ?

jasveer

sorry to sound off the planet or the something but how would i got about doing that? and would the format of the output report be correct

wadair

Assuming that you already have the query created in Access, you just have to create a report based on that query then set the command button to run the report. When you use the wizard in access it allows you to format the report in several ways. Its kind of a 3 part process....Do you have design access to the database ?

jasveer

yes I have.

The report has already been created in access.

wadair

I programmed mine in Access VB so I set the command button like this

Private Sub Command38_Click()
DoCmd.OpenReport "Report Name", acViewPreview
End Sub

jasveer

okay, How does VB now the location of the report?

how do I enter the path of the report/database

wadair

Are you using a seperate VB app to access the database or are you using VB within the database itself

jasveer

HI! i am using VB app to access the database (access 97) VB V6.0

Robby

You can retrieve reports created in Access, BUT keep in mind that all users will need to install Access on their machine.

jasveer

Robby...

Lets suppose the report I had created in Access 97 was called employee shift or empshift and, that my database was called jas and saved in c:\jas\jas

What would the code for the shift report command button be? bearing in mind the code you stated previously? And how would I reference the version of access?

thank you in advance

Thinker

To set a reference, go to the Project menu, and select References.
Find Microsoft Access 8.0 Object Library, check the checkbox and
OK.

In Robby's example, replace "myPathToTheDatabase" with
"c:\jas\jas\jas.mdb". Replace "myReport" with "empshift".
Replace "myCondition Optional" with whatever parameters you
need to pass to make the report run correctly.

jasveer

Thank You... JUST TO RECAP.

Would this be correct?

Private Sub Empreport_Click()

Dim appAccess As Access.Application
Set appAccess = New Access.Application
appAccess.OpenCurrentDatabase ("c:\jas\jas.mdb")
appAccess.DoCmd.OpenReport "empshift", acPreview, , "myCondition Optional"

appAccess.Quit
Set appAccess = Nothing

End Sub

Secondly, what additional properties do I have to setup for the empreport command button if any?

thank you....

Robby

This line....
Should be (if you have no criteria)
Or you can do somthing like this...
Where FirstName is a field in the underlying table and srtName is a variable holding a persons' name.

jasveer

thank you i will give it a try

jasveer

Thanks Robby, for that it worked however this is the what I want to happen.

I want to be able to open the report without opening Access (I know this is not possible, unless you know otherwise) therefore, how can I create a text/report file in VB that contains the same fields in the original access report.

Two tables are linked by shiftcode

The tables are

Shift and employeeshift
Prim Key Shiftcode txt field Shiftcode txt field
Day txt field FKey Employeeno txt fld
Type txt field FKey WeekNo

The report should have Shiftcode, WeekNo and Employeeno as headings.


Help!!!!!

Memnoch1207

You can set up a Data Environment linked to your query in Access. Then create a Data Report and place the fields from the query on it. Then everything would work...the report wouldn't be opened with access, it would be opened from within your VB Application.