Conditional Format, Continuous Forms

[darinmc]

Hi
I have seen similar posts BUT hopefully some1 will be able to get me there easier and quicker.

I have a form (continuous), I can either use a box or preferably use the BackColour of the details (If Possible)

On the form i will display more detail, However what i'm trying to achieve is alternate colours BUT here is the complication...

E.g.(Rota form)
Empreg Names(alphabetical) + etc etc
3254 Darin Toto + ...
3254 Darin Toto + ...
425 Eroll Marshal + ...
6523 Frank Butter + ...
6523 Frank Butter + ...

The First thing i'm trying to do (using Empreg), 1st record [3254], Box Fill colour OR Details section = white (16777215)
Next record [425] = Pale yellow (15794175)

To sum it up, each time the Empreg changes it will change the alternate records backcolour.

Is this possible?
WHERE and WHAT sort of coding would I use?
When I print the records, will it print in this way as well or would I have to create a report to achieve this?

I do have more questions which i would like to ask later after solving each individual problem..

Hope U can help, Plz?

Thx
Dain

 

[MajP]

http://www.lebans.com/alternatecolordetailsection.htm

not sure if it is easier and quicker.

 

[darinmc]

I Have had a look at this BUT dont know where to start to implement it.
As you can see, some records repeat 2 or 3 times and they will both need to be the same colour!

Therefore,
first - record(s) 1 & 2, 3254 Needs to be white (twice)
next - Record (Next different from 3254 - ie 425 - only 1) to be yellow
next - Record(s) (6523 - Be white again)

I imagine there will have to be a for... next loop somewhere..

Thx
Darin

 

[MajP]

Sorry. I did not read your post carefully.
AceMan provides a nice solution for alternating colors
thread702-1377558

Take a look at his thread to understand how I manipulated it. He may have a better idea. My answer is a little hard wired but works.

Using your forms rowsource make a distinct query, returning only 1 of each primary key. Call this query "qryDistinct". Modify AceMan's code to do the following.

Public Function AltBkg(frmRpt As Object, PKname As String) As Boolean
   Dim rsDistinct As DAO.Recordset
   Set rsDistinct = CurrentDb.OpenRecordset("qryDistinct", dbOpenDynaset)
   rsDistinct.FindFirst "[" & PKname & "] =" & frmRpt(PKname)
   If (rsDistinct.AbsolutePosition + 1) Mod 2 = 0 Then
      AltBkg = True
   End If
End Function

Do everything else as the thread states

 

[darinmc]

Thx MajP
I'm not very good at coding and might need quite a bit more help BUT i have attached a spreadsheet with notes which will show the exact effect i'm trying to achieve

http://www.4shared.com/file/5784472...arget=_blank>TEST Diagram of ROTA TekTips.xls

Thx
Darin

 

[MajP]

The code provided creates that effect. Everything should be a cut and paste from this thread and Ace Man's thread. Replace his AltBkg function with mine. You just need to make a distinct query returning only one Empreq per group, and save that as qryDistinct. It also has to be sorted in the same manner as the forms recordsource. Read the help on returning unique values.

 

[darinmc]

Hi
I'm trying to do a simple test... Created a New table(uTABLE) with 2 fields, EmpReg and Name.
I made EmpReg the Primary Key, Do I have to? I may want to use the same table information later using JobID to display alternate colours...

I then did a simple continuous form called (uFORM) using the above 2 fields.

1) Error = Object Required --- AltPrep() = True
2) What determines what the alternate back colour is? (I would like Pale yellow (15794175)

Module Code - Called it BackColour

Option Compare Database

Public Function AltBkg(FrmRpt As Object, PKname As String) As Boolean
   
   FrmRpt.RecordsetClone.FindFirst "[" & PKname & "] =" & FrmRpt(PKname)
   
   If (FrmRpt.RecordsetClone.AbsolutePosition + 1) Mod 2 = 0 Then
      AltBkg = True
   End If
   
End Function

Code on form

Private Function AltPrep()
   AltPrep = AltBkg(Me, "EmpReg")
End Function

Private Sub Form_Current()
AltPrep() = True
End Sub

Please can you tell me where I have gone wrong with the code

Thx
Darin

 

[MajP]

The code is designed to use on any form with a primary key. The function is flexible because you tell it the name of the form and the key to use.

Reread AceMan's Post.

The conditional formatting Expression Is should be:
AltPrep()=True

Highlight all of your controls in the row,and select from the menubar
Format
Conditional Formatting
change "field value is"
to "Expression is", and put in your Expression.

Get rid of your on current event.

 

[MajP]

In the conditional formatting you can pick any backcolors, forecolors, and other formatting.

 

[darinmc]

Thx, that seems to fill the text box, so I created another, made it the size of the detail area and sent it to the back.

Can the DETAIL area not be filled in colour OR does that mean coding would have to be used as in Lebans sample D/Base (AlternatColorDetailSection?)

I had to create another field TransID, as each is unique.

I put a word document on to see what all the tables, form and query look like.

Surely there must be some more coding as there wont be unique records in the distinct query as Jobs are different?

http://www.4shared.com/file/57990741/e0866231/Diagram_Alternating_Colours.html

Darin

 

[MajP]

so I created another, made it the size of the detail area and sent it to the back

Yes that is exactly what you have to do.
The detail section is one section so there is no way to put stripes in it unless you do what you did.

In your distinct query you only need one field and that is "EmpReg", as long as the EmpReg is sorted the same way as your forms recordsource.

Here is an example of what you are trying

http://www.4shared.com/file/58028345/83c1362b/AltColors.html?dirPwdVerified=5ee0ba64

 

[darinmc]

Thx, that seems to work fine. (I must have included to many fields in the distinct query)

I now need to add something different, in the diagram (On word), you can see the name repeats on each line.

Is there a way, maybe using conditional formating, to hide the (Name, Telephone Number -Not in now) in the following rows which have the same EmpReg?

Thx
Darin

 

[MajP]

You could do it with conditional formatting and a function to determine if the records are primary or subsequent. Then set the text color = the background color.

 

[MajP]

Here is an example

http://www.4shared.com/file/58122978/7f2baf42/AltColors.html

 

[darinmc]

Hi MajP
I downloaded the last file you posted, AltColors.

I couldn't see anything different to show the 2nd function, primary or subsequent.
I have no Idea how to do that?

I assume, after the function is created, i would go into the continuous form, highlight the appropriate txt boxes, (Name and Tel Number) and apply that condition..

Thx, Darin

 

[darinmc]

Hi
I have included my form i'm working on.
I have put some lines to go between each record.

http://www.4shared.com/file/58240323/3bc448dc/AltColTEST.html

Thx, Darin

 

[MajP]

Sorry I uploaded the wrong file

http://www.4shared.com/file/58246747/4891dc43/AltColors2.html

 

[MajP]

Take a look

http://www.4shared.com/file/58251072/b62e7c83/AltColTEST2.html

 

[darinmc]

Hi
I have updated the below database, the problem is the table (uTABLE) which holds the Rota / Timetable is over 12 Mb holding 48000 records.
When loading the form, i Have used a query instead of directly to the table. qryUtable opens very quickly, to a specific week, showing 395 records.
qryDistinct has 225 records, again quick.

http://www.4shared.com/file/58240323/3bc448dc/AltColTEST.html

I am assuming the conditional formating is whats slowing it down tremendously, about 12 seconds.... :-(

My computer i fairly fast, the form is snapshot. Surely there is a way to speed up OR do something about it???

plz plz PLZ
Darin

 

[MajP]

Yes this is not very efficient, but it should no run this slow. I am not sure why it is so slow on your query. For each record you have to open up two recordsets, do a search, and then apply the formatting. So this is my workaround. I can do this on your entire 40k records in a few seconds.

This is how I would do it.
1) Add a text field to your table "strFormInfo".
2)Add this field to your query, qryUtable
3) Paste this code into a module

Public Sub setFrmInfo()
  Dim rs As DAO.Recordset
  Dim lngCount As Long
  Dim lngID As Long
  Dim strType As String
  Set rs = CurrentDb.OpenRecordset("qryUtable", dbOpenDynaset)
  lngID = -1 'some bogus number
  Do While Not rs.EOF
    If lngCount Mod 2 = 0 Then
      If Not rs.Fields("EmpRegNo") = lngID Then
        strType = "NewEven"
        lngCount = lngCount + 1
        lngID = rs.Fields("EmpRegNo")
      Else
        strType = "OldOdd"
      End If
    Else
      If Not rs.Fields("EmpRegNo") = lngID Then
        strType = "NewOdd"
        lngCount = lngCount + 1
        lngID = rs.Fields("EmpRegNo")
      Else
        strType = "OldEven"
      End If
    End If
    rs.Edit
      rs.Fields("strFormInfo") = strType
    rs.Update
    'Debug.Print rs.Fields("EmpRegNo") & " " & strType
    rs.MoveNext
  Loop
End Sub

What this code does is tag a record as one of four possibilities
The first record for the first person is
NewEven
If that person has another record
OldEven
If you go to the next person
NewOdd
Another record for the above person is
OldOdd
and so on.

4)On the forms on load event run the procedure
You do not have to run it each time you open a form, and you probably do not want to. Just when records have been added or deleted prior to viewing. So figure out where you want to run it.
5) in the conditional formatting I would have alternating blocks of color for the background for each person;
expression is: [strFormInfo] = "NewEven" or [strFormInfo] = "OldEven" (yellow)
6)For the textboxes something like
[strFormInfo] = "OldEven" (yellow on yellow)
[strFormInfo] = "OldOdd" (white on white)

Example data (moded the names for:

Ruta Zoyd NewEven
Erimo Ormando NewOdd
Razi Sharafi NewEven
Razi Sharafi OldEven
Maria Dowit NewOdd
Maria Dowit OldOdd
Maria Dowit OldOdd
Maria Dowit OldOdd
Almaz Abrha NewEven
Roma Tewelde NewOdd
Roma Tewelde OldOdd
Roma Tewelde OldOdd
Roma Tewelde OldOdd
Roma Tewelde OldOdd
Roma Tewelde OldOdd
Daniel Haile NewEven

There is some issue with your form I can not fix. The sort order on the form is different than the query and I can not figure out where that is happening. But this works on a demo.

I can put all of your records into a query and this will open nearly instantaneously.