Number or filtered records 3

[Phudsen]

Hi,

I have a tablular form that lists all the records in the table. I added some combo boxes to filter some data using this code:

Me.Filter = "[Action]='" & Me.cboAction.Column(1) & "'"
Me.FilterOn = True

Everything is working just fine, all combo boxes are working and filtering records.

What I want is to add something to this code to count the number of filtered records. If I select an action from the combo box I get the right result of the action. But I want a field on the form that says, 35 records were filtered.

How to count the number or filtered records and save it in a variable.

Thanks
Paulin

 

[RoyVidar]

Hi!

The standard navigation buttons and recordnumbers at the bottom of each form shows that, else you could try something like this:

[tt]lngMyVariable = Me.RecordsetClone.RecordCount[/tt]

Roy-Vidar

 

[Phudsen]

Hi Roy,

Thank you for your reply.

I tried this and worked:
Me.Filter = "[Title]='" & Me.cboTitle.Column(1) & "'"
Me.FilterOn = True
RecordCount = DCount("[Title]", "CassetteMaster", "[Title] = '" & Me.cboTitle.Column(1) & "'")
Me.txtRecordNumber = RecordCount
Me.txtField = "Title"
Me.txtStatus = Me.cboTitle.Column(1)

I'll give your solution a try, it is much shorter.

Thanks
Paulin

 

[Phudsen]

Hi Roy,

I liked your solution much better. No need to open and close brackets.

Thanks a lot. It is working Perfectly.

Paulin

 

[Phudsen]

Hi Roy,

I noticed that my code show the correct number of recrods. But your code does not show more than 21.

Do you know why?

I prefer to use your code which is shorter, I have many fields.

Thanks

 

[RoyVidar]

On my setup this shows the number of filtered records (same as using [tt]=count(*)[/tt] as controlsource for a text control on a form - try this to as a test, and display the navigation buttons with recordnumber)

Are you sure it's placed after the filter is set and that 21 isn't the correct number of records? Could it be a timing issue, place a Me.Refresh before the count?

Roy-Vidar

 

[Phudsen]

Hi Roy,

This is what I did and I am still not getting more than 21, if the number of records is less than 21 then the count is correct. If the number of records is more than 21 I get only 21. This is the code:

Me.Filter = "[Language]='" & Me.cboLang & "'"
Me.FilterOn = True
Me.Refresh
RecordCount = Me.RecordsetClone.RecordCount
Me.txtRecordNumber = RecordCount

Is it OK?

Thanks

 

[Phudsen]

Hi Roy,

I got it, I know where I was wrong , SORRY

The recordsource of the form is a query, but in my code I put the table. This is why I am getting wierd results.

Thanks a lot
Paulin

 

[Phudsen]

Hi again,

Using my code is working fine after I typed the name of the query, but your code is still giving maximum of 21.

Do you have anything in mind?

Thanks
Paulin

 

[RoyVidar]

Hi!

I've used something like this reliable for some time, so I really don't know, here's some thoughts/alternatives:

Recordcount is a reserwed word, used to count the number of records in a recordset, and should not be used as a variable.

Does it change anything by using:

[tt]Me.Filter = "[Language]='" & Me.cboLang & "'"
Me.FilterOn = True
Me.Refresh
msgbox Me.RecordsetClone.RecordCount
Me.txtRecordNumber = Me.RecordsetClone.RecordCount[/tt]

- added a msgbox for good measure;-)

Since I've used something like the above, and not had any problems with it. I'm suspecting it could be a timing issue, that the populating of the recordset isn't finished when the counting takes place.

Did you try using =Count(*) as a controlsource in a text control?

Either that, or try something like this:

[tt]dim rs as dao.recordset
Me.Filter = "[Language]='" & Me.cboLang & "'"
Me.FilterOn = True
Me.Refresh
set rs=me.recordsetclone
' Either
Me.txtRecordNumber = rs.RecordCount
' or
rs.movelast
Me.txtRecordNumber=rs.absoluteposition+1
set rs = nothing[/tt]

Roy-Vidar

 

[Phudsen]

Hi Roy,

Still getting 21 records count maximum.

The message box is also displaying 21

Strange.


Thanks for your patience

Paulin

 

[Phudsen]

Roy,

Thanks a lot

The second one is working fine:

Me.txtRecordNumber = 0
Me.Filter = "[" & Me.Combo108 & "]='" & Me.Combo110 & "' And [" & Me.Combo113 & "]='" & Me.Combo114 & "'"
Me.FilterOn = True
Me.Refresh
Set rs = Me.RecordsetClone
rs.MoveLast
Me.txtRecordNumber = rs.AbsolutePosition + 1
Set rs = Nothing


I had to remove the DIM, and it worked just fine.

Would you please tell me why did you use AbdolutePosition+1 which is correct of course.

Thanks
Paulin

 

[RoyVidar]

I'd recommend to insert a dim again and check the Microsoft DAO 3.# Object Library (Tools | References when in the VBE)

Also I highly recommend using

[tt]Option Explicit[/tt]

as the second line at the top of the module. This will enforce variable declaration. I never dare program without it. (And in the Tools | Options - select "Require Variable Declaration" - which will add the mentioned line at the top of each new module)

For DAO recordset (don't know about 2003, but 2000 and xp form recordsets are DAO unless you're using ADP or explicitly set the form recordset to an ADO recordset), the .Absoluteposition property numbers the current recordset starting from 0 (first record is number 0). So to get the correct number, one needs to add 1.

In ADO, the .Absoluteposition property for the first record is 1, so no adding would be necessary there.

In spite of the name, the .Absoluteposition is just a temporary recordnumber good until the next filter/sort/requery.

Roy-Vidar

 

[Phudsen]

Thanks again Roy, you've been a tremendous help.

Paulin

 

[mswilson16]

just a quick note could you not just put the controlsource of me.txtrecordcount as =Count(*). this will update automatically?