[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VB6 Nested Loops

Posted on 2011-04-29
5
Medium Priority
?
492 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:KimD2
  • 2
  • 2
5 Comments
 
LVL 16

Expert Comment

by:HooKooDooKu
ID: 35494301
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

0
 

Author Comment

by:KimD2
ID: 35495263
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

0
 
LVL 1

Expert Comment

by:smartchaps
ID: 35496162
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.
0
 
LVL 16

Accepted Solution

by:
HooKooDooKu earned 2000 total points
ID: 35496234
Contents of the database souldn't cause a Type Mismatch error just running the query... but it can when you go to look at the content of the records.

However, h\the sample statement you show in the last question appears to be missing a string concatenation character and you've misplaced some quotes.  Try this


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

Open in new window

0
 

Author Closing Comment

by:KimD2
ID: 35497068
That worked beautifully.  I truly appreciate your quick assistance!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question