Full Version: How Would I Craft This Record Set
UtterAccess Forums > Microsoft® Access > Access Forms
Hey all,
I am building a pretty stock standard software register and have ground to a halt with the last totals I need to determine.
The form is pretty straight forward, a combo to select the product, this displays the main product information. A subform for any maintenance we have on that product and another form which displays all the variants we have of the product and what is active or upgraded (the last part is the key info).
Oneed to show the total we have for a particular product (i.e. Photoshop CS4). I have to show which we have upgraded and which are still active (all controlled by a status).
So my subform for Photoshop CS4 looks like this.
Photoshop CS4 Active 72
Photoshop CS4 Upgraded 29
Photoshop CS4 Extended Active 19
Photoshop CS4 Extended Upgraded 6
at the bottom of the subform I display a total Photoshop CS4 pool we have ever purchased, but what I want to do is on the main form in the general product info for CS4 is show how many seats we can use out of the original pool. So with this data it would be that we bought 126 seats of Photoshop CS4 and we can use a maximum of 91.
I am sitting here staring at my query that has the output I need and know some sort of recordset should come into play that will write a temp count of the lines with 'Active' as the status and then at the end of the recordset loop display that as the count.
I just cannot put it together.
Can anyone offer me up some assistance?
Many thanks
Is there some sort of flag field that determines if a maintenance is "active"?
FOr is it just the text of the entry (the active ones). That seems a little flakey to me to be honest. (Well... very actually.)
You can use the subform easily enough in either eventuality (I hate unnecessary fetches to the database when the data is already loaded right there ;-)
But it depends upon how you identify a row as active.
the product status is driven from the asset system where the data comes from..
ngStatusDescription - A (active) or U (upgraded).
these are the two status's that I will be pumping through the recordset, stripping out the A and the total for that product to come up with the total available products variants within a main product range.
A field called lngStatusDescription holds a value of either "A" or "U" ??
(The "lng" prefix would usually indicate a Long data type - not text as this seems to be holding.)
nyway - such a field then would allow you to sum the rows which conform to that status? The field would need to be included in the subform's recordsource of course.
You can add a general function like this to a standard module...
Function fSumForm(pfrm As Form, pstrField As String, Optional strConditionField As String, Optional varConditionValue)
On Error GoTo HandleErr
    With pfrm.Recordset.Clone
        If Not (.BOF And .EOF) Then .MoveFirst
        Do Until .EOF
            If strConditionField > "" Then
                If .Fields(strConditionField) = varConditionValue Then
                    fSumForm = fSumForm + Nz(.Fields(pstrField), 0)
                End If
                fSumForm = fSumForm + Nz(.Fields(pstrField), 0)
            End If
    End With
    Exit Function
    If Err = 7951 Then
        'Form has no source yet
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in " & "" & ".fSumForm", vbExclamation
    End If
    Resume exitHere
End Function

Your expression would then be something like
lngTotal = fSumForm(Me.sfmSubform.Form, "QuantityField", "lngStatusDescription", "A")
That's the idea anyway.
Thanks for the info, I will give it a try.
#39;lngStatusDescription' that is what was there when I got hold of it. The whole name doesn't make sense, yes it is a text field and it is not even a description it is just an identifer (txtStatusCode) would be something better but I just work with what is already embedded in the system I need to work with.
Fair enough, see how you get on. Maybe in years to come you'll feel more empowerd and will be able to take complete ownership of the databases you're given (or hopefully you'll just be building your own ;-)
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.