Count the records in a table to use value 2

[Umbane]

Open my form with all records, then after pressing a button I want to calculate programmatically how many records there are so that I can use that number in a do loop statement

 

[drctx]

something like this would work.

Private Sub Command3_Click()
On Error GoTo Err_Command3_Click
Dim PTdb As DAO.Database, PTrs As DAO.Recordset, PTsql As String

Set PTdb = CurrentDb()
PTsql = "SELECT * FROM DBnames;"
Set PTrs = PTdb.OpenRecordset(PTsql)
PTrs.MoveFirst
PTrs.MoveLast

MsgBox "record count is: " & PTrs.RecordCount

If PTrs.RecordCount = 0 Then
'do something
Else
'do something else
End If

Exit_Command3_Click:
DoCmd.SetWarnings True
Exit Sub

Err_Command3_Click:
If Err.Description = "No current record." Then
Resume Next
Else
MsgBox Err.Description
End If
Resume Exit_Command3_Click
End Sub

 

[PHV]

Me.RecordsetClone.RecordCount

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Umbane]

Thanks guys, I think I got it. Code now looks like this:

Forms!Orders.RecordsetClone.MoveLast
MsgBox "My form contains " _
& Forms!Orders.RecordsetClone.RecordCount _
& " records.", vbInformation, "Record Count"

Just replace the red text with whatever form name you are using.