Open report and change its source 3

[Dustman]

I need a script that will open a report (name is passed in from a variable) and then change the recordset for that report. The filter will not work because the SQL statement is too long/complicated.

I'm sure it has something to do with

dim rpt as Report
rpt.RecordSource = strSQL

But I don't have a clue how to go about that.
-Dustin
Rom 8:28

 

[FancyPrairie]

One think you could do is to set your SQL string as a Public variable (i.e. gstrSQL). Then, in the OnOpen event of the report, set its RecordSource equal to gstrSQL. Something like this:

Private Sub Report_Open(Cancel As Integer)

Me.RecordSource = gstrSQL

End Sub

Another thing you could do is when you build your SQL string, delete the old query (assigned to the report) and create a new one that contains your SQL statement.

Here's an example:

Function CreateQuery()

    Dim dbs as DAO.Database
    Dim qdf as DAO.QueryDef
    Dim strSQL as String

    On Error GoTo ErrHandler

    strSQL = "Select ...."

    Set dbs = CurrentDb
    dbs.QueryDefs.Delete "qryForYourReport"
    Set qdf = dbs.CreateQueryDef("qryForYourReport", strSQL)

ExitProcedure:
    Exit Function

ErrHandler:
    If (Err.number = 3265) Then         'IFT, tryed to delete a query that did not exist.
        
        Resume Next

    Else

        Msgbox err.description
        Resume ExitProcedure

    End if

End Function

On side effect of this method, is that your database will tend to grow as you delete and create queries.

 

[Dustman]

Interesting.. I'll try that as a quick fix but I forsee great problems when I emplement this with my client. If only one user is in the db at a time it would be fine.. but what happens if two people do it at the same time?

Thats why I'm trying to do everything with objects.. basically using the report as a template. -Dustin
Rom 8:28

 

[SBendBuckeye]

Not sure exactly what you are trying to do but here is something to get you started. Look up the Report Object in Access help (97 is better because it is organized much better). Methods are like functions you can call and properties are attributes you can set similar to your first post.

Let's say you have a report named rptTest. In a standard module do something like the following:

Dim rpt As Report
DoCmd.OpenReport "rptTest",options, etc
set rpt = Reports("rptTest&quot 'Open reports collection
etc
etc
rpt.close
set rpt = nothing

Kick your code into single step mode. After you have set a reference to rpt, open up the view locals window and nose around.

Sorry I can't help you more. Good Luck! Ps 103

 

[Dustman]

Ok.. I'll give that a try too. I've spent the last two years in java and c++, my VB skills are rusty. I knew there was some way to set rpt = something.. I just couldn't figure out the sentax.. Thanks! -Dustin
Rom 8:28

 

[Dalain]

The Following example Code will replace the report RecordSource.

Private Sub Report_Open(Cancel As Integer)

Me.RecordSource = "SELECT AcousticLink.*, SonarDef.LastUpdate FROM AcousticLink INNER JOIN SonarDef ON AcousticLink.SensorID = SonarDef.SensorID WHERE (((AcousticLink.SensorID)= Me.[SensorID] ));"

End Sub



You could also try saving the SQL Statement in a table (Like Settings) which is kepted with the Front end database.

If this is done you can replace the above me.RecordSource = "etc...."

With Me.RecordSource = DLookup("RecSourceDefault", "Settings&quot

Just before the OpenReport command, you can define your Sql Statement. For example:

Private Sub PrintRpt_Click()
On Error GoTo Err_PrintRpt_Click

DoCmd.RunSQL "UPDATE Settings SET Settings.RecSourceDefault = 'SELECT *FROM GroupCoord WHERE (((GroupCoord.GroupCode)=""" & IDCode & ""&quot);'"

DoCmd.OpenReport "MsgDraft", acViewPreview, , "[MsgID]=" & Me![MsgID]


Exit_PrintRpt_Click:
Exit Sub

Err_PrintRpt_Click:
MsgBox Err.Description
Resume Exit_PrintRpt_Click

End Sub


Hope this helps
Pierre

 

[Dustman]

Thanks for the help everyone.. I've got it working now with a little of all the posts here. Things were a little more complicated than I originally posted. Thanks for the input.

And, Dalian, I really like the idea of storing the SQL in a table instead of global variables. Much easier for access to mess with. One solution when doing things like that when you don't have a split db is to give each user their own options table.. that way your VBA can store all kinds of variables like that. -Dustin
Rom 8:28

 

[Dalain]

Good suggestion Dustin, one of my databases currently has a hidden mdb file that has been renamed with a “.dll” extension, encrypted and password protected. This database it is kept local system in the windows dir.

This database I have one Table called UserSet (fields [UserID] and [LevelAcc]). The first field [UserID], I use to identify the user, and the [LevelAcc] field is used to determine what information the user is allowed to see. For every user a record is created. In order to retrieve the info:

Set dbs = OpenDatabase(MyHiddenFilePathFileName, False, False, ";DATABASE=" & MyHiddenFilePathFileName & ";pwd=1234***&quot
Set rst = dbs.OpenRecordset("SELECT * FROM UserSet Where UserID = """ & CurrUserID & """&quot
If Not rst.EOF Then rst.MoveFirst
With rst
UserLevelAcc = !LevelAcc
End With
rst.Close

Then with this UserLevelAcc variable I can base my filters to control what information this user is allowed to view.

The main reason for the hidden file, was if the user knows about the file or if the table is part on the main database, he/she can then do a link to see the info contained within (Get external data – Import) . This way, first the user must find the dll file and even if the file is known, the password is not!!

 

[FancyPrairie]

Storing information in tables in an unsplit database can cause some problems. The main one is that when you issue a new version of your database, the tables will be overwritten with what you have in the new database you are about to distribute. So, now you have to make a copy of their tables prior to giving the user a new version and copying the tables back to the user's copy of the new database. Quite a hastle.

I have a user preference table (library database) that all of my databases link to. It contains, among other things, the name of the database, user name, user's phone number, office location, and which form they want displayed at startup. At startup, my program checks to see if the user has been registered, if not, a form is displayed that asks the user to fill in the required info (some of which I already know (i.e. database name, user name)).

I have not had problems building the RecordSource of a report with a global variable. I always create a form from which the user can select which report he/she wants printed, a dual list box to indicate which fields they want the report ordered by, several list boxes to narrow the filter down, as well as text boxes for date ranges, etc. I then build the SQL statement based on the criteria the user selected. I do this by "unbuilding" the SQL statement assigned to the report and "rebuilding" the SQL statement based on the criteria the user selected. In most cases the SQL statement does not have to be rebuilt. A simple Where clause and/or Order By clause needs to be added. Or the items added to the OpenReport method. However, there are times, based on what the user selected, where I have to add, programmatically, join clauses to the original SQL statement.