• look for difference to previous record

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » look for difference to previous record

    Author
    Topic
    #464772

    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

    Viewing 17 reply threads
    Author
    Replies
    • #1191239

      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.

    • #1191240

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

      grts Nancy

    • #1191288

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

      • #1191289

        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.

    • #1191298

      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.

      • #1191300

        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.

    • #1191302

      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.

      • #1191304

        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:

    • #1191307

      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.

    • #1191336

      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

    • #1191357

      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

    • #1191362

      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.

      • #1191363

        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.

    • #1191364

      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.

    • #1191365

      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

    • #1191367

      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.

    • #1191372

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

      Many thanks

    • #1191377

      Hello,

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

      Grts Nancy

    • #1191378

      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.

    • #1191383

      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

    • #1191399

      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 ?

    • #1191413

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

      Many many thanks.

    Viewing 17 reply threads
    Reply To: look for difference to previous record

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: