New to Looping

[FREDDY67]

I have a form with 100 textboxes on & I want to loop through table records & colour the textboxes which meet the criteria. The textboxes are named 'txt1' , 'txt2' & so on to 100. The code below works to a degree but not correctly, when I use the code it changes the colour of the first textbox which meets the criteria but then when it loops it re-colours the first matching textbox & ends up just colouring the textbox which meets the criteria of the last recordset.

Private Sub DisplayStaff()
Dim rst As DAO.Recordset2
Dim strSQL As String
Dim i As Integer
Dim intYear As Integer


SetCalendar

strSQL = "SELECT * FROM [tblYearStaffTemp] WHERE [UserName] =’Simon Jones';"

Set rst = CurrentDb.OpenRecordset(strSQL)

If rst.RecordCount > 0 Then
rst.MoveFirst

Do While Not rst.EOF

For i = 1 To 100

If rst!DV = i And rst!yr = intYear Then
Me("Txt" & i).BackColor = vbBlack
Else
Me("Txt " & i).BackColor = vbWhite
End If

Next i
rst.MoveNext
Loop

rst.Close
Set rst = Nothing
Else
rst.Close
Set rst = Nothing
End If

End Sub

Any assistance would be appreciated.

Freddy

 

[DCrake]

Is this a continious form that you ar trying to populate?

If you had only one record in the recordset it would work ok one a single form, but what I think is happening is that as soon as it move to the next record in the reocrdset it re applys the logic. It may be that you willneed to use the conditonal formatting on your detail fields.

 

[gemma-the-husky]

which access version are you using?

can you not get this very simply with conditional formatting?

 

[Rich]

100 textboxes?

 

[gemma-the-husky]

2 years employmnt history for a temp teacher?

 

[FREDDY67]

Hello

It is a Single Form & I am using Access 2007, the table contains a record showing the type of jobs or combination of jobs carried out each day in a period & on the form the textboxes represent days. Whilst at present the period is 100 days & I could represent each day by a field in the table (then use conditional formatting), at some point it may become 365 days & then that's not possible.

Thanks

Freddy

 

[FREDDY67]

Not the correct way (I will use whilst I look for a better solution), but by placing an extra if in the original it works as required.

Private Sub DisplayStaff()
Dim rst As DAO.Recordset2
Dim strSQL As String
Dim i As Integer
Dim intYear As Integer


SetCalendar

strSQL = "SELECT * FROM [tblYearStaffTemp] WHERE [UserName] =’Simon Jones';"

Set rst = CurrentDb.OpenRecordset(strSQL)

If rst.RecordCount > 0 Then
rst.MoveFirst

Do While Not rst.EOF

For i = 1 To 100

If rst!DV = i And rst!yr = intYear Then
Me("Txt" & i).BackColor = vbBlack
Else
If Me("Txt " & i).BackColor = vbWhite Then
Me("Txt " & i).BackColor = vbWhite
Else
Me("Txt" & i).BackColor = vbBlack
End If
End If

Next i
rst.MoveNext
Loop

rst.Close
Set rst = Nothing
Else
rst.Close
Set rst = Nothing
End If

End Sub



Freddy

 

[DCrake]

It seems to me that your database structure is seriously denormalised, and without seeing a screenshot of your data collection form is sounds like it is extremely busy.

 

[FREDDY67]

This form acts as a report to analyze the data in an excel manner showing cyclic patterns, it is populated from a table that only ever contains data when the form is in use. So yes this table contains repeat data on temporary basis, I felt it would be more efficient to loop through temporarily stored data rather than sql's containing calculations & groupings. At present the formatting on the form is simple in order learn the principle behind the formatting, but as it develops into the required 12 colours the table loop seems likely to be more efficient.

Freddy