Formatting in a continuous form 1

[jonney]

Hi there

I have a form with a sub form which is a continuous form. There is a field SalesPersonID, which I would like to format in different colours when the SalesPersonID changes. So at a glance the user can identify the salesperon.

I am sure it is a simple bit of code but I am a beginner and a little help would be much appreciated.

Jonney

 

[ZmrAbdulla]

You can use Conditional formatting if you are using Access 2000 or higher.
While you are in Design Veiw of the Form
Format Menu > Conditional Formatting
But how many Sales Persons you have? I think this is fixed to 3 steps.


Zameer Abdulla
Visit Me
Where to pay your [purple]Tsunami[/purple] relief donations?

 

[MichaelRed]

A slight variation -available in most (all?) versions is to mark the field to hide duplicates.





MichaelRed

 

[TheAceMan1]

How are ya jonney . . . . .

[blue]ZmrAbdulla[/blue] is correct. You can only have [blue]3 conditional formats[/blue] ([blue]actually 4 including default settings[/blue]). Therefore a max of 4 different colors.

If you don't mind [blue]alternating bewteen two colors[/blue] (1 format condition + default) whenever [purple]SalesPersonID[/purple] changes, try the following:

[ol][li]The code requires [purple]Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. Click OK.[/li]

[li]A new field is required to hold a tag for the color, so add a field to the appropriate table.
[ol a][li]Name [blue]colTag[/blue][/li]
[li]DataType [blue]Yes/No[/blue][/li][/ol][/li]

[li]In a module in the modules window, copy/paste the following code ([blue]you![/blue] substitute proper names in [purple]purple[/purple]):

[blue]Public Sub SetColorTags()
   Dim rst As DAO.Recordset, Color As Boolean, Hld
   
   Set rst = Forms![purple][b]YourFormName[/b][/purple].RecordsetClone
   
   With rst
      If Not .BOF Then
         .MoveNext
         
         If Not .EOF Then
            Do
               Hld = !SalesPersonID
               .MovePrevious
               
               If !SalesPersonID <> Hld Then Color = Not Color
               .MoveNext
               .Edit
               !ColTag = Color
               .Update
               
               .MoveNext
            Loop Until .EOF
         End If
      End If
   End With
   
   Set rst = Nothing
[/blue]

[/li]

[li]Open the form in design view. If the [blue]RecordSource[/blue] for the form is a [blue]Query or SQL[/blue], make sure the new field [blue]colTag[/blue] is included. The new field will work in the background, so don't add it to the form.[/li]

[li]In the [blue]Open event[/blue] of the form, copy/paste the following:

[blue]   Call SetColorTags[/blue]

[/li]

[li]The following code takes care of [blue]adding new records[/blue]. In the [blue]AfterUpdate event[/blue] of the form, copy/paste the following:

[blue]Private Sub Form_AfterUpdate()
   Dim rst As DAO.Recordset, Hld, hldTag As Boolean
   
   Set rst = Me.RecordsetClone
   
   With rst
      .FindFirst "[SalesPersonID] =" & Me!SalesPersonID
      .MovePrevious
      
      If Not .BOF Then
         Hld = rst!SalesPersonID
         hldTag = !ColTag
         .MoveNext
         
         .Edit
            If !SalesPersonID <> Hld Then
               !ColTag = Not hldTag
            Else
               !ColTag = hldTag
            End If
         .Update
      End If
   End With
   
   Set rst = Nothing

End Sub[/blue]

[/li]
[li]Finally the Conditional Format. In form design view select [purple]SalesPersonID[/purple]. Then click [blue]Format[/blue] - [blue]Conditional Formatting[/blue]. In the combobox select [purple]Expression Is[/purple]. To the right of the combobox enter:

[blue][colTag]=True[/blue]

Select your colors and click OK.[/li][/ol]
[purple]Thats it! . . . . give it a whirl & let me know . . . .[/purple]

See Ya! . . . . . .

 

[jonney]

Thanks guys I will try this out and let you know. Much appreciated.

Jonney