mercredi 15 avril 2015

Show total record count on single form with filter

I have a database that tracks outstanding tasks. The data for this form is stored in a table called tblTasks. The unique identifier is TaskNumber. The form has two dropdown menues which filter the records. You pick a client name and you pick a task (taskdescription). It then shows all outstanding items for this client and this task (similarly, you can just filter based on client). I want a text box at the top of the screen to show the current record and another to show to total records (after the filter). When the form opens, I adjust the controlsource of this textbox like this.



Me!RecHigh.ControlSource = "=DCOUNT('[TaskNumber]', 'tblTasks', '[DateComplete] Is Null')"


RecHigh is the name of the textbox where I want the record count to show. This works great. The issue is, once the user begins to pick the client and the task, I can't get this filter to work. I think it may just be that I can't seem to get the syntax right. Can anyone help? Here is what I have right now. It doesn't give an error, rather it shows a total record count of 0, even though there are many.



Me!RecHigh.ControlSource = "=DCOUNT('[TaskNumber]', 'tblTasks', '[Client]='' & Client & '' And [TaskDescription]='' & Descr & '' And [DateComplete] Is Null')"


Client is a variable which is defined by the combobox cmbClientName and Descr is a variable which is defined by the combobox cmbTaskD. Both are populating correctly, and the form filters exactly how I want. Below is the working code for the filter.



Client = Me.cmbClient.Value
Descr = Me.cmbTaskDesc.Value

Me.FilterOn = False
Me.Filter = "[Client]='" & Client & "' And [TaskDescription]='" & Descr & "' And [DateComplete] Is Null"
Me.FilterOn = True


Any help would be appreciated. I realize you can add the navigation buttons at the bottom and get something like this, but I am trying to basically rebuilt that functionality in a custom way for the form. I am also open to alternate methods of making this work if there is a better way. I was hoping for something easy, like =DCOUNT() or =Form.RecordCount or something, but I can't seem to find any functionality that allows for counting of filtered records. All of my previous attempts ended up showing TOTAL count of all records in the table, whether the form was filtered or not.


Aucun commentaire:

Enregistrer un commentaire