Member Avatar for lovelydan

i want to design a module to count each assets on a table so i can generate an aggregated data report of for each item. this is where i got stuck:Help!

Private Sub countAssets()
     Dim mydb As Database, MysetAssets As Recordset, MysetSoftware As Recordset
        Set mydb = DBEngine.Workspaces(0).Databases(0)
    '............................................................................
     
        Set MysetAssets = mydb.OpenRecordset("qryAssetDetails", DB_OPEN_DYNASET)
        Set MysetSoftware = mydb.OpenRecordset("qrySoftwareDetails", DB_OPEN_DYNASET)
     
        Dim assName As Field, assStatus As Field, assCondition As Field
        Dim countName As Integer, countStatus As Integer, countCondition As Integer, Found As Boolean
        Dim foundName As String, foundStatus As String, foundCondition As String
     
        Set assName = MysetAssets![equipCatName]
        Set assStatus = MysetAssets![equipStatus]
        Set assCondition = MysetAssets![assCondition]
        countName = 0
        countStatus = 0
        countCondition = 0
        Found = False
     
        MysetAssets.MoveFirst
        Do Until MysetAssets.EOF
            Case assName <> ""
            Found = True
            Dim strWhere As String
     
        strWhere = "(foundname=#" & assName & "#) AND " & _
                   "(foundstatus='" & assStatus & "')"
     
     
       countname = countname + 1
     
            End Select
        Myset.MoveNext
        Loop
    End Sub

i just have a table to store different assets in a field called assName eg; CPU, Monitor, UPS, Table...etc. so what i want to do is to design a module that can counts each item separately. for example i used to use a module below to do this.
this module worked just fine the problem is, i need to amend it if a new item is introduced so i need to design a module which will just move to the first record in a table and finds items and start counting without being amended.

hope this will give you enough information. my regards

Do Until Myset.EOF
            Select Case AssetName
                Case Is = "CPU"
                    countCPU = countCPU + 1
                Case Is = "Monitor"
                    countMonitor = countMonitor + 1
                Case "UPS"
                    countUPS = countUPS + 1
                Case Is = "Kettle"
                countKettle = countKettle + 1
                Case Is = "Book"
                countBook = countBook + 1
                Case Is = "TV"
                countTV = countTV + 1
                Case Else
                    countothers = countothers + 1
        End Select
        Myset.MoveNext
        Loop
  • 2 Contributors
  • 3 Replies
  • 110 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by BitBlt

Dani AI

Generated

opened this thread asking for a way to count each asset type without hardcoding every possible name, and correctly pointed out that doing the aggregation on the database side is the cleanest, fastest solution. If you must do it in VBA (for example to do extra processing before storing results), use a dictionary-style tally so the code discovers categories at runtime instead of requiring a Case block for every new asset.

The example below uses late-bound Scripting.Dictionary so it runs without adding a reference. It reads each record, normalizes the category text (trim + case), and increments a dynamic counter. After the pass you can print the results, write them to a summary table, or feed them to a report.

Sub CountAssetsDynamic()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim dict As Object
    Dim key As String

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("SELECT Category FROM tblAssets", dbOpenSnapshot)
    Set dict = CreateObject("Scripting.Dictionary")

    Do While Not rs.EOF
        key = Trim$(rs!Category & "")
        If Len(key) > 0 Then
            key = UCase$(key) ' normalize case
            If dict.Exists(key) Then
                dict(key) = dict(key) + 1
            Else
                dict.Add key, 1
            End If
        End If
        rs.MoveNext
    Loop

    rs.Close
    Set rs = Nothing
    Set db = Nothing

    Dim k As Variant
    For Each k In dict.Keys
        Debug.Print k & vbTab & dict(k)
    Next
End Sub

Notes and troubleshooting:

  • Normalize text (Trim, UCase/LCase) so "CPU", "cpu " and "Cpu" count together.
  • Watch NULLs and empty strings; handle them explicitly.
  • For large tables or permanent summaries, push aggregation to the database (as suggested) or create/maintain a summary table via a single aggregate query for best performance.
  • If persisting counts, use a transactions/recordset AddNew or SQL INSERT/UPDATE to avoid leaving partial results.

Recommended Answers

All 3 Replies

Member Avatar for BitBlt

Frankly, you'd be better off spending your time designing an aggregation (i.e. "Group By") query in SQL on the database side. It would be more efficient and probably perform better. What database management system are you using?

Member Avatar for lovelydan

inventory management system, would you give me a light on that...im not good with queries either, im just trying to develop a small DB 4 counting different categories of assests. an example would do..

Member Avatar for BitBlt

It could look something like this:

select assetname, count(*) 
from assettable
group by assetname

Keep in mind that the names I chose above are made-up, because I don't know what your data structures look like. You'll have to do some investigating to find the actual names in your own environment. Maybe you should get the help of some friendly database administrator or senior developer where you are.

Edited by BitBlt because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.