Show total entries of a table on a form

  • Thread starter Thread starter weircolin
  • Start date Start date
W

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
 
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
 
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
 
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
 
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?
 
Back
Top