Looping problems - Microsoft Access / VBA

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

Subscribe Post Reply

1418

Reggie

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

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

Similar topics

SELECT duplicates without looping

by: bellefy | last post by:

Hi All, I have a fairly large table with approx 30K rows that updates every night via a cron script that automatically downloads the 2 new csv's. The problem is the files are downloaded from...

MySQL Database

SelectSingleNode always selects same node when looping through

by: Claire Reed | last post by:

Dear All, I am repeatedly encountering a problem whilst looping through XML Nodes and I am unsure as to what is going on and how I can get around it. I load the following XML document into an...

.NET Framework

problems with looping, i suppose

by: John Salerno | last post by:

Here's an exercise I was doing to guess a number from 1-100. Just for fun (ha ha) I decided to add some error checking too, and now when I run it, the DOS prompt flashes real quick and disappears....

Python

Looping through CheckBoxList items doesn't pick up selected ones when items were disabled

by: Bart Van Hemelen | last post by:

The situation: I have a CheckBoxList cblTest, the items are disabled in cblTest_DataBound in a foreach (ListItem oItem in cblTest.Items) loop. I provide a link that calls a client-side JavaScript...

ASP.NET

Is looping FindWindow() ok?

by: Jim S | last post by:

Is looping FindWindow() ok? We have a simple little c# 2.0 app thatâ€™s sole purpose is to look for a dialog box from another app to pop up and then click itâ€™s OK button. To do this we use a...

C# / C Sharp

Left$ Function, Looping through

by: Dököll | last post by:

Hiya, Partners! I have been into it for 12 hours straight this week-end, my son is very unhappy. Looks like I am getting pretty close but need your help, Again. I will post my first...

Visual Basic 4 / 5 / 6

Flash sound file looping problems

by: hayz | last post by:

Flash sound file looping problems hello there I'm definitely a newb so please bare some patience. I have a flash sound file on the index page of a site i'm working on. First off i need the...

Flash / Actionscript

Two problems, probably simple answers, flash 8

by: MikeDiver | last post by:

My first problem is that i was working on a project that had two radio buttons in it, i decided to delete one of them and now i cant get the second to activiate without being clicked (the second...

Flash / Actionscript

how to calculate the number of looping?

by: vbwire | last post by:

i use vb 6.0 Option Explicit Dim Error As Double Dim x As Integer Dim y As Double Dim z As Integer

Visual Basic 4 / 5 / 6

How to turn on java script in a villaon keypad mobile phone

by: Charles Arthur | last post by:

How do i turn on java script on a villaon, callus and itel keypad mobile phone

Java

Migrating Website to Cloud - Emmanuel Katto

by: emmanuelkatto | last post by:

Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel

General

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

Online Marketing

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

General

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

Microsoft Access / VBA