|
Subject:
|
controling loops
|
|
Posted By:
|
pabo
|
Post Date:
|
4/10/2006 4:09:18 PM
|
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.
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
4/11/2006 11:18:58 AM
|
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
|
|
Reply By:
|
pabo
|
Reply Date:
|
4/12/2006 10:58:25 AM
|
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
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
4/12/2006 11:04:21 AM
|
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
|
|
Reply By:
|
pabo
|
Reply Date:
|
4/12/2006 4:47:01 PM
|
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.
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
4/13/2006 6:47:23 AM
|
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
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
4/13/2006 6:51:25 AM
|
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
|
|
Reply By:
|
pabo
|
Reply Date:
|
4/13/2006 3:35:45 PM
|
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
|
|
Reply By:
|
pabo
|
Reply Date:
|
4/13/2006 5:12:15 PM
|
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
|
