By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,804 Members | 1,627 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,804 IT Pros & Developers. It's quick & easy.

Looping problems

P: n/a
pj
Code below is on continuous form. Depending on a Y/N field value the colours
should change on the form. Although the looping appears to work it sets all
controls in each record to the first record's colour. I've tried setting
criteria to the key numeric field in the table to the same field in form
without success. The code has been tested in various form events but no
luck.
Any useful ideas would be very welcome.
Regards

pj

Private Sub Form_Current()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tblImportFile", dbOpenDynaset)
rs.MoveFirst
Do While Not rs.EOF
If Me.LCDone = 0 Then
Me.LCDoneDate.ForeColor = 255
Me.LCDoneDate_Label.ForeColor = 255
ElseIf Me.LCDone = -1 Then
Me.LCDoneDate.ForeColor = 0
Me.LCDoneDate_Label.ForeColor = 0
End If
If Me.LCConfirm = 0 Then
Me.BankConfirmLC.ForeColor = 255
Me.BankConfirmLC_Label.ForeColor = 255
ElseIf Me.LCConfirm = -1 Then
Me.BankConfirmLC.ForeColor = 0
Me.BankConfirmLC_Label.ForeColor = 0
End If
If Me.SuppLCConfirm = 0 Then
Me.SuppLCConfirmDte.ForeColor = 255
Me.SuppLCConfirm_Label.ForeColor = 255
ElseIf Me.SuppLCConfirm = -1 Then
Me.SuppLCConfirmDte.ForeColor = 0
Me.SuppLCConfirm_Label.ForeColor = 0
End If
If Me.CADocs = 0 Then
Me.CADocsDate.ForeColor = 255
Me.CADocsDate_Label.ForeColor = 255
ElseIf Me.CADocs = -1 Then
Me.CADocsDate.ForeColor = 0
Me.CADocsDate_Label.ForeColor = 0
End If
If Me.FUETA = 0 Then
Me.FUETADte.ForeColor = 255
Me.Label70.ForeColor = 255
ElseIf Me.FUETA = -1 Then
Me.FUETADte.ForeColor = 0
Me.Label70.ForeColor = 0
End If
If Me.FUDocs > "" Then
Me.FUDocs.ForeColor = 0
End If
If Me.EMailBankLC = -1 Or Me.EmailSuppTT = -1 Then
Me.LCTTDate.ForeColor = 0
Me.LCTTDate_Label.ForeColor = 0
End If
If Me.ETAConfirmed = -1 Then
Me.ConfirmETA.ForeColor = 16711680
Me.ConfirmETA_Label.ForeColor = 16711680
ElseIf Me.ETAConfirmed = 0 Then
Me.ConfirmETA_Label.ForeColor = 255
End If
If Me.GRNDone = -1 Then
Me.GRNDate.ForeColor = 0
Me.GRNDate_Label.ForeColor = 0
ElseIf Me.GRNDone = 0 Then
Me.GRNDate.ForeColor = 255
Me.GRNDate_Label.ForeColor = 255
End If
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Have you tried using conditional formatting? Open form in design view.
Select a field then select the Format/Conditional Formatting from the main
menu.

--
Reggie

www.smittysinet.com
----------
"pj" <pj@mevaprod.co.za> wrote in message
news:NZ********************@is.co.za...
Code below is on continuous form. Depending on a Y/N field value the colours should change on the form. Although the looping appears to work it sets all controls in each record to the first record's colour. I've tried setting
criteria to the key numeric field in the table to the same field in form
without success. The code has been tested in various form events but no
luck.
Any useful ideas would be very welcome.
Regards

pj

Private Sub Form_Current()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tblImportFile", dbOpenDynaset)
rs.MoveFirst
Do While Not rs.EOF
If Me.LCDone = 0 Then
Me.LCDoneDate.ForeColor = 255
Me.LCDoneDate_Label.ForeColor = 255
ElseIf Me.LCDone = -1 Then
Me.LCDoneDate.ForeColor = 0
Me.LCDoneDate_Label.ForeColor = 0
End If
If Me.LCConfirm = 0 Then
Me.BankConfirmLC.ForeColor = 255
Me.BankConfirmLC_Label.ForeColor = 255
ElseIf Me.LCConfirm = -1 Then
Me.BankConfirmLC.ForeColor = 0
Me.BankConfirmLC_Label.ForeColor = 0
End If
If Me.SuppLCConfirm = 0 Then
Me.SuppLCConfirmDte.ForeColor = 255
Me.SuppLCConfirm_Label.ForeColor = 255
ElseIf Me.SuppLCConfirm = -1 Then
Me.SuppLCConfirmDte.ForeColor = 0
Me.SuppLCConfirm_Label.ForeColor = 0
End If
If Me.CADocs = 0 Then
Me.CADocsDate.ForeColor = 255
Me.CADocsDate_Label.ForeColor = 255
ElseIf Me.CADocs = -1 Then
Me.CADocsDate.ForeColor = 0
Me.CADocsDate_Label.ForeColor = 0
End If
If Me.FUETA = 0 Then
Me.FUETADte.ForeColor = 255
Me.Label70.ForeColor = 255
ElseIf Me.FUETA = -1 Then
Me.FUETADte.ForeColor = 0
Me.Label70.ForeColor = 0
End If
If Me.FUDocs > "" Then
Me.FUDocs.ForeColor = 0
End If
If Me.EMailBankLC = -1 Or Me.EmailSuppTT = -1 Then
Me.LCTTDate.ForeColor = 0
Me.LCTTDate_Label.ForeColor = 0
End If
If Me.ETAConfirmed = -1 Then
Me.ConfirmETA.ForeColor = 16711680
Me.ConfirmETA_Label.ForeColor = 16711680
ElseIf Me.ETAConfirmed = 0 Then
Me.ConfirmETA_Label.ForeColor = 255
End If
If Me.GRNDone = -1 Then
Me.GRNDate.ForeColor = 0
Me.GRNDate_Label.ForeColor = 0
ElseIf Me.GRNDone = 0 Then
Me.GRNDate.ForeColor = 255
Me.GRNDate_Label.ForeColor = 255
End If
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub

Nov 12 '05 #2

P: n/a
pj
Reggie
Thanks for response. The criteria for setting a related date field to a
check box is too complex for CF.
pj
"Reggie" <no**********@smittysinet.com> wrote in message
news:XZ********************@comcast.com...
Have you tried using conditional formatting? Open form in design view.
Select a field then select the Format/Conditional Formatting from the main
menu.

--
Reggie

www.smittysinet.com
----------
"pj" <pj@mevaprod.co.za> wrote in message
news:NZ********************@is.co.za...
Code below is on continuous form. Depending on a Y/N field value the

colours
should change on the form. Although the looping appears to work it sets

all
controls in each record to the first record's colour. I've tried setting
criteria to the key numeric field in the table to the same field in form
without success. The code has been tested in various form events but no
luck.
Any useful ideas would be very welcome.
Regards

pj

Private Sub Form_Current()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tblImportFile", dbOpenDynaset)
rs.MoveFirst
Do While Not rs.EOF
If Me.LCDone = 0 Then
Me.LCDoneDate.ForeColor = 255
Me.LCDoneDate_Label.ForeColor = 255
ElseIf Me.LCDone = -1 Then
Me.LCDoneDate.ForeColor = 0
Me.LCDoneDate_Label.ForeColor = 0
End If
If Me.LCConfirm = 0 Then
Me.BankConfirmLC.ForeColor = 255
Me.BankConfirmLC_Label.ForeColor = 255
ElseIf Me.LCConfirm = -1 Then
Me.BankConfirmLC.ForeColor = 0
Me.BankConfirmLC_Label.ForeColor = 0
End If
If Me.SuppLCConfirm = 0 Then
Me.SuppLCConfirmDte.ForeColor = 255
Me.SuppLCConfirm_Label.ForeColor = 255
ElseIf Me.SuppLCConfirm = -1 Then
Me.SuppLCConfirmDte.ForeColor = 0
Me.SuppLCConfirm_Label.ForeColor = 0
End If
If Me.CADocs = 0 Then
Me.CADocsDate.ForeColor = 255
Me.CADocsDate_Label.ForeColor = 255
ElseIf Me.CADocs = -1 Then
Me.CADocsDate.ForeColor = 0
Me.CADocsDate_Label.ForeColor = 0
End If
If Me.FUETA = 0 Then
Me.FUETADte.ForeColor = 255
Me.Label70.ForeColor = 255
ElseIf Me.FUETA = -1 Then
Me.FUETADte.ForeColor = 0
Me.Label70.ForeColor = 0
End If
If Me.FUDocs > "" Then
Me.FUDocs.ForeColor = 0
End If
If Me.EMailBankLC = -1 Or Me.EmailSuppTT = -1 Then
Me.LCTTDate.ForeColor = 0
Me.LCTTDate_Label.ForeColor = 0
End If
If Me.ETAConfirmed = -1 Then
Me.ConfirmETA.ForeColor = 16711680
Me.ConfirmETA_Label.ForeColor = 16711680
ElseIf Me.ETAConfirmed = 0 Then
Me.ConfirmETA_Label.ForeColor = 255
End If
If Me.GRNDone = -1 Then
Me.GRNDate.ForeColor = 0
Me.GRNDate_Label.ForeColor = 0
ElseIf Me.GRNDone = 0 Then
Me.GRNDate.ForeColor = 255
Me.GRNDate_Label.ForeColor = 255
End If
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub


Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.