Counting Records in Recordset with Condition

Diskutiere und helfe bei Counting Records in Recordset with Condition im Bereich Microsoft Office im Windows Info bei einer Lösung; I have a large table of dates and I am using an audit table to track the changes. Conditional formatting lets me highlight fields in my forms that have... Dieses Thema im Forum "Microsoft Office" wurde erstellt von rrzehren, 7. Februar 2019.

  1. rrzehren
    rrzehren Gast

    Counting Records in Recordset with Condition


    I have a large table of dates and I am using an audit table to track the changes. Conditional formatting lets me highlight fields in my forms that have recently been changed using a function.

    In the past I've been using DCount with a condition on the timestamp to determine whether there have been any recent changes. However, this causes performance problems as anytime a user scrolls through the lists, the cells are painted with conditional formatting and I get a lot of traffic from hundreds of DCount requests to my backend.


    Now I've come up with a workaround: When a user opens the form that displays the dates, I pull the recordset of changes from the backend once and use this snapshot recordset to check for changes.

    However, I cannot use DCount on a recordset. Do I have to write my own "DCount" or is there a more elegant solution? As mentioned, this is crucial to the performance of my application, so I appreciate every tip on how to do this as efficiently as possible.


    Thank you

    Raphael
     
  2. rrzehren Win User

    Counting Records in Recordset with Condition

    I am not sure how this would help in this scenario. I am not trying to look up values, I am counting records. I suppose you mixed up DCount() and DLookup?
  3. dhookom Win User

    Counting Records in Recordset with Condition

    Allen Browne has an alternative to DLookup() at
    http://allenbrowne.com/ser-42.html
    .

    The performance should be much better.

    Note: This is a non-Microsoft website. The page appears to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a **** (Potentially Unwanted Products). Thoroughly research any product advertised
    on the site before you decide to download and install it.
  4. rrzehren Win User

    Counting Records in Recordset with Condition

    I have now implemented my Idea and it seems like my performance has increased significantly. I still have some questions though:

    I have now created a public recordset of "recent changes" (blueFields) in my "global" module. It contains information about which records I want highlighted throughout my application using conditional formatting.

    The first time this recordset is filled with records is when my main form is populated with the data that the user selected. I then pull the respective changes from the backend into said DAO.Recordset and refresh the form so the formatting is displayed. This
    all happens in the form module.

    As I only check whether the recordset contains certain records, I open it as dbOpenSnapshot.

    Public Sub initBlueFields()    Dim dbs As DAO.Database    Set dbs = CurrentDb    Set blueFields = Nothing
    
        
    
        
    
        sqlOpenChanges = _
    
        "SELECT ID, Bestätigt, Projekt, Eintrag, Feld FROM AuditForm " & _
    
        "WHERE (Bestätigt=False) AND (Projekt =" & Me.Parent.cmbProjekt.value & ")"
        Set blueFields = dbs.OpenRecordset(sqlOpenChanges, dbOpenSnapshot)
    
        'this is what i would usually do in this case    'blueFields.Close
    
        'dbs.Close    'Set blueFields = Nothing
    
        'Set dbs = NothingEnd Sub
    The thing is, as you can see, I cannot terminate this db connection in the Sub as I could no longer access the public recordset from elsewhere in my application. (Closing the db empties the recordset as there are no disconnected Recordsets in DAO)

    When the user refreshes the form, I execute above Sub again. It empties the recordset and pulls the data again - works just fine.

    However, I am worried about the Currentdb that I set every time. Does this run multiple Connections or is the one that's currently running just "overwritten"?

    I've found confliction information about running a global instance of the CurrentDB, which is why I kept it in in my Sub.

    A workaround that I thought of was populating a local table with the changes instead of using the snapshot recordset. This way I could terminate the recordset and the Connection after I update the table. Maybe this would be a cleaner solution?

    Thanks for the help!

    Raphael
  5. markus888 Win User

    VBA Recordset-Objekt - Verständisfrage zu RecordCount

    ergänzend zu den Ausführungen von Karl Donaubauer würde ich dir empfehlen in der VBA Hilfe die Infos zum Thema Recordset und
    Database.OpenRecordset aufmerksam durchzulesen.

    Es ist zwar sehr einfach ein Recordset zu öffnen - um aber sinnvoll zu programmieren muss man das Verhalten verstehen und sich folglich etwas damit beschäftigen - viel ist es ja eh nicht.

    LG Markus
  6. markus888 Win User

    Endlos-Formular mit ADO-Recordset: letzte Zeile verschwindet bei neuem Datensatz

    Hallo Peter,

    ist sehr seltsam.

    Kannst du den Code bezüglich der Connection und Recordset posten?

    Insbesondere interessieren mich Angaben zu CursorLocation, CurserType und LockType.

    Geschieht das immer unabhängig von der Anzahl der Datensätze?

    Sortierst oder filterst du Datensätze via Recordset?

    LG Markus
  7. User Advert


    Hi,

    willkommen im Windows Forum!
Thema:

Counting Records in Recordset with Condition - Microsoft Office

Die Seite wird geladen...

Counting Records in Recordset with Condition - Similar Threads - Counting Records Recordset

Forum Datum

OneNote record video

OneNote record video: Hello, why is it not possible to record video directly from the OnNote app iPad and add it to the note? That would be a great addition!
Microsoft Office 10. September 2020

Counting persons in one cell

Counting persons in one cell: Good Evening, I would like to know how to count persons in excels. The persons are written in one cell and seperated by ";" I need urgent help! Thank you very much [IMG]
Microsoft Office 28. Mai 2020

Error opening ADO Recordset with MSDataShape provider

Error opening ADO Recordset with MSDataShape provider: Since Windows Update 1809 was public for some days, some of our users haved already installed this Windows version. These users are now reporting an error. We are using recordsets with the...
Windows Insider 16. Oktober 2018

ADO Recordset Formular Filter

ADO Recordset Formular Filter: Hallo, ich habe ein Formular als Datenblattansicht mit einem recordset, welches mittels ado.recordset belegt wird. Backend ist eine SQL-Datenbank (SQL-Express und auch SQL-2008-Server probiert)....
Microsoft Office 10. August 2018

Pocket Recorder™

Pocket Recorder™: Pocket Recorder™ [IMG] Version 10 adds support for Windows Phone 8.1 including: -save to directly to device folders or sd card -attach to email -Share with Office, OneNote -Speed enhancements...
Tools + Produktivität 22. März 2015

Mini Recorder Free

Mini Recorder Free: Mini Recorder Free [IMG] Mini Recorder is a tiny, but effective way to record audio on Windows Phone. Made with the focus on the minimalistic design and great user experience, it becomes the...
Tools + Produktivität 22. März 2015

Audio Recorder

Audio Recorder: Audio Recorder [IMG] Diese Anwendung ist ein einfaches und leicht zu bedienen Diktiergerät. Features: - Unbegrenzte Rekordzeit und Dateigröße - Einfache und leicht zu bedienende...
Musik + Videos 21. Februar 2015
Counting Records in Recordset with Condition solved
  1. Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten.
    Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet.
    Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.