How to Count Records in Access VB?

Clarifications

nt

How to Count Records in Access VB?
I have an Item "A" which, for each unit in stock has its own record.

How do I create a form with a field that upon opening the form, will search for Item "A", count how many are in stock, then display that number in the field?

Ok Tink,
You have a couple things you want to do here.

First create a Query looking at your larger data set using the Query Wizard.
in the Criteria Gird set under the general heading Widget enter ="A"

If you need the number of records for the whole dataset this is of course unnecessary.
Ok now use the form wizard using your Query qryTink as the recordsource.

when you open the form the number at the bottom would be the number of records where Widget="A"
but I suspect you need something a little better than that.

Your going to want to ad to your form a module that runs when the form opens
I'm writing the value to a Label but you can easily adapt the code to a text box.

I expect you'll find it looks something like this.

Private Sub Form_Open(Cancel As Integer)
Call CountWidgets
End Sub

Private Sub CountWidgets()

Dim db As DAO.Database
Dim DRS As DAO.Recordset
Dim iWidgets As Long, strWidgets As String
Dim stDocName As String

Set db = CurrentDb
stDocName = "qryTink"
Set DRS = db.OpenRecordset(stDocName, dbOpenDynaset)

'Record count wont give you a true count till you've
'scanned all the records so this bit of code is truly
'needed.

DRS.MoveLast
DRS.MoveFirst

iWidgets = DRS.RecordCount

Let lblWidgets.Caption = iWidgets

DRS.Close
Set DRS = Nothing

db.Close
Set db = Nothing

Exit Sub

End Sub