Question

Locked

How to Count Records in Access VB?

By Tink! ·
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?

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Select count(*) from tablename where Item="A" {nt}

by Slayer_ In reply to How to Count Records in A ...
Collapse -

RecordCount Example.

by Blackbird "BB" In reply to How to Count Records in A ...

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

Collapse -

Thanks for that input however,

by Tink! In reply to RecordCount Example.

I had actually worked out a solution to what I was trying to do. I created a subform in the form I was needing the counted records in. Then wrote the following procedure for the On Current event in the main form.

Private Sub Form_Current()
On Error GoTo Err_Form_Current

Dim ItemCount As String

'get stkitem value in a variable
Dim strItemNo
strItemNo = Forms!fmRawsInStock!stkitem.Value

Dim strNoOfItems As String

strNoOfItems = DCount("[stkitem]", "qryGetDescrip", "[stkitem]='" & strItemNo & "'")

Forms!fmRawsInStock.subfmItemCount!itemnumbers.Value = strNoOfItems

Exit_Form_Current:
Exit Sub

Err_Form_Current:
MsgBox Err.Description
Resume Exit_Form_Current

End Sub

Collapse -

TY!

by Blackbird "BB" In reply to Thanks for that input how ...

Thanks for sharing your solution,
and Thanks for my first thumbs up!

I hope we can help each other more in the future. Blessings, BB.

Back to Software Forum
4 total posts (Page 1 of 1)  

Related Discussions

Related Forums