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