Ihave generated a report which each row could have different backcolors and/or visible for each field. How can I contol the loops that reads the data into the report. I can make this work with an InputBox. That ia very unsatifactory.

Hi,

   I don't understand what you are trying to do. Do you want to set the backcolor and visible property of text boxes based on the value or another value?



mmcdonal

The report that I have generated retrives data from a table.
each row can have a seperate color and each field can be aseperate color as well.
I solved that problem by layering labels.
However when the Do...Until loops run only the EOF data is posted to the report.
By using an InputBox I can control the process. I believe Access sees the information as seperate reports.
I am look for a way to us loops to accomplish the same thing as an InputBox.
Pabotonto

How are you trying to capture data with a Do Loop?

If you want to concatenate the data, on each loop, catch the data and put it into a variable like this:

'--------
Do...
sData = (your loop value for this iteration)

sVariable = sVariable & ", " & sData
rs.MoveNext
Loop

Me.myTextBox = sVariable
'--------

This will accumulate data from your loops, seperated by a comma. Is this the kind if thing you want?

HTH

mmcdonal

HTM:
 Yes; That controls the loop to run only the number of times as the ammount of records(I realize that you knew that).
However, I have two tables that I am doing a data compare to change the colors on the report. How do I retrieve this concatenated data to do the compare. I have to compare ,for example, field 1 in tbl 1 and field 1 in tbl 2 and if different color red. The next record may have the data as equal in both tbls therefore no color change.

You want to do a nested loop, something like this:

'Connection information
'Create rs1 on first table
'Create rs2 on second table

rs1.MoveFirst
Do Until rs1.EOF
   dDate1 = rs1.Fields(x)
        rs2.MoveFirst
          Do Until rs2.EOF
             dDate2 = rs2.Fields(n)
                 If dDate1 <> dDate2 Then
                    Me.myTextBox.BackColor = vbRed
                 Else
                    Me.myTextBox.BackColor = vbWhite
                 End If
          rs2.MoveNext
          Loop
rs1.MoveNext
Loop

I think this will work. I can't test it right now, but I use this technique a lot.

HTH

mmcdonal

Actually, I don't think this will work since you will get vbRed/vbWhite in many loops, and only the last one will be spit out.

Can you give me more info on the structure of the tables and the query behind the report? There is probably a way to do this by coding conditional formating, like in the report's Detail section On Format event:

If Me.myTextBox1 <> Me.myTextBox2 Then
   Me.myTextBox1.BackColor = vbRed
   Else
   Me.myTextBox1.BackColor = vbWhite
End If

I need more info, but it sounds doable.


mmcdonal

I am running the code from the the detail section on print.
The report is based on a standard select query with sorting on two date fields. Which gathers the data from tbl's ist and ist1
The following is the code as written now:
   ist.MoveFirst
              Do Until ist.EOF
            sdate1 = ist![INCOStart]
            svariable = svariable & "," & sdate1
             Debug.Print svaraible
             ist1.MoveFirst
                Do Until ist1.EOF
            sdate2 = ist1![INCOStart]
            svariable1 = savariable1 & "," & sdate2
             Debug.Print svariable1
            ist1.MoveNext
                Loop
            ist.MoveNext
                Loop


            If svariable = svariable1 Then
                lblColorControl.BackColor = cpu
            Else
                lblColorControl.BackColor = crd
            End If

Additionally:
The report only reports background data.
txtBoxes or lbl's.
As I stated in my first report an InputBox works. My reasoning is that the InputBox breaks the Print functions and increments the print count. So the report thinks that each time it is new data.
This is just conjecture on my part..
thanks again
Pabotonto