Select Querying using VBA

[AT76]

Hi all,

Need some advice as to the best method to perform a long intrinsic access query through vba. The access query I'm refering to was originally created using access. This query feeds a report. My task is to split up this report into sub reports.

What I'm thinking of doing is running the same select query through vba but not return all records as the original but instead do some filtering. I'm doing this via a recordset rs. Then I generate the report from this new recordset.


Here's what I have:

    Dim sqlstring As String
    Dim dbCurrent As Database
    Dim rs As DAO.Recordset
    
    Set dbCurrent = CurrentDb()

    sqlstring = "........  'This is the long query!!!
                           'My idea is to pass a value to the field I will be doing the filter on.  Do a For Loop for all values.

    Set rs = dbCurrent.OpenRecordset(sqlstring)

    '***Output  the report here!!!

    rs.Close
    Set rs = Nothing
    Set dbCurrent = Nothing

Is this approach good? Can someone recommend a better way to do this? I have not finished doing the above. I'm struggling with the Select Query string because it is so long. That is why I decided to post this. Is there a way I can utilize the Access query that it is already created??

Thanks in advance and Happy New Year!

 

[AlexCuse]

I think I may have a better idea for you. I can track down code if you need it, but this is how I have approached this very issue in the past.

Build report, using SQL Statement (or a saved query) as RecordSource.

Within Code (when running the report) you can do the following:

a. Build a SQL String (or saved query name) based on the criteria for what you need in the report.

b. Set Warnings OFF

c. Open Report in Design Mode

d. Set Report's RecordSource property to your new SQL String (or saved query name).

e. Set Warnings ON

Then print your report.

I prefer to use the SQL String so as not to save too many queries in my DB's.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom