Running multiply queries - wondering where to output result 1

[teqtaq]

I am running compliance querie - 12 different queries.
I want to put them in a Function and run one by one - which I did.

Now I am thinking - even most of them come up with no result (which is good) - some of them sometimes come with result and I want to autamate it but IF result is <>Null - I want to see it.

What would be the approach to have it done?
I am luck of logic here.
I could create a table and have it write intot he table but format is so different for each. I am stuck, please, push me into right direction.
Thanks

 

[Remou]

Have you considered writing out to a text file? The FileSystemObject and a textstream should suit.

http://lessthandot.com

 

[teqtaq]

Could be a good idea. I will test it now. Thanks

 

[teqtaq]

I found code

Dim fs, f
Set fs = Server.CreateObject("Scripting.FileSystemObject")
Set f = fs.CreateTextFile("c:\test.txt", True)
f.WriteLine ("Hello World!")
f.Close
Set f = Nothing
Set fs = Nothing

However it is not clear to me - where should I put the code?
I have
docmd.openquery "1"
docmd.openquery "2"
docmd.openquery "3"
docmd.openquery "4" etc

and if one comes up with result - code should start but I am not sure how to do this.

Thanks

 

[Remou]

Instead of opening the queries, use them for recordsets.

Some notes.

    Const ForAppending = 8
    Dim strPath As String
    Dim rs As DAO.Recordset
    
    strPath = CurrentProject.PATH
    
    Set fs = CreateObject("Scripting.FileSystemObject")
    If fs.FileExists(strPath & "\ErrorLog.txt") = True Then
        Set a = fs.OpenTextFile(strPath & "\ErrorLog.txt", ForAppending)
    Else
        Set a = fs.CreateTextFile(strPath & "\ErrorLog.txt")
    End If
    a.WriteLine Date
    Set rs = CurrentDb.OpenRecordset("Query10")
    
    If Not rs.EOF Then
        Do While Not rs.EOF
            For i = 0 To rs.Fields.Count - 1
                aline = aline & " " & rs(i)
            Next
            
            a.WriteLine aline
            rs.MoveNext
            aline = ""
        Loop
    End If
    a.Close

http://lessthandot.com

 

[teqtaq]

It is complex but thanks so much!
I will try and hope it'll work for me.
THANKS

 

[teqtaq]

How is that suppose to be done:"Instead of opening the queries, use them for recordsets."

Should I be pasting this code 12 times and changing "Query10" to name of each query?

 

[PHV]

...
a.WriteLine Date
For Each strQryName In Array("query1", ..., "query12")
Set rs = CurrentDb.OpenRecordset(strQryName)
Do While Not rs.EOF
For i = 0 To rs.Fields.Count - 1
aline = aline & " " & rs(i)
Next
a.WriteLine aline
rs.MoveNext
aline = ""
Loop
rs.Close
Next
a.Close

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SeeThru]

Consider also adding each query into a UNION (assuming the same columns are possible). You can cut down on saved queries by adding the full SQL for each, rather than referencing the individual 12 queries, but it makes debugging really difficult.

You can then view the results of the union query in a form or report.

SeeThru
Synergy Connections Ltd - Telemarketing Services