Show total entries of a table on a form

[weircolin]

Hi

I have my main database table and queries from it for different fields.

Is it possible for me to display the amount of entries selected by a
query in a text box on a form?

Cheers

Colin

 

[Nick 'The database Guy']

Hi Colin,

I can do better than that!

This routine also shows you where you are in the records.

' Gives the current record count and where you are in it
Private Sub Form_Current()
Dim rs As DAO.Recordset
Dim lngCount As Long
Set rs = Me.RecordsetClone
With rs
.MoveFirst
.MoveLast
lngCount = .RecordCount
End With
Me.txtRecordNumber = "Event number " & .CurrentRecord & " of "
& lngCount & " events"
End Sub

You must create a text box called txtRecordNumber and the code must be
placed behind the form.

Hope this helps.

Good luck,

Nick

 

[weircolin]

Thanks Nick

I don't know if it'll work for what I'm trying to do. Basically I am
trying to create a form that will show just the amount of entries on
different tables if that makes sense.

ie. I have a table called "Newsletter Database" and one called "Issues
and Concerns". I would like a form to show me that there are x number
of entries on the Newsletter Database and y on the Issues and Concerns.

Cheers

 

[Nick 'The database Guy']

Hi Colin,

This is even easier in that case, try the following code:

Dim SQL as String
Dim rs as New.adodb recordset
Dim lngCount as Long
sql = "SELECT * FROM tblAnyTable"
with rs
.Open sql, CurrentProject.AccessConnection, adOpenForwardOnly,
adLockOptimistic
.movelast
lngCount = .recrodcount
end with
me!textbox = lngCount

OK now?

Nick

 

[weircolin]

Hi Nick

Thanks for the code. Much appreciated.

Getting compile errors on the lines

Dim rs as New.adodb recordset
and
..Open sql, CurrentProject.AccessConnection, adOpenForwardOnly,

Also, where you have the tblAnyTable, could I put the name of a query
there?