RecordCount problem

[Bruce75]

Hi

I have a form containing a subform. The form is based on a query. I would like it so that (1) if the user enters the form and the query returns no records, a warning appears and they are returned to the switchboard; and (2) if the query returns record(s), the user can cycle through the available records - however, I have made it so, if the user clicks a certain command button, a particular record is updated so that it no longer meets the crieria for the initial query on which the form is based. This means that whilst the user is cycling through the available records, they may come to a point where no more records are available. I would like a similiar warning to appears, as in (1), if this happens.

I have tried the code below in forms on load, on open and on current event. The msgbox reveals that the record count is wrong. I have used the movelast code after looking through this forum re: recordcount. Does anybody know what I am doing wrong? Any help greatly appreciated...

Dim rst As Object
Set rst = Me.RecordsetClone
On Error Resume Next
rst.MoveLast
On Error GoTo 0
MsgBox rst.RecordCount
If rst.RecordCount = 0 Then
MsgBox ("All MDM decisions have been recorded.")
DoCmd.OpenForm "Switchboard"
DoCmd.close acForm, "frm_aftermdm"
Else
End If

cheers,

Bruce

 

[ShaneMan]

Hey Bruce,

Below is a Public Sub that I use on several of my forms. I call it from the forms OnCurrent event (I also incorporate it into some other code on a form at times too) for the same reason that you stated about cycling through records until no records meet the criteria. I place an unbound text box control on the forms that I'm using so that's where you see the 'frm.txtRCount' part. Then when I call it I use: Call UpdateRecordCount(Me)

HTH,
Shane

Public Sub UpdateRecordCount(frm As Form)

Dim rs As Object
Set rs = frm.RecordsetClone

If rs.RecordCount > 0 Then
rs.MoveLast
rs.MoveFirst
frm.txtRCount = rs.RecordCount
Else
frm.txtRCount = rs.RecordCount
End If

rs.Close
Set rs = Nothing

End Sub

 

[Bruce75]

thanks for that.. it does the job for me!

cheers

 

[ShaneMan]

thanks for that.. it does the job for me!

cheers

Your welcome. Glad I could help.

Shane