Counting the number of records 2

[xtraterrestrial]

Hi I have the following code in a form to count the number of records in a table

Private Sub Form_Current()
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
lblRecordNum.Caption = rs.RecordCount
rs.Close
Set rs = Nothing
End Sub

When I first start up the form the number of record seems to be only 1. I have to move to a new record before the real number of records show up. I tried putting the code into Form_Load and get the same results. Any suggestioins?

 

[mph1]

Look at the following. Hope this is what you are tying to do.

http://www.mvps.org/access/forms/frm0026.htm

Also, when you open the form, have it go to a new recored automatically

Maurie

An investment in knowledge always pays the best dividends.

by Benjamin Franklin

 

[ptpaton]

If codes doesn't work, put an expression called "count" in the query that your form is based on with a value of "1"

Count: "1"

Then, create a text box with the control source: =sum([Count])

This will add your records for you.

-Patrick

Nine times out of ten, the simplest solution is the best one.

 

[ptpaton]

If code doesn't work, put an expression called "count" in the query that your form is based on with a value of "1"

Count: "1"

Then, create a text box with the control source: =sum([Count])

This will add your records for you.

-Patrick

Nine times out of ten, the simplest solution is the best one.

 

[xtraterrestrial]

The code didn't seem to work.

I implemented it using a count variable however, when the form is first loaded it still displays the number of records as 1, even though there are more than one records

 

[ptpaton]

Hmmm...if you sum the [Count] field, it should give you the number of records. Check your query...does it have a "1" on each record in datasheet view?

-Patrick

Nine times out of ten, the simplest solution is the best one.

 

[mph1]

The code from the article shoud work. It does for me. Something esle you might try... create a new text box in the form, it its control source put

=Count([IDNumb])

Where I have IDNum use whatever title you have for the autonumber field. This assumes you are not running off a query where you filter files out.

HTH

An investment in knowledge always pays the best dividends.

by Benjamin Franklin

 

[xtraterrestrial]

ptpaton

I think I misunderstood what you meant by put a count in the query. I'm not totally sure what you mean by that. My form is based on a table

 

[mph1]

Since your form is off a table, I know this will work.

=Count([IDNumb])

Where I have IDNum use whatever title you have for the autonumber field.

If you don't have an ID# of some kind, you should in order to make it an identifier for each record.


An investment in knowledge always pays the best dividends.

by Benjamin Franklin

 

[xtraterrestrial]

thanks mph

it works perfectly

 

[RoyVidar]

Hi!

Back to your original question, why doesn't this code work. The recordcount property isn't always working as (I believe) intended.

By moving to last record in code, it should normally produce the correct record count

Private Sub Form_Current()
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.movelast
lblRecordNum.Caption = rs.RecordCount
rs.Close
Set rs = Nothing
End Sub

Roy-Vidar

 

[xtraterrestrial]

adding rs.movelast actually crashes the program.

I think the problem it doesnt show the correct number of records is because the records haven't been loaded properly when the form first initially loads up.

 

[RoyVidar]

that wasn't very nice of ms. access, would you say;-)

Might be a version thingie, works fine on Access 2000 and XP.

The form on load event is where the data is loaded, and the on current event fires later on, so this shouldn't happen:-(

Only difference between your code and mine is, i reference the control with

me!lblRecordNum.Caption

But if the movelast trigger the crash, I don't know. Hope your code hasn't become corrupted.

As long as the other thingy works, wer'e all happy

Roy-Vidar

 

[ReluctantDataGuy]

I'm having this same problem and I've found that its a timing problem. OnCurrent Code runs before the recordset is completely loaded, so if you have a form that opens quickly, Me.Recordset.RecordCount works fine. If your form loads a little slower, Me.Recordset.RecordCount just shows 1.

Interestingly, you can't capture the problem in break mode because break mode doesn't stop the form's recordset from loading, and the homemade navigation bars work fine because the code stops, but the loading of the recordset doesn't.

Unfortunately, unless someone has a better solution, I'm gonna have to use the native navigation bars, which I hate for several reasons. You can't put them where you want them, you can't change their appearance, and you can't make them give you more information - like instead of "Record 1 of 7" maybe I want it to say "New Record - Unsaved".

If anyone has a good solution to this problem I'd sure like to see it.

Thanks!
ReluctantDataGuy

 

[OhioSteve]

Use Dcount(). For example,

variable1 = DCount("[field1]", "table1&quot

This code counts the number of records in table1 where field1 has a non-null value. It writes the number to variable1. Just be sure to choose a field that is never null, and you will be counting the number of records in the table.

 

[adamroof]

Easy solution!

add a text box, call it text30
In the form current, add this...

If Me.RecordsetClone.RecordCount > 0 Then
Me.RecordsetClone.MoveLast
Me![Text33] = Me.RecordsetClone.RecordCount
Else
Exit Sub
End If

It handles nulls in the record count in case you filter a form and have no records!