My query has pairs of fields form 2 tables that I want to compare.<br>I do a recordset clone in my Open event for the Form, then iterate through the fields checking for a change in pairs of fields.<br><br>Dim comp1 as varient, comp2 as varient<br>For indx = 0 to 16 <br> comp1 = RSMT.Fields(indx).Value<br> comp2 = RSMT.Fields(indx + 17).Value<br>etc.........<br><br>I want to set the corresponding field on the Form to a color when the pairs are not equal. <br>No problem in checking the pairs, but how do I set the field on the Form. I know the relative location of the fields through the index, but need the syntax. What is the way to iterate through fields on the Form ie. the Me.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Loop through text fields by index not name 1
- Thread starter cmmrfrds
- Start date
- Thread starter
- #2
I can use this syntax to set the column but it stays set for all the rows. <br>ctl as controls<br>set ctl = me.Controls<br><br> ctl.Item(indx + 4).BackColor = 4227200<br> ctl.Item(indx + 20).BackColor = 16711680<br><br>Is it possible to set the color attribute on a row by row basis or does the attribute setting hold for all rows?<br>
jimmythegeek
Programmer
Question 1) <br>Could you give us an example of the field names both in your table, and on your form, and then how you are comparing them. This will help for a sufficient response.<br><br>Question 2)<br>You can set the color row by row if you use some decision structures inside your loop:<br><br>For example:<br><br>==========<br>For indx = 0 to 16 <br> If ThisIsTrue Then<br> ctl.Item(indx).BackColor = 4227200<br> Else<br> ctl.Item(indx).BackColor = 16711680<br> End If<br>Next indx<br>==========<br><br><b>OR</b><br><br>==========<br>For indx = 0 to 16 <br> Select Case ????<br> Case ???<br> ctl.Item(indx).BackColor = 4227200<br> Case ???<br> ctl.Item(indx).BackColor = vbRed<br> Case ???<br> ctl.Item(indx).BackColor = vbWhite<br> End Select<br>Next indx<br>==========<br><br><br><br> <p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href= Application Development
- Thread starter
- #4
Here is my code, which is a little lengthy. I have a different index setting for the form than the recordset since there are additional labels on the form. If I don't reset the color each time then once it is set it stays set for all the rows. If I reset the color then the reset color stays for all the rows. So, I was going to try a different twist and add a variable to my recordset which I would add as the first field in the row and flag if any columns had differnces but it won't let me update the variable.<br><br> Dim mKey As Integer, RSMT As DAO.Recordset<br> Dim indx As Integer, indx2 As Integer<br> Dim comp1 As Variant, comp2 As Variant<br> Dim ctl As Controls<br> indx2 = 0<br> Set RSMT = Me.RecordsetClone<br> <br> RSMT.MoveFirst<br> Set ctl = Me.Controls ' 3 label fields so starts at 3<br> <br> For indx2 = 0 To (RSMT.RecordCount - 1)<br> <br> For indx = 0 To 16 'Match first 17 to second 17 fields<br> ctl.Item(indx + 4).BackColor = -2147483643 '---- initialize colors for the row<br> ctl.Item(indx + 21).BackColor = -2147483643<br> <br> comp1 = RSMT.Fields(indx).Value<br> comp2 = RSMT.Fields(indx + 17).Value<br> If (Nz(comp1, 0)) <> (Nz(comp2, 0)) Then<br> ' -- Value is different then set color ---------<br> '''' Me.Bookmark = RSMT.Bookmark<br> ctl.Item(indx + 4).BackColor = 4227200<br> ctl.Item(indx + 20).BackColor = 16711680<br> End If<br> Next '-- loop thru the fields<br> <br> RSMT.MoveNext<br> Next '-- loop thru records<br> <br> RSMT.Close<br>
jimmythegeek
Programmer
Before I even look at the code, <br><br>Is this a continuous or datasheet form? If it is, you will <b><font color=red>not</font></b> be able to change properties for individual rows. If you change any property (backcolor, Bold, forecolor, etc.) it changes for EVERY row. This is the case whether you do it in design view, or programmatically. Let's say a form opens with a certain field backcolor of white. You then change the backcolor to red in code. That field's backcolor changes to red for <b>ALL</b> rows. If you changed it back to white, <b>ALL</b> rows would change back to white.<br><br>If this is not the case, then we can look at what you are trying to do. <p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href= Application Development
-
1
- #6
- Thread starter
- #7
It is continuous and I discovered as you pointed out that the property could not be changed on a row by row basis. I simply wanted to highlight the differences when retreiving the data. So, for example, if 20 records changed out of 3000 then they would be easy to spot when scrolling through the output and since there are quite a number of fields to compare, I didn't want to do the compare in the query or referencing each unique name. I solved how to easily detect the differences in a code loop but I am not sure how to highlight these differences and display on the continuous form. <br>I tried another approach of putting a calculated field in my query which was initialized to blank and then filling it with a flag in the code loop, to finally display this field in front of the row. Access won't let me do this since the field is not updateable. Maybe there is no easy way to do this in Access. <br><br>Thank you,<br>Jerry
- Thread starter
- #8
Ok, conditional format available in Access 2000 for text boxes and combo boxes, and in every view of form (single, continuous and datasheet).<br>Anyway. select the control you want to change its properties then from the format menu choose "Conditional Formatting", then you'll be brought up with a dialog box to choose your conditional statements and the colors you wanna change.<br>If you'd like rather to do it by VBA, you could use the new property of (text box and combo box)which called "FormatCondition". i didnt try this before but i read about it and i know it works man, so go ahead.<br><br>Hope this would help YOU... <p>Mohamed Aly<br><a href=mailto:samara_79@hotmail.com>samara_79@hotmail.com</a><br><a href= > </a><br>
jimmythegeek
Programmer
I have never used what Samara has talked about with A2K (I tend to stay away from 2000 unless it is required). So if it works, great, if it does not work, then let us know, there is another way, but try Samara's way first. <p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href= Application Development
- Thread starter
- #11
- Thread starter
- #12
I am happy to say that it worked perfectly! There were a few false starts until I got the syntax down. Nulls caused me some consternation for a while, but it now works with and without nulls. <br><br>The syntax is: Nz([Table852ZANew_QC],0) 'this is an example of one field<br><br>I used "field value" and "Not Equal" to Nz([Table852ZANew_QC],0)<br><br>Now it is very easy to see what fields have changed on the form.<br><br>Thank you very much.
- Status
Similar threads
- Locked
- Question