Access report with a twist

Hi all, hope someone can be of help. have successfully created routine to open an existing report, set it's record source to an SQL statement and print it out. This is done in VBA through an Access form. What i would like to do is open the report from a VB6 front end, set the record source to an SQL statement, print it out, save the report under a new name leaving the original template untouched. of course all this will occur without Access being visible to the user!This would be useful for archival purposes on a system I'm putting together. My code for the original approach is below:

VB Code:

Private Sub cmdReport_Click()
Dim strSQL As String
strSQL = "Select tblTransaction.UserID, tblTransaction.TransactionNumber,tblDetails.Quantity, tblProduct.SellPrice, tblProduct.Description, (tblDetails.Quantity * tblProduct.SellPrice) As Total  From tblTransaction, tblDetails, tblProduct" & _
" Where Month(Now) = Month (tblTransaction.SellDate)And tblTransaction.TransactionNumber = tblDetails.TransactionNumber And tblDetails.ProductID=tblProduct.ProductID Group By tblTransaction.UserID, tblTransaction.TransactionNumber, tblProduct.Description, tblDetails.Quantity, tblProduct.SellPrice;"
DoCmd.Echo False
DoCmd.OpenReport "rptTemplate", acViewDesign
Reports("rptTemplate").RecordSource = strSQL
DoCmd.Close , , acSaveYes
DoCmd.Echo True
DoCmd.OpenReport "rptTemplate", acViewNormal
DoCmd.Close , "rptTemplate", acSaveYes
End Sub

Could some one point me in the right direction as to how to do this please?

Thanks, Lol :bigyello:

I don't have the answer, but you could probably search on terms such as-
automating access reports

What I am pretty sure I have read is, all the users will have to have Office Pro (or at least Access) installed on their machines.
Is that going to be a problem ?

Hi Rob, not a problem re: Access on the machine. It's going to be a 1 pc system! Basically it'ss my industry project for a Business IT course I'm on. None of the lecturers know this so I'm in a tight spot! I've found a number of posts where a report is opened and printed from Vb but none where an existing report is printed and then re-named, hence the new thread. Because there's no SaveAs method in the DoCmd object I can't use that. One way, if possible, would be to create a new object "equal" to the existing report and re-name that. I just don't know how to assign all the properties, for want of a better word, of the existing report to a new Access report object. Then this new object could be stored using a SaveAs command.Any thoughts?

Lol :bigyello:

I'm not much help I fear.
It is only recently (out of necessity), that I managed to automate excel, to mirror my SGrid2's contents into Excel,
The User mentioned that Excel had neat printing abilities, and I knew that trying to print a grid that was wider than texas, was going to be a major task.
So automating Excel was a life saver.

I note that this site has a VBA forum. Would posting there be more fruitful ?

Thanks for your input Rob, if I source the solution elsewhere I'll post it up here for othe users.

Lol

There are possibilities for what you want to do. You could just create the report on the fly dynamically.
Then the "template" will be in your vb code.

This is my code example on how to create an Access report dynamically from VB6.

Hi Rob, I've looked at creating the report on the fly, only trouble being I couldn't figure out how to put text boxes for Sum functions into say report footer. Every time I tried to enter a position for the text box in the create string I got an error saying it was an invalid value. Plus it's easier to make really nice reports in Access. Any further thoughts?

Cheers, Lol :bigyello:

I always seem to do things the hard way :blush:

Try this out in VB6 by adding a reference to MS Access xx.0 Object Library

VB Code:

Private Sub Command1_Click()
    Dim oApp As Access.Application
    oApp.Visible = True
    oApp.DoCmd.OpenReport "Report1", acViewPreview, , , acDialog
    DoCmd.CopyObject , "Report1a", acReport, "Report1"
    Dim oReport As Access.Report
    Set oReport = oApp.Reports("Report1a")
    oReport.Print
End Sub

Thanks Rob, that looks to do exactly what I was trying to explain. i suppose the string to save it would be along the lines of:

oreport.DoCmd.SaveReport "C:\Lol\Report1a"

I'm self taught at most of this stuff as my college lecturers can't do most of it ;) If I've got the save string wrong feel free to correct me :D

Many thanks for your help, Lol :bigyello:

the oReport object does not contain a docmd object.
You would have to use the application's docmd to save the currently opened report.

VB Code:

oApp.DoCmd.Save acReport, "Report1a"

This would save the current version of the report to the db, not the filesystem.

Thanks again Rob, am on a steep learning curve on this project, I really apprecciate the help.

Cheers, Lol :bigyello:

Not a problem.

I noticed that you seemed to be asking how to save the report to a file?
Is this something else you need to do?

On a side note, say i wanted to save the report as say "MonthEnd"&Format(Now,"Medium Date") how would I do this? Would it be along the lines of

oApp.DoCmd.save acReport "'MonthEnd' & Format(Now,"Medium Date")" or would the ' delimiters be superfluous, or would the extra "" cause problems?
Reason I ask is these things will be saved as "Month End" and then the date appendage, either the current date formatted or a range of dates selected by the user as part of the original SQL string for the record source?

Lol :bigyello:

VB Code:

DoCmd.CopyObject , "MonthEnd_" & Format(Now,"mm-dd-yyyy"), acReport, "Report1"

The date delimiters will cause an issue so its better to do it this way. There are certain characters that Access
doesnt like for names of its objects and different characters that will messup your sql statement too.

Thanks Rob, that makes sense, will give it a try and post the results. Many thanks for your help! As I'm doing this from a VB6 front end and connecting to an Access dbase I assume I just initiate a connection in the standard way, do my report code, then close the connection?

Lol :bigyello:

Hi Rob, tried your code thanks but it sprang a few errors. here is the code I am using

VB Code:

Private Sub Command1_Click()
Dim cnABOF As ADODB.Connection ' set connection
Dim strConnection As String ' declare connection string
 
   
  strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=" & "C:\Documents and Settings\Lol\Desktop\Project Final 9th April\Database\French.mdb;" ' text for connection
Set cnABOF = New ADODB.Connection ' set new ADODB connection
cnABOF.Open strConnection ' open connection
   
   
   Dim oApp As Access.Application
    Set oApp = New Access.Application ' added this also
    oApp.Visible = True
    oApp.DoCmd.OpenReport (rptTemplate), acViewNormal ' my code for opening "cannot perform this action at this time
    
 
    DoCmd.CopyObject , "Report1a", acReport, "rptTemplate"
    Dim oReport As Access.Report
    Set oReport = oApp.Reports("Report1a")
    oReport.Print
oApp.DoCmd.Save acReport, "Report1a"
 
 
Set cnABOF = Nothing ' close connection
 
End Sub

I changed the code where you open the esisting report as I got a message saying too many arguments specified. But instead, even using the intellisense provided syntax i keep getting "cannot perform that action at this time" errors. I know the connection string is correct because I can run another form in my project with it to access the same db. Any thoughts please as to what's happening?

Cheers, Lol :bigyello:

rptTemplate should not be wrapped with parenthesis.
Also, why connect to the db using ADO when your not using it?

VB Code:

oApp.DoCmd.OpenReport "rptTemplate", acViewPreview

I thought I needed to be connected to the database from VB6 or how else would it know where to get the report from?

Lol

Hi Rob, just tried the re-done open command but still get the "cannot perform the operation at this time" message.

Lol :bigyello:

Ah! I missed that. I thought we were doing this from within the access db. I think I messed up :blush:

From VB6 we need to open the db using the Access Object Model like initially we were doing?

VB Code:

Private Sub Command1_Click()
    Dim oApp As Access.Application
    oApp.Visible = True
    oApp.OpenCurrentDatabase "C:\MyDB.mdb", False, "YourPassword"
    oApp.DoCmd.OpenReport "Report1", acViewPreview, , , acDialog
    'oApp.DoCmd.CopyObject , "Report1a", acReport, "Report1"
    Dim oReport As Access.Report
    Set oReport = oApp.Reports("Report1a")
    oReport.Print
    oApp.DoCmd.CopyObject , "MonthEnd_" & Format(Now,"mm-dd-yyyy"), acReport, "Report1"
End Sub

Hi Rob, just tried the code, added a line and ammended another (VB6 not happy), here it is below:

VB Code:

Private Sub Command1_Click()
Dim oApp As Access.Application
Set oApp = New Access.Application 'added this line
    oApp.Visible = True
    oApp.OpenCurrentDatabase " C:\Documents and Settings\Lol\Desktop\Project Final 9th April\Database\French.mdb", False' removed password statement
  
    
    oApp.DoCmd.OpenReport "rptTemplate", acViewPreview ' changed this because of arguments
    oApp.DoCmd.CopyObject , "Report1a", acReport, "rptTemplate"
    Dim oReport As Access.Report
    Set oReport = oApp.Reports("Report1a")
    oReport.Print
    oApp.DoCmd.CopyObject , "MonthEnd_" & Format(Now, "mm-dd-yyyy"), acReport, "Report1"
 
End Sub

I'm now getting runtime error 7866 "cannot open database because missing or opened exclusaively by another user".

I really appreciate your help on this and would like to crack it but if it's becoming a chore just say and we can forget it.

Lol :bigyello:

Check the directory where the db is located for a .ldb file. Usually VB will retain a connection under ADO to the db
and keep it locked. If so then close vb and re-open it.

No ldb file showing. Closed Vb and re-opened satill not having it. Connection path correct and ADO is closing the connection properly because I just checked for an ldb file after running my form with the ADO in it. Curious!

Lol

Ok, if your connecting to the db using ADO and using the Acess Object Model to open the report then your connecting
to it twice, once with each technology.

The form with the ADO connection on it only runs the connection on a click event on various buttons. There's no way Access could be picking up a reference to it from an uninitiated ADO connection and see this as a link, thus denying the Access Object connection, is there?

Lol

I dont think so as long as your closing the connection and destroying the objects inside each button
click. This would ensure that the ADO is not in conflict. Or as a test you could comment out the ADO code
and see if it works then.

Hi Rob, tried taking a copy of the database to a new folder, creating new project and running the code but still get the "cannot open database etc".

Looks like a doozy mate. Not a problem and I appreciate your efforts. if you want to walk away from this one I can't fault you! if wev'e hit the end of the road could you do me one last huge favour? please could you configure the code toi sit behind an Access form and I'll just run a form with no ingress to the database for the user. When I say code I'm referring to the copying object and re-naming, saving etc.

Cheers, Lol :bigyello:

Your db is ok and not corrupted? Can you open it from a double-click?

The Access code for behind an Access Form's Button would be like so. I made some changes so it seems to
work correctly now.

VB Code:

Private Sub Test2()
 
    Dim sStamp As String
    Dim oApp As Access.Application
    Set oApp = GetObject(, "Access.Application") 'Setting oApp as a New access.applicaion was 
    'creating the 2nd instance and preventing the access to the db.
    'oApp.OpenCurrentDatabase "C:\MyDB.mdb", False, "YourPassword" 'If Report1 is not in the current db
    sStamp = "MonthEnd_" & Format(Now, "mm-dd-yyyy")
    oApp.DoCmd.CopyObject , sStamp, acReport, "Report1"
    oApp.DoCmd.OpenReport sStamp, acViewPreview, , , acWindowNormal
    Dim oReport As Access.Report
    Set oReport = oApp.Reports(sStamp)
    'oReport.Print
    oApp.DoCmd.Close acReport, sStamp, acSaveYes
    Set oReport = Nothing
    
End Sub

Hi Rob, no my db is not corrupted and works fine. tried the code and it works great thanks! By your inference then if Setting the oApp as a new Access application was creating a second connection then changing the Set to Get in the VB code should work as well? Definitely gives me something to play with!

Thanks, Lol

Thats correct. GetObject only works if Access is already running.

Later :)