Parse UDT as parameter (1 Viewer)

winshent

Registered User.
Local time
Today, 10:45
Joined
Mar 3, 2008
Messages
162
I have a User Defined Type which stores an array..

I have 2 instances of this UDT, one storing matched data, the other storing exception data

I was to parse these arrays to an Excel output routine. However, the parameter variable contains no data when accessing it..

Here is my code:

UDT:
Code:
Private Type TOutputRow
    RACFID As String
    FullName As String
    Access As String
    LastLoggedIn As Date
End Type

Private Type TOutputRows
    Count As Long
    OutputRow() As TOutputRow
End Type

Private mudtOutputRowsMatched As TOutputRows
Private mudtOutputRowsExceptions As TOutputRows


UDT Array selector:
Code:
Public Sub gOutputToExcel(pstrData As String)

    Select Case UCase(pstrData)
    
        Case "MATCHED"
            mOutputToExcel mudtOutputRowsMatched

        Case "EXCEPTIONS"
            mOutputToExcel mudtOutputRowsExceptions
    End Select

End Sub

Excel output sub:
Code:
Private Sub mOutputToExcel(pudtOutputRows As TOutputRows)

	'excel output code

end sub

Any ideas ?
 

spikepl

Eledittingent Beliped
Local time
Today, 11:45
Joined
Nov 3, 2010
Messages
6,144
So the assignment of data is where?
 

winshent

Registered User.
Local time
Today, 10:45
Joined
Mar 3, 2008
Messages
162
The assignment of data is within another block of code:

Code:
If !RACFException = 0 Then

    j = j + 1
    mudtOutputRowsMatched.Count = mudtOutputRowsMatched.Count + 1
    ReDim Preserve mudtOutputRowsMatched.OutputRow(j)
    mudtOutputRowsMatched.OutputRow(j - 1).FullName = !FullName
    mudtOutputRowsMatched.OutputRow(j - 1).Access = mudtProfiles.Profile(i).Access
    mudtOutputRowsMatched.OutputRow(j - 1).RACFID = Left(strRACFID, 4)
    
    If Nz(!LastLoggedIn, vbNullString) <> vbNullString Then
        mudtOutputRowsMatched.OutputRow(j - 1).LastLoggedIn = !LastLoggedIn
    End If

Else

    k = k + 1
    mudtOutputRowsExceptions.Count = mudtOutputRowsExceptions.Count + 1
    ReDim Preserve mudtOutputRowsExceptions.OutputRow(k)
    mudtOutputRowsExceptions.OutputRow(k - 1).FullName = !FullName
    mudtOutputRowsExceptions.OutputRow(k - 1).Access = mudtProfiles.Profile(i).Access
    mudtOutputRowsExceptions.OutputRow(k - 1).RACFID = Left(strRACFID, 4)
    
    If Nz(!LastLoggedIn, vbNullString) <> vbNullString Then
        mudtOutputRowsExceptions.OutputRow(k - 1).LastLoggedIn = !LastLoggedIn
    End If

End If
 

spikepl

Eledittingent Beliped
Local time
Today, 11:45
Joined
Nov 3, 2010
Messages
6,144
You keep showing snippets, which makes it hard to guess whether the variables are in scope or not and what actually is executed. Are they in scope? Is all that stuff in one module?

Put a trace on and see when they "lose" their values, or step through with debugger.
 

winshent

Registered User.
Local time
Today, 10:45
Joined
Mar 3, 2008
Messages
162
You keep showing snippets, which makes it hard to guess whether the variables are in scope or not and what actually is executed. Are they in scope? Is all that stuff in one module?

Put a trace on and see when they "lose" their values, or step through with debugger.

Yes its all in the same module so is all in scope...

I have stepped through the debugger... Like I say, the parameter pudtOutputRows within mOutputToExcel does not contain any data when loaded..


Let's see the full blocks of code.

Here it is..
Code:
Public Function gblnProcessData(ByRef pobjConn As ADODB.Connection) As Boolean
    On Error GoTo PROC_ERR

    Dim objRst As ADODB.Recordset
    
    Dim strRACFID       As String   ' store RACFID as variable. Used to determine if RACFID is same previous RACFID
    Dim strRole         As String   ' store concatenated string of role

    Dim i               As Integer
    Dim j               As Integer
    Dim k               As Integer
    Dim z               As Integer
    
    Dim blnReturn As Boolean
    blnReturn = False
    
    Dim strSQL As String
    
    Dim arrSQL(2)       As String
    
    Set objRst = New ADODB.Recordset
    
    ' RACF exception data
    strSQL = ""
    strSQL = strSQL & "SELECT I.RecordID, I.RACFID, I.FullName, I.Role, I.PermissionString, I.Access, I.MaxSequence, I.Hash, I.LastLoggedIn, 1 AS RACFException " & vbNewLine
    strSQL = strSQL & "  FROM Import AS I INNER JOIN RACFExceptions ON I.RACFID = RACFExceptions.RACF " & vbNewLine
    strSQL = strSQL & " ORDER BY I.RACFID, I.Role "
    arrSQL(0) = strSQL
    
    ' Matched data
    strSQL = ""
    strSQL = strSQL & "SELECT I.RecordID, I.RACFID, I.FullName, I.Role, I.PermissionString, I.Access, I.MaxSequence, I.Hash, I.LastLoggedIn, 0 AS RACFException " & vbNewLine
    strSQL = strSQL & "  FROM Import as I " & vbNewLine
    strSQL = strSQL & " WHERE RACFID NOT IN(SELECT RACF FROM RACFExceptions) " & vbNewLine
    strSQL = strSQL & " ORDER BY I.RACFID, I.Role "
    arrSQL(1) = strSQL
           
    strSQL = ""
    strSQL = strSQL & "SELECT RACFID, MaxSequence " & vbNewLine
    strSQL = strSQL & "FROM Import " & vbNewLine
    strSQL = strSQL & "ORDER BY RACFID, Role "
            
    ' Set MaxSequence to identify last record with full permission string
    objRst.Open strSQL, pobjConn, adOpenDynamic, adLockOptimistic
    
    If objRst.BOF Or objRst.EOF Then
        MsgBox "No data has been imported !", vbCritical, "NO DATA !"
    Else
    
        With objRst
            
            .MoveFirst
            Do Until .EOF
                If (strRACFID <> "" And strRACFID <> !RACFID) Or .EOF Then
                    .MovePrevious
                    !MaxSequence = True
                    .MoveNext
                End If
                               
                .MoveNext
                If .EOF Then
                    .MovePrevious
                    !MaxSequence = True
                Else
                    .MovePrevious
                End If
                
                strRACFID = !RACFID
                .MoveNext
            Loop
        End With
        
        objRst.Close
              
        ' reset udt's
        If blnLoadProfiles Then
            ReDim mudtOutputRowsMatched.OutputRow(0)
            mudtOutputRowsMatched.Count = 0
            ReDim mudtOutputRowsExceptions.OutputRow(0)
            mudtOutputRowsExceptions.Count = 0
            
            For z = 1 To UBound(arrSQL)
            
                objRst.Open arrSQL(z - 1), pobjConn, adOpenDynamic, adLockOptimistic
            
                With objRst
                    If Not .BOF And Not .EOF Then
                        .MoveFirst
                                            
                        Do Until .EOF
                            If strRACFID <> !RACFID Then
                                ' New RACFID
                                
                                strRACFID = Trim(!RACFID)
                                
                                strRole = Trim(!Role)
                                !PermissionString = strRole
                                
                                If !MaxSequence = True Then
                                    !Hash = modMD5.MD5_string(strRole)
                                End If
                                
                            Else
                                ' RACFID same as above record
                                
                                ' Build role string, concatenate from previous record
                                strRole = strRole & "|" & Trim(!Role)
                                !PermissionString = strRole
                                
                                If !MaxSequence = True Then
                                    !Hash = modMD5.MD5_string(strRole)
                                End If
                                                                                  
                            End If
                            
                            .Update
                        
                            ' match users against profile table
                            For i = 0 To mudtProfiles.Count - 1
                            
                                If !Hash = mudtProfiles.Profile(i).Hash Then
                                    !Access = mudtProfiles.Profile(i).Access
                                    
                                    If !RACFException = 0 Then
                                    
                                        j = j + 1
                                        mudtOutputRowsMatched.Count = mudtOutputRowsMatched.Count + 1
                                        ReDim Preserve mudtOutputRowsMatched.OutputRow(j)
                                        mudtOutputRowsMatched.OutputRow(j - 1).FullName = !FullName
                                        mudtOutputRowsMatched.OutputRow(j - 1).Access = mudtProfiles.Profile(i).Access
                                        mudtOutputRowsMatched.OutputRow(j - 1).RACFID = Left(strRACFID, 4)
                                        
                                        If Nz(!LastLoggedIn, vbNullString) <> vbNullString Then
                                            mudtOutputRowsMatched.OutputRow(j - 1).LastLoggedIn = !LastLoggedIn
                                        End If
                                    
                                    Else
                                    
                                        k = k + 1
                                        mudtOutputRowsExceptions.Count = mudtOutputRowsExceptions.Count + 1
                                        ReDim Preserve mudtOutputRowsExceptions.OutputRow(k)
                                        mudtOutputRowsExceptions.OutputRow(k - 1).FullName = !FullName
                                        mudtOutputRowsExceptions.OutputRow(k - 1).Access = mudtProfiles.Profile(i).Access
                                        mudtOutputRowsExceptions.OutputRow(k - 1).RACFID = Left(strRACFID, 4)
                                        
                                        If Nz(!LastLoggedIn, vbNullString) <> vbNullString Then
                                            mudtOutputRowsExceptions.OutputRow(k - 1).LastLoggedIn = !LastLoggedIn
                                        End If
                                    
                                    End If
                                    
                                    Exit For
                                End If
                            Next i
                                              
                            .MoveNext
                        Loop
                    End If
                End With
                
                objRst.Close
                
            Next z ' iterate thru both pieces of SQL
        
            blnReturn = True
        End If ' blnLoadProfiles
    
    End If ' test if data has been loaded
    
PROC_EXIT:
    On Error Resume Next
    
    objRst.Close
    Set objRst = Nothing
        
    gblnProcessData = blnReturn
    Exit Function
    
PROC_ERR:
    MsgBox "Error occurred in modProcess.gblnProcessData() : " & Err.Number & vbNewLine & Err.Description
    Resume PROC_EXIT
    Resume

End Function
 

winshent

Registered User.
Local time
Today, 10:45
Joined
Mar 3, 2008
Messages
162
This is now fixed... Turns out the code was working, it was an issue with the excel output code.. sorry for wasting your time guys...

Marked as solved..
 

Users who are viewing this thread

Top Bottom