Record Count Problem on Form

[Neil Chelo]

I have a form with an on click event procedure to open up another form using
the code below. The navigation buttons will show that the records have been
filtered and the 'filtered message, i.e. 1 of 3 (filtered)' comes up
instantly.

However, I don't use the navigation buttons and instead have code to show
the record count in a text box. This works fine with one form but not
another even though the code is identical in each form in the On Current
Event.

My second form only shows one record even though there is more than one,
(and if I use the navigation buttons the 'filtered message' takes a long
time to appear). My second form always shows a record count of one when it
first opens even though there are more than one record. With other click
events it will update to the correct number. Such as when I click for next
record, it will change from 1 of 1 to 2 of 3. Why won't my recordcount work
correctly when the form is opened using On Current Event??? Does it have to
do with the delay in the filtering process??? Any way to avoid this?


' Code that opens another form from existing form
Private Sub cmdOpenWorkHistoryForm_Click()
On Error GoTo Err_cmdOpenWorkHistoryForm_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmWorkHistory"

stLinkCriteria = "[fkFirmEmployeeID]=" & Me![pkFirmEmployeeID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdOpenWorkHistoryForm_Click:
Exit Sub

Err_cmdOpenWorkHistoryForm_Click:
MsgBox Err.Description
Resume Exit_cmdOpenWorkHistoryForm_Click

End Sub


' Code that counts total amount of records in the newly opened form and
displays number in a text box.
Private Sub Form_Current()
Dim rst As Recordset

Set rst = Me.RecordsetClone
Me![txtRecordCount] = rst.RecordCount
rst.Close
End sub

 

[MacDermott]

Try this:
Set rst = Me.RecordsetClone
rst.MoveLast
rst.MoveFirst
Me![txtRecordCount] = rst.RecordCount

The rule of thumb on recordsets is: If you opened it, you close it.
Since you didn't open rst, I wouldn't close it.

HTH
- Turtle

 

[Neil Chelo]

Thanks, that worked perfectly although I am not sure on the logic of why it
is working. Exactly why do you need movelast and movefirst? Why can't you
simply skip to the recordcount?

Try this:
Set rst = Me.RecordsetClone
rst.MoveLast
rst.MoveFirst
Me![txtRecordCount] = rst.RecordCount

The rule of thumb on recordsets is: If you opened it, you close it.
Since you didn't open rst, I wouldn't close it.

HTH
- Turtle

I have a form with an on click event procedure to open up another form using
the code below. The navigation buttons will show that the records have been
filtered and the 'filtered message, i.e. 1 of 3 (filtered)' comes up
instantly.

However, I don't use the navigation buttons and instead have code to show
the record count in a text box. This works fine with one form but not
another even though the code is identical in each form in the On Current
Event.

My second form only shows one record even though there is more than one,
(and if I use the navigation buttons the 'filtered message' takes a long
time to appear). My second form always shows a record count of one when it
first opens even though there are more than one record. With other click
events it will update to the correct number. Such as when I click for next
record, it will change from 1 of 1 to 2 of 3. Why won't my recordcount work
correctly when the form is opened using On Current Event??? Does it

have

to
do with the delay in the filtering process??? Any way to avoid this?


' Code that opens another form from existing form
Private Sub cmdOpenWorkHistoryForm_Click()
On Error GoTo Err_cmdOpenWorkHistoryForm_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmWorkHistory"

stLinkCriteria = "[fkFirmEmployeeID]=" & Me![pkFirmEmployeeID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdOpenWorkHistoryForm_Click:
Exit Sub

Err_cmdOpenWorkHistoryForm_Click:
MsgBox Err.Description
Resume Exit_cmdOpenWorkHistoryForm_Click

End Sub


' Code that counts total amount of records in the newly opened form and
displays number in a text box.
Private Sub Form_Current()
Dim rst As Recordset

Set rst = Me.RecordsetClone
Me![txtRecordCount] = rst.RecordCount
rst.Close
End sub

 

[MacDermott]

As I understand it, the RecordCount is actually a count of records accessed.
Until you move to the last record, you can't be sure that all records in the
recordset have been accessed.
The MoveFirst isn't necessary to get the recordcount; it's just a
convenience because if you're going to continue to use your rst variable,
you'll probably expect it to be positioned at the first record, not the
last.

HTH
- Turtle

Thanks, that worked perfectly although I am not sure on the logic of why it
is working. Exactly why do you need movelast and movefirst? Why can't you
simply skip to the recordcount?

Try this:
Set rst = Me.RecordsetClone
rst.MoveLast
rst.MoveFirst
Me![txtRecordCount] = rst.RecordCount

The rule of thumb on recordsets is: If you opened it, you close it.
Since you didn't open rst, I wouldn't close it.

HTH
- Turtle

I have a form with an on click event procedure to open up another form using
the code below. The navigation buttons will show that the records

have
been

filtered and the 'filtered message, i.e. 1 of 3 (filtered)' comes up
instantly.

However, I don't use the navigation buttons and instead have code to show
the record count in a text box. This works fine with one form but not
another even though the code is identical in each form in the On Current
Event.

My second form only shows one record even though there is more than one,
(and if I use the navigation buttons the 'filtered message' takes a long
time to appear). My second form always shows a record count of one

when
it

first opens even though there are more than one record. With other click
events it will update to the correct number. Such as when I click for next
record, it will change from 1 of 1 to 2 of 3. Why won't my

recordcount
work

correctly when the form is opened using On Current Event??? Does it

have

to
do with the delay in the filtering process??? Any way to avoid this?


' Code that opens another form from existing form
Private Sub cmdOpenWorkHistoryForm_Click()
On Error GoTo Err_cmdOpenWorkHistoryForm_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmWorkHistory"

stLinkCriteria = "[fkFirmEmployeeID]=" & Me![pkFirmEmployeeID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdOpenWorkHistoryForm_Click:
Exit Sub

Err_cmdOpenWorkHistoryForm_Click:
MsgBox Err.Description
Resume Exit_cmdOpenWorkHistoryForm_Click

End Sub


' Code that counts total amount of records in the newly opened form and
displays number in a text box.
Private Sub Form_Current()
Dim rst As Recordset

Set rst = Me.RecordsetClone
Me![txtRecordCount] = rst.RecordCount
rst.Close
End sub