recordsetclone not working

[CCIDBMNG]

I'm having a problem cloning the current record source of my form to check for records already entered. The code I'm using is
Dim rst as dao.recordset

set rst = me.recordsetclone

I don't get any errors but I have a msgbox(rst.recordcount) which returns 0 even though there should be 3 records in the cloned database.

Could someone please help or tell me exactly what I'm supposed to be referencing to do this. I do have a reference to Microsoft DAO 3.6 Object Library but should I be referencing something else also?

 

[Elana]

I'm no expert, but I just worked on this and was successful in returning the record count.

dim dbs as Database
dim rst as recordset

set dbs = Currentdb()
set rst = me.recordsetclone


rst.movelast

msgbox "You have " & rst.recordcount & "records."

Hope this works for you. PS - Look up RecordCount Property under Access Help for more info.

EB

[This message has been edited by Elana (edited 04-08-2002).]

 

[CCIDBMNG]

Ok, I figured out what's going on but I still need help to do what I need to do.

It counts the records that have been entered since that database was opened but it's not counting any records that were previously entered. I need to count all the records in the table that is the record source of the form not just the records that were just entered.

 

[Geoff Codd]

Heres the code I use and it works a treat

Private Sub Form_Current()
If Me.NewRecord Then
Me!lblNavigate.Caption = "New Order"
Else
With Me.RecordsetClone
Me.RecordsetClone.MoveLast
.Bookmark = Me.Bookmark
Me!lblNavigate.Caption = "Order " & _
Me.CurrentRecord _
& " of " & .RecordCount
End With
End If

End Sub

 

[CCIDBMNG]

It still doesn't work. It's only counting the number of orders that have been entered since the form was opened. Which means if I open the form there are no records and I get the error "No current record" when I movelast. Maybe I'm not referencing everything I should be referencing I don't know. But it's getting very frustrating.

 

[Elana]

Try referring to another recordset in the same procedure that looks at the table as a whole and counts those records.

dim rst2 as recordset

rst2 = ("tblYourTable", dbopentable)

rst2.movelast

msgbox "You have " & rst2.recordcount & "records in the table"



[This message has been edited by Elana (edited 04-08-2002).]

 

[CCIDBMNG]

That worked perfect Elana thanks, I was under the impression that you couldn't do that with the table that was set as the forms record source. Thanks again.