[AccessD] Filtering an SQL based OpenRecordset

Thanks Gustav,
Since these recordsets are only used for printing reports should I use the
constant dbOpenSnapshot?

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Wednesday, September 30, 2009 3:57 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Filtering an SQL based OpenRecordset

Hi John

Try with:

Set rs2 = db.OpenRecordset("SELECT * FROM AddressData WHERE TrayNumber = " &
lngTrayNum & " ORDER BY SortPosition")

/gustav


>>> john at winhaven.net 30-09-2009 22:36 >>>
This doesn't work:

Public Sub PrintAllReports()
    'Loops through AddressData table and prints all customer reports
    
    Dim db As Database
    Dim rs1 As Recordset
    Dim rs2 As Recordset
    Dim lngTrayNum As Long
    Dim lngAcctNum As Long
    
    Set db = CurrentDb
    lngTrayNum = 1
    lngAcctNum = 0
    
    Set rs1 = db.OpenRecordset("SELECT * FROM AddressData ORDER BY
TrayNumber")
    Do While Not rs1.EOF
        lngTrayNum = rs1!TrayNumber.Value
        Set rs2 = db.OpenRecordset("SELECT * FROM AddressData ORDER BY
SortPosition WHERE TrayNumber = " & lngTrayNum)
        Do While Not rs2.EOF
            lngAcctNum = rs2!Cust.Value
            'DoCmd.OpenReport "rpt1", acViewNormal, , "Cust = " & lngAcctNum
            'DoCmd.OpenReport "rpt2", acViewNormal, , "Cust = " & lngAcctNum
            DoCmd.OpenReport "rpt3", acViewNormal, , "Cust = " & lngAcctNum
            rs2.MoveNext
        Loop
        rs2.Close
        rs1.MoveNext
    Loop
    rs1.Close

How do I filter an SQL based recordset?

John B.


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com