Loops and Assignments not working

[bhughes2187]

Hey all, Me again!

I have created this subroutine, and maybe I just don't understand loops well enough, but this is what I came up with and it is not working.

To summarize, I run an sql statement that retrieves some values and places them into an array.

i.e.
Record(0) = A
Record(1) = C
Record(2) = D

Then what I need to do, is assign values to my unbound text boxes on the report named rptTitle1, rptTitle2, rptTitle3, rptPage1, rptPage2, rptPage3 by looping through that array, pulling data out of a table based on each recordset, and assign for example rptTitle1 = "Holdings", rptPage1 = 1, rptTitle2 = "NetInvestment" , rptPage2 = 4.. I think, i hope, you can see where I am going with this. But my code, loops to many times, and the text boxes are not being assigned their values. My sub routine follows:

Option Compare Database
Option Explicit
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim db As Database
Dim rept As DAO.Recordset
Dim Rcount As DAO.Recordset
Dim T As DAO.Recordset
Dim Report() As Variant
Dim sql1 As String
Dim sqlA As String
Dim sqlB As String
Dim i As Integer
Dim l As Integer
Dim R As String
Dim Z As String
Dim rptTitle As String
Dim rptPage As String
Dim pg As Integer
Set db = CurrentDb
pg = 1

' Get list of report codes and place into an array
sql1 = "Select rpt FROM scarlet WHERE rpt <> '~' GROUP BY rpt"
Set rept = CurrentDb.OpenRecordset(sql1)
rept.MoveLast
rept.MoveFirst

Report = rept.GetRows(rept.RecordCount)

' Loop through array of reports from table scarlet
' select recordset where array(0, i) = report title

For i = 0 To rept.RecordCount - 1
 For l = 1 To rept.RecordCount
  rptPage = "Me.rptPage" & l & "=" & pg
  MsgBox "The Number of Records = " & rept.RecordCount
  rptTitle = "Me.rptTitle" & l
  R = Report(0, i)
  sqlB = "select rpt_title from titles where code = " & "'" & R & "'"
  Set T = CurrentDb.OpenRecordset(sqlB)
  rptTitle = T.GetRows(T.RecordCount)
  sqlA = "select count(*) from scarlet where attribute = 'R' and rpt = " & "'" & R & "'"
  Set Rcount = db.OpenRecordset(sqlA)
  pg = pg + Rcount.Fields(0) + 1
  Next l
 Next i
End Sub

Any help is GREATLY appreciated. I haven't touched anything related to VB in two years so it is hard for me to remember what to do...

[ADezii]

Hey all, Me again!

I have created this subroutine, and maybe I just don't understand loops well enough, but this is what I came up with and it is not working.

To summarize, I run an sql statement that retrieves some values and places them into an array.

i.e.
Record(0) = A
Record(1) = C
Record(2) = D

Then what I need to do, is assign values to my unbound text boxes on the report named rptTitle1, rptTitle2, rptTitle3, rptPage1, rptPage2, rptPage3 by looping through that array, pulling data out of a table based on each recordset, and assign for example rptTitle1 = "Holdings", rptPage1 = 1, rptTitle2 = "NetInvestment" , rptPage2 = 4.. I think, i hope, you can see where I am going with this. But my code, loops to many times, and the text boxes are not being assigned their values. My sub routine follows:

Option Compare Database
Option Explicit
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim db As Database
Dim rept As DAO.Recordset
Dim Rcount As DAO.Recordset
Dim T As DAO.Recordset
Dim Report() As Variant
Dim sql1 As String
Dim sqlA As String
Dim sqlB As String
Dim i As Integer
Dim l As Integer
Dim R As String
Dim Z As String
Dim rptTitle As String
Dim rptPage As String
Dim pg As Integer
Set db = CurrentDb
pg = 1

' Get list of report codes and place into an array
sql1 = "Select rpt FROM scarlet WHERE rpt <> '~' GROUP BY rpt"
Set rept = CurrentDb.OpenRecordset(sql1)
rept.MoveLast
rept.MoveFirst

Report = rept.GetRows(rept.RecordCount)

' Loop through array of reports from table scarlet
' select recordset where array(0, i) = report title

For i = 0 To rept.RecordCount - 1
 For l = 1 To rept.RecordCount
  rptPage = "Me.rptPage" & l & "=" & pg
  MsgBox "The Number of Records = " & rept.RecordCount
  rptTitle = "Me.rptTitle" & l
  R = Report(0, i)
  sqlB = "select rpt_title from titles where code = " & "'" & R & "'"
  Set T = CurrentDb.OpenRecordset(sqlB)
  rptTitle = T.GetRows(T.RecordCount)
  sqlA = "select count(*) from scarlet where attribute = 'R' and rpt = " & "'" & R & "'"
  Set Rcount = db.OpenRecordset(sqlA)
  pg = pg + Rcount.Fields(0) + 1
  Next l
 Next i
End Sub

Any help is GREATLY appreciated. I haven't touched anything related to VB in two years so it is hard for me to remember what to do...

The typical Method of data retrieval from the 2-Dimensional Array returned by GetRows() is as follows. I'm sorry that I did not have too much time to look at the code, but see if this helps and I'll return later:
[CODE=vb]
Dim intCounter As Integer

For intCounter = 0 To UBound(Report, 2)
Debug.Print Report(0, intCounter)
Next[/CODE]

[bhughes2187]

Yeah, that doesn't seem to work.

There are 12 unbound text boxes on the page. 6 of them for possible report titles (pulled from getrows ). The other 6 are for page numbers assigned by variable pg. I did realize that I had no need for two for loops as this is what was causing my issues with looping too many times. Now that I have that figured out, all I need is to populate the text boxes appropriately. Also, when I get down to rptTitle = T.getrows(T.rec ordcount), i get a type mismatch. No doubt I am trying to do it wrong.

[ADezii]

Yeah, that doesn't seem to work.

There are 12 unbound text boxes on the page. 6 of them for possible report titles (pulled from getrows ). The other 6 are for page numbers assigned by variable pg. I did realize that I had no need for two for loops as this is what was causing my issues with looping too many times. Now that I have that figured out, all I need is to populate the text boxes appropriately. Also, when I get down to rptTitle = T.getrows(T.rec ordcount), i get a type mismatch. No doubt I am trying to do it wrong.

Also, when I get down to rptTitle = T.getrows(T.rec ordcount), i get a type mismatch. No doubt I am trying to do it wrong.

1. You are trying to assign the return of the GetRows() Method to a String Variable (as Declared in Line #13 - rptTitle). The return from GetRows() can only be a Variable declared as Variant, in order that it may hold the 2-Dimensional Array.
2. I do not think your SQL Statement (sql1) will properly Filter out any Temporary Report Objects (~Temp Report), and there is no need for a GROUP BY Clause, try:
   [CODE=vb]
   sql1 = "SELECT DISTINCT [rpt] FROM scarlet WHERE Left$([rpt], 1) <> '~';"
   Set rept = CurrentDb.OpenR ecordset(sql1)[/CODE]
3. You should always test for an Empty Recordset before starting any processing:
   [CODE=vb]If rept.BOF And rept.EOF Then Exit Sub 'No Records exist
   [/CODE]
4. To be perfectly honest with you, I am having a little problem following the overall logic and this is one of those problems, at least in my case, where I would need the DB in front of me to pinpoint the problem. Would you be willing to send me the Database, or a subset of it, to me as an E-Mail Attachment and then I'll be happy to have a look at it. If this is acceptable to you, let me know and I'll send you my E-Mail Address in a Private Message. If not, then good luck with your Project.

[bhughes2187]

Let me try and simplify.

I have an array ' rptTitle ' and a variable ' pg '

I want to place the values of these into a table that I can query off of and fill two text boxes.

Array1: (rptTitle)

0: Portfolio Holdings
1: Gains
2: Net Investment

It is getting these values into a table I am having issues with.

[ADezii]

Let me try and simplify.

I have two array's ( rptTitle and rptPage).

I want to place the values of these array's into a table that I can query off of and fill two text boxes.

Array1: (rptTitle)

0: Portfolio Holdings
1: Gains
2: Net Investment


Array2:(rptPage)

0: 1
1: 3
2: 6

It is getting these values into a table I am having issues with.

I apologize for the confusion, but I am totally in the dark on this one. Both rptTitle and rptPage are Declared as Strings, not as Variants, and not as Arrays. As such they can never contain valid Array Data, the rpt Prefix usually indicates they are Report Objects, but that obviously is not the case. Are you telling me that they are both 1-Dimensional Arrays (rptTitle - String), (rptPage - Numeric), and that you wish to populate a Table with their values? If such is the case, what is the Table Name, what are its Field Names/Data Types, and in what sequence is the data from each Array to populate the Table? How are the Lower and Upper Bounds of these Arrays determined? Kindly post a few Rows of what data in the Table would like, that should clarify matters. Thanks, and I'll be checking back.

[bhughes2187]

I edited my last post... forget rptPage...

I changed rptTitle to a variant on my code and made it look rptTitle() as variant.

the table name is tblContents. Field names are Title and Page



table:

title page
Portfolio Headings 1
Gains 3
Net Investment 6


Code sofar looks like this, (probably have vars not necessary)

Function table_contents()
Dim db As Database
Dim rept As DAO.Recordset
Dim Rcount As DAO.Recordset
Dim T As DAO.Recordset
Dim Report() As Variant
Dim sql1 As String
Dim sqlA As String
Dim sqlB As String
Dim sqlC As String
Dim b As Integer
Dim l As Integer
Dim R As String
Dim Z As String
Dim rptTitle() As Variant
Dim pg As Integer
Dim test As Integer
Set db = CurrentDb
pg = 1

' Get list of report codes and place into an array
sql1 = "Select rpt FROM scarlet WHERE rpt <> '~' GROUP BY rpt"
Set rept = CurrentDb.OpenRecordset(sql1)
rept.MoveLast
rept.MoveFirst

Report = rept.GetRows(rept.RecordCount)

' Loop through array of reports from table scarlet
' select recordset where array(0, i) = report title
l = 1

For b = 0 To rept.RecordCount - 1
  
  'MsgBox "The Number of Records = " & rept.RecordCount
 
  R = Report(0, b)
  sqlB = "select rpt_title from titles where code = " & "'" & R & "'"
  Set T = CurrentDb.OpenRecordset(sqlB)
  rptTitle = T.GetRows(T.RecordCount)
  sqlA = "select count(*) from scarlet where attribute = 'R' or attribute = 'E' and rpt = " & "'" & R & "'"
  Set Rcount = db.OpenRecordset(sqlA)
  'sqlC = "insert into tblcontents(title, page) values()"
  MsgBox "Page Number = " & pg
  pg = pg + Rcount.Fields(0) + 1
  l = l + 1
  Next b
End Function

[bhughes2187]

I edited my last post... forget rptPage...

I changed rptTitle to a variant on my code and made it look rptTitle() as variant.

the table name is tblContents. Field names are Title and Page



table:

title page
Portfolio Headings 1
Gains 3
Net Investment 6


Code sofar looks like this, (probably have vars not necessary)

Function table_contents()
Dim db As Database
Dim rept As DAO.Recordset
Dim Rcount As DAO.Recordset
Dim T As DAO.Recordset
Dim Report() As Variant
Dim sql1 As String
Dim sqlA As String
Dim sqlB As String
Dim sqlC As String
Dim b As Integer
Dim l As Integer
Dim R As String
Dim Z As String
Dim rptTitle() As Variant
Dim pg As Integer
Dim test As Integer
Set db = CurrentDb
pg = 1

' Get list of report codes and place into an array
sql1 = "Select rpt FROM scarlet WHERE rpt <> '~' GROUP BY rpt"
Set rept = CurrentDb.OpenRecordset(sql1)
rept.MoveLast
rept.MoveFirst

Report = rept.GetRows(rept.RecordCount)

' Loop through array of reports from table scarlet
' select recordset where array(0, i) = report title
l = 1

For b = 0 To rept.RecordCount - 1
  
  'MsgBox "The Number of Records = " & rept.RecordCount
 
  R = Report(0, b)
  sqlB = "select rpt_title from titles where code = " & "'" & R & "'"
  Set T = CurrentDb.OpenRecordset(sqlB)
  rptTitle = T.GetRows(T.RecordCount)
  sqlA = "select count(*) from scarlet where attribute = 'R' or attribute = 'E' and rpt = " & "'" & R & "'"
  Set Rcount = db.OpenRecordset(sqlA)
  'sqlC = "insert into tblcontents(title, page) values()"
  MsgBox "Page Number = " & pg
  pg = pg + Rcount.Fields(0) + 1
  l = l + 1
  Next b
End Function

basically what I am trying to do is based on the client, the reports included will be different. What I want is this:







Portfolio Holdings page 1
Gains page 3
Investment page 4

[ADezii]

basically what I am trying to do is based on the client, the reports included will be different. What I want is this:







Portfolio Holdings page 1
Gains page 3
Investment page 4

Still a little hazy, but I posted some pseudo code that may/may not be of help to you:
[CODE=vb]
Dim db As DAO.Database
Dim rept As DAO.Recordset
Dim T As DAO.Recordset
Dim Rcount As DAO.Recordset
Dim rstContents As DAO.Recordset
Dim sql1 As String
Dim sqlA As String
Dim sqlB As String
Dim pg As Integer
Dim varReport As Variant
Dim rptTitle As Variant
Dim l As Integer
Dim intCounter As Integer
Dim b As Integer
Dim R As String

sql1 = "Select Distinct rpt From scarlet Where Left$(rpt, 1) <> '~';"

Set db = CurrentDb()
pg = 1

Set rstContents = db.OpenRecordse t("tblContents" , dbOpenDynaset)
Set rept = db.OpenRecordse t(sql1)
rept.MoveLast
rept.MoveFirst

varReport = rept.GetRows(re pt.RecordCount)

l = 1

For b = 0 To UBound(varRepor t, 2)
R = varReport(0, b)
sqlB = "Select [rpt_title] From titles Where

 = " & "'" & R & "'"
  Set T = CurrentDb.OpenRecordset(sqlB)
  rptTitle = T.GetRows(T.RecordCount)
  sqlA = "Select Count(*) From scarlet As [Total_Count] Where [attribute] = 'R' Or [attribute] = 'E'" & _
         " And [rpt] = '" & R & "'"
  Set Rcount = db.OpenRecordset(sqlA)
    For intCounter = 1 To UBound(rptTitle, 2)
      With rstContents
        .AddNew
          ![Title] = rptTitle(0, intCounter)
          ![Page] = Rcount![Total_Count]
        .Update
      End With
    Next
Next

rept.Close
Set rept = Nothing
rstContents.Close
Set rstContents = Nothing
'Close other Recordsets

[bhughes2187]

I will give that a try.

I really do appreciate the help.. It has been almost 2 years since I last did anything with VB. In my current profession, I do not do enough with one particular language to retain very much. I will code for about 2 weeks and then do nothing for a month or so.. Basically I have to relearn everytime.

[bhughes2187]

Well your code helps some, I am getting the info I need, but for some reason I cannot display the information in the unbound text boxes..


Which is odd, because I have a similar thing going on all my reports for doing page numbers where on the on format of the page footer section I set an unbound text box to the value of a variable... (me![page] = lngpagenumber)

So not sure why its not working for me.. Perhaps I need to do this function on the on open event, and then on the on format event, call the variables for the unbounds...

[NeoPa]

I think the way you need to look at this is to have a recordset inside your code.

The loop should be controlled with a "For x = 1 To 6" construct, but with an escape line which exits the For loop (Exit For) when .EOF is detected.

You process through the recordset setting two controls on each pass (iteration). An array can be used for this, but involves setting up the array before processing through the loop. Alternatively, a more complicated piece of code would be required within the loop to set the correct controls for that particular iteration through the loop.

You should also handle resetting any possibly unused controls outside of the loop, in case there are fewer than 6 records to process.

[NeoPa]

When you say it is not working, can you be a little more precise?

Does the project crash, or does the value not match what you expect?

[bhughes2187]

When you say it is not working, can you be a little more precise?

Does the project crash, or does the value not match what you expect?

The project does not crash, if I use a msgbox to display the values, ( as it loops through, I have it display the results, ie. msgbox = R or msgbox = rptTitle(0,0) )
the values are getting there, they are not displaying on the report. I get a blank page.

[NeoPa]

I checked the Help for Format Event and I can see no reason why this might happen.

Can you post just the relevant code in here (the event procedure should do) to check over. If you have any debug (MsgBox() etc) lines then include these and indicate as clearly as you can the output you received.

PS. Have you had a chance to review my earlier post (#12) yet and might that not answer your original question?