look for difference to previous record

Topic

New Reply

Hello,

I have a continues form. In that form there will be records who have the same date.
I want that records with the same date has a other collor (font or background) than the other records who has a different date.
See printscreen in attachement, this is an example how I want it.
Is this possible in access?

thanks on advance

Reply | Quote

Replies

Hi Nancy,

I think this is not possible in a continuous form or in datasheet view. With some VBA, you could achieve this in a report.

Reply | Quote

Hello Ciske,
Thanks for the reply, I thought it was no possible, but the customer asked for it.

grts Nancy

Reply | Quote

Check out this database and see if you can use it.

Reply | Quote

Hi Pat,

That database was originally created and posted by me (freely using ideas from others); I don’t think it will help Nancy (plutoken) directly – to do what she wants would be quite complicated. As Francois remarked, it can be realized more easily in a report.

Reply | Quote

I can think of a couple of options that use Conditional Formatting, based on a hidden control that counts the number of records with the same date.

(I have only applied the conditional formatting to the date, but it could be applied elsewhere)

Setting the Control Source of the hidden control to:

Code:

=DCount("entryID","tblEntries","[Entrydate]=#" & Format([entrydate],"mm/dd/yyyy") & "#")

Would allow you to to able to Add New records, and Edit existing records in this form, but would probably be slow if there is much data.

If the form is just used for viewing records, then a query that counts records by date, would be a much faster way to provide the count.

Reply | Quote

John, your suggestion would highlight records with the same date as the one in the current record.

If I interpret Nancy’s request correctly, records would be highlighted alternatingly according to the date field. That is not the same.

Reply | Quote

Hans, I agree with your interpretation of Nancy’s request, but I don’t think you are right about my solution. Here is another screenshot.
Current record is entry 1, but other records are highlighted based on the Date.

Reply | Quote

Current record is entry 1, but other records are highlighted based on the Date.

Sorry, I misunderstood – but I still don’t think it’s what Nancy asked. As far as I can tell, she’d want the records for the 6th highlighted, but not those for the 7th, etc. as in this screenshot:

Reply | Quote

OK I have read her post again, and I think I got it wrong. I thought she wanted to highlight any record where the date was repeated.

Reply | Quote

Well, let’s see what she says…

Reply | Quote

Nancy,

I have been searching a little and I think I have to change my mind. Look at the attached mdb and see if you can do something with it.
I wrote a function to add a Boolean field to the recordset. That field change from true to false and vice versa each time the date change in the date field. Once that field is created, it’s easy to use the conditional formating to change the backcolor of each field in the form.

HTH

Reply | Quote

Hello Francis,
I have opend your example of form and that is exactly what I want.
I will give it a try in my database and let you know if it worked.

Many many thanks.

grts Nancy

Reply | Quote

Hello Francis,

I works just fine your code, exept, in my database it is just the “alternate” field that is colored, not the whole row.
If you want I can upload my database. But it is in access 2007.

I have an extra question, can I determe which color it should be? Now it is default yellow.

Thanks on advance.

Reply | Quote

Francois uses conditional formatting on the text boxes. You should apply the same conditional formatting to all text boxes (and combo boxes) in the detail section.
You can change the fill color in the conditional formatting dialog.

Reply | Quote

You have to set the conditional formating for each field on the form individually.

You have to click on the little arrow (see attachment) beside the paint pot to change the color.

Reply | Quote

Hello,
Yes i found it.
But it only change de fields, not the complete background of the detail section.
Is it possible to change that to? In VB f.e.?

grts Nancy

Reply | Quote

No, you can’t use conditional formating to change the backcolor of the detail section.
What you can do is create a textbox as great as the detail section, put it behind all the other textboxes and change the backcolor of that textbox with conditional formating.

Reply | Quote

Hallo Francis,
That was a good idea and it works just fine.
For me this case is closed 🙂

Many thanks

Reply | Quote

Hello,

I have one remark.
When I have this form in a subform the condtitions does not work like it should.

Grts Nancy

Reply | Quote

Are you saying that the conditional formating is not working or that the AlternateColor field is not correct ?
Maybe you can attach a stripped down version of your DB, so we can see what you have.

Reply | Quote

Hallo,
In bijlage de database, het formulier start automatisch op, gewoon in de combobox het eerste record kiezen.
Ik heb de database van accdb omgezet naar mdb.

Translation :
In attachment the database. The form start automatically, just select the first record in the combobox.
I have saved the database from accdb to mdb.

grts Nancy

Reply | Quote

Nancy,

Please post in English so that everyone can follow the thread.

I found two problems in your database:
– the subform contains a subset of the whole table.
– the subform is sorted descending

I modify the function ChangeColor and the query QrysubLogboek.

Can you test the modification in your db ?

Reply | Quote

Hello,
Sorry for the dutch in my previous answer, I my hurry I was forgotten it.
It works fine, without problems.

Many many thanks.

Reply | Quote