Microsoft Access - Continuous Forms and formatting each record individually

I have a complex database with heaps of forms and extensive use of subforms.
I sometimes have to use continuousforms in a subform and generally this is fine for simple lists of records.
However, I need to format each record for the following:
1. if record is archived, title is formatted with rd text. if not archived (ie current) then title should be green text.
2. a similar type date condition sets the background form colour to a different colour.
3. the value of a field determines if another field is displayed or enabled.

The problem comes when running the form - when scrolling through the continuous forms, the formatting is applied to all the forms.  So, all the records will have the formatting of the last record in the cnt. form.

I think this is a problem which cant be "fixed".
I have tried using conditional formatting but this is linked to one field and only allows text colour or font changes.  It doesnt allow visiblility properties to be set or enabled/locked settings. So this is not the answer.

I hope there is a solution as I cnt see another way around progamming this normally.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

1 & 3 in your list are just Conditional Format settings . You can use cf to enable/disable a control, and although you can't affect its visible property you can apply a format to the control to make it the same as the background it appears on.

There is no cf for a background so you would have to put a false background onto the form, by adding a textbox control the size of the form and setting its background colour. Use the SendToBack/BringToFront format options to get the controls on top of the textbox.

In a continuous form there is nothing else available that will do this.  Any formatting you do in code will be applied to all visible copies of that control.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
That's the nature of Continuous forms ... in essence, you're seeing the SAME controls printed to screen for each record. You cannot individually format a singe control (since there are really no single controls) ... Conditional Formatting is the closest you'll get.

You could switch to a 3rd party grid control (Janus Gridex is one); this gives you full control over each individual cell in the grid.
toylibraryAuthor Commented:
Thank-you - I did not realise I could use CF to enable/disable  as I didnt see that tiny button at the end!
This will have to suffice as the solution for now.

LSMConsulting: - what are these 3rd party grids you mention?  IS it an add-on?
It is not part of Access I assume so how would it be ultimately deployed/used in compiled version of the program?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
They are ActiveX controls you can use in the Access environment. Deployment can be a bit of a hassle; normally there's a "runtime" version of the control that you would deploy and install on the enduser machine.
toylibraryAuthor Commented:
Thanks for the information - I will have to research the use of ActiveX in Access.  This particular database has over 150 users so I need to be very sure of deployment!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.