Record Count of Variable Input

hollyneal

Registered User.
Local time
Today, 05:54
Joined
Jan 5, 2012
Messages
21
I don't know what I'm doing yet and maybe I'm going about this the wrong way. I'm trying to come up with a count of records meeting variable criteria.

My code is:

Code:
Sub TestGetInput()
    DoCmd.OpenForm "InquireWithin", acNormal
    Dim TrackingDB As Object
    Dim TrackingFields As Object
    Dim fldCounter As Object
    Dim fldColumns As Object
    Dim RcdCount As Integer
    RcdCount = 0
    Set TrackingDB = CurrentDb
    Set TrackingFields = TrackingDB.OpenRecordset("Tracking")
    Set fldColumns = TrackingFields.Fields
    ' Scan the records from beginning to each
    While Not TrackingFields.EOF
        ' Check the current column
        For Each fldCounter In TrackingFields.Fields
            ' [COLOR=blue]I want the user to put this in from the form[/COLOR]
            If fldCounter.Name = "CallType" Then
                ' [COLOR=blue]I want the user to put this in from the form[/COLOR]
                If fldCounter.Value = "Transfer" Then
                    ' then add to the record count
                    RcdCount = RcdCount + 1
                End If
            End If
        Next
        ' Move to the next record 
        TrackingFields.MoveNext
    Wend
End Sub

What I want is to have a form where the user inputs fldCounter.Name and fldCounter.Value instead of using the hard-coded inputs I've got, and have the record count (of those meeting the criteria) displayed on the form.

I'm trying to teach myself Access and I'm somewhat bewildered. Any suggestions would be greatly appreciated!
 
For starters, does this query return the same result as the above?

SELECT Count(*) AS HowMany
FROM Tracking
WHERE CallType = "Transfer"

If so, making it dynamic would be fairly simple, and it would be MUCH more efficient than what you have.
 
Well it returns the correct answer! (I'm still not sure what the VB code returns because the rcdCount variable doesn't have a value after the code finishes. I couldn't figure out what to do with it to see it besides put a watch on it.)

I was trying to use SQL to do the same thing but I couldn't figure that out either.

Is there an Access SQL reference somewhere I could read?
 
Well, two ways to test your existing code would be

MsgBox RcdCount
Debug.Print RcdCount

The latter would output the value to the VBA Immediate window. What's the overall goal of what you're trying to do?
 
Well I'm trying to take baby steps. My ultimate goal is to be able to take an inquiry from a form and display all the records that match, along with the total. I would like the user to be able to choose which field and the value of that field to search for. I'm trying to teach myself so I know how to do this again some other time...
 

Users who are viewing this thread

Back
Top Bottom