asked on

VB6 Nested Loops

Hello Experts,

I am working with a very large, very old VB6/ Access 2003 program.

The first table has a list of reports.

The second table handles the report security. There should be one line for each report for each employee.

I need to loop through the second table checking to see that each employee has a line for each report. If there is not a report line then it would be added to the recordset.

Could someone help me out with basic code? My ideas are below. I truly appreciate your time! Kim

Dim dynReportList As Recordset
   Dim dynActivePers As Recordset
   Dim dynReportSec  As Recordset
   Dim liPers As Long
   Dim liRep As Long
   
   Dim i As Integer
   Dim n As Integer
 
 For n = 0 To i
   Set dynActivePers = dbaTest.OpenRecordset("SELECT Personnel.IDNmbr from Personnel WHERE Status = 'Active' order by Personnel.IDNmbr", dbOpenSnapshot)
        liPers = dynActivePers("IDNmbr")
   
   Set dynReportList = dbaTest.OpenRecordset("Select ReportList.IDNmbr from ReportList order by IDNmbr", dbOpenSnapshot)
        liRep = dynReportList("IDNmbr")
          
   i = dynActivePers.RecordCount
      
        Set dynReportSec = dbaTest.OpenRecordset("SELECT * from ReportSecurity where ReportListID = " & liRep And PersonnelID = " & liPers", dbOpenDynaset)
        
        If dynReportSec.EOF = True And dynReportSec.BOF = True Then
                    dynReportSec.AddNew
                        dynReportSec("PersonnelID") = liPers
                        dynReportSec("ReportListID") = liRep
                        dynReportSec("AddedBy") = gsUserID
                        dynReportSec("DateAdded") = Now()
                    dynReportSec.Update
                End If
          Next n

Open in new window

HooKooDooKu

You don't need a 'For' loop. Just loop through the Report List within a loop of the Employee List. For each Report/Employee combo, query the Security table. If a matching record is not found, add it.

The code generates a lot of queries. It is possible to get the entire Report Security Table at the beginning and try to step through it as you iterate the other two tables. But that logic requires a much more complex set of if statements to keep everything in sync and avoid moving any record past EOF.

'Get the list of Reports
   Set dynReportList = dbaTest.OpenRecordset("Select ReportList.IDNmbr from ReportList order by IDNmbr", dbOpenSnapshot)

   'Get the list of Employees
   Set dynActivePers = dbaTest.OpenRecordset("SELECT Personnel.IDNmbr from Personnel WHERE Status = 'Active' order by Personnel.IDNmbr", dbOpenSnapshot) 

   'Iterate the Employees 
   Do Until dynActivePers.EOF()
      liPers = dynActivePers("IDNmbr")
      
      'Iterate the Reports
      dynReportList.MoveFirst()      
      Do Until dynReportList.EOF()

         liRep = dynReportList("IDNmbr")

         Set dynReportSec = dbaTest.OpenRecordset("SELECT * from ReportSecurity where ReportListID = " & liRep And PersonnelID = " & liPers", dbOpenDynaset)
         if dynReportSec.EOF() then
            dynReportSec.AddNew
               dynReportSec("PersonnelID") = liPers
               dynReportSec("ReportListID") = liRep
               dynReportSec("AddedBy") = gsUserID
               dynReportSec("DateAdded") = Now()
            dynReportSec.Update
         End If
         
         dynReportList.MoveNext()
      Loop

      dynActivePers.MoveNext()
   Loop

Open in new window

KimD2

ASKER

Thank you so much. Would it be possible for you to help me with the syntax on the "Set dynReportSec" line as well? The statement works fine using either the liRep or liPers condition. When I use both (as listed below) I receive RTE 13- Type Mismatch. Both of these fields are defined as long in code as well as in the database.

Set dynReportSec = dbaTest.OpenRecordset("SELECT * from ReportSecurity where ReportListID = " & liRep And PersonnelID = " & liPers", dbOpenDynaset)

Open in new window

smartchaps

If for a particular record either of liRep or liPers is Blank (Nothing), it will give this error. So check for an empty data in a record.

ASKER CERTIFIED SOLUTION

HooKooDooKu

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

KimD2

ASKER

That worked beautifully. I truly appreciate your quick assistance!