RecCount in Form Text Box

I searched for count in this forum and read all the resulting posts. None do exactly what I want and I am not proficient in the VB codint to edit them to fit my needs.

I have a main form Dispatch which I need a text box to count the dispatches for the current month. I have a querry that lists the dispatches for this month. I need to display the record count for that querry in the text box on the main form. How do I update the querry after each new entry?

I don't understand clone and other terminology in the previous posts. Does Me. stand for memory? So, if possible KISS (keep it short and simple).

So Much to learn, So Little Time

realnine, I can suggest two ways of going about this.

The first is to open the query that you have already designed programatically, using ADO or DAO, check the recordcount of your query, then update the field on your form to reflect the recordcount.

The other way is to use the DCount function. It's pretty slow to calculate, usually, and since you've already got a functioning query to work with, I'd go with solution one.

Let me know if you're using DAO or ADO and I'll suggest some code for you.

DCX693,
I am new and ignorant to Access that I don't know what DAO orADO mean. I searched Access help for these with no help. The querry has at eht bottom n number of records if n2 records. Thanks for your patience.

RealNIne

No problem. If you're using Access 97, chances are you're using DAO (stands for Data Access Objects), the data access technology that is most efficient (according to some) when working with plain mdb Access files.

ADO, introduced in Access 2000, stands for ActiveX Data Objects is is now Microsoft's preferred data access technology. If you're using Access 2000+, it's the default.

You don't mention which version of Access you're using so I'll write code for both. Just adapt the code to the names of the objects in your database and place it into the appropriate event, perhaps after you make a selection from a combo box, place the text into the After Update event.

If you're using Access 97, assuming the text box on your form is called txtCountDispatches and assuming your existing query is called "qry":


Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("qry")
rst.MoveLast
Me.txtCountDispatches = rst.RecordCount
Set rst=Nothing

If you're using Access 2000+, then try this:

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Open "qry", , adOpenKeyset, , adCmdTable
Me.txtCountDispatches = rst.RecordCount
rst.Close
Set rst = Nothing

Thanks dcx693,
I use Access 2002 and I could not get the code you wrote to work. I think I had problems with identifying the querry. I have to become more knowledgeable in VBA code before I can start debugging. I plan a trip to the book store when the SS check comes in. Thanks for your interest and effort.

I did find a solution:

in the properties of the text box, under the data tab,control source I put this:
=DCount("[DispatchDate]","macMonthlyView")
DispatchDate is a mandantory field so I don't worry about nulls

I works!

Hi dcx693,
I finnaly did get the code to work. I had to put the qry in brackest, i.e. "[qry]". If I could impose on your knowledge, I would like to understand how the code works.

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Open "qry", , adOpenKeyset, , adCmdTable
Me.txtCountDispatches = rst.RecordCount
rst.Close
Set rst = Nothing

The first line defines the varible rst as a record set
The second line assigns rst to a new recordset
I don't know what the third line does, I suspect it links the new recordset to the form.
The fourth line opens the querry and adds it to rst even thouogh I don't know what adOpenKeyset and adCmdTable do.
The next line puts the RecordCount in the text box.

Why can I not open the querry and get its RecordCount directly and then set the textbox equal to it.

If you don't have time to answer I understand. Thanks again for the code you provided, it runs a lot faster that the DCount approach.

So much to know, So little time