Issue with accessing dictionary/array/recordset from multiple procedures

[Outlier]

EDIT: Ignore any typos in the code field names - I changed them to preserve confidentiality (lame yes, but I have to). The logic of the code is the problem. Thanks!

Here is what my project is:

1) I query a database for a list of employees depending on criteria that the user inputs.
2) The recordset is then displayed in a list box.
3) The user can then select the desired employees from the list box (lets say they choose 30 out of 60 that are returned by the SQL statement).
4) After the user has selected the 30 employees he/she then clicks a command button to return their employee ID's into an array/dictionary to be used later to perform an action in a compeltely different sub/function.

THIS IS WHERE IT FAILS. I can access the dictionary from within this sub, but after I loop through the recordset and dump it into a dictionary object it dies when the sub dies. I MUST be able to access it at a later point when the user clicks another button (this other button interacts with a third party program and passes the employee IDs to it). I really can't wrap my head around how to do this... I was thinking I may need to create a class? even then that may be another monster to take on... any help is much appreciated..

Any thoughts?

Here is what I have so far:

Set dctEmployeeID = New Dictionary
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
 
rs.Open "SELECT EmployeeLastName,EmployeeFirstName, ManagerName,EmployeeID FROM tblEmployeeInfo " & _
"WHERE EmployeeID <> '' AND Status='current' 
ORDER BY EmployeeLastName", conn, adOpenForwardOnly, adLockReadOnly
 
Do While Not rs.EOF
    Form.lstEmployees.AddItem rs!EmployeeLastName.Value & rs!EmployeeFirstName.Value & " (" & rs!ManagerName.Value & ")"
    strEmployee = rs!AgentLastName.Value & rs!AgentFirstName.Value & rs!TMName.Value & rs!Location
    strEmployee = Replace(strEmployee, " ", "")
    'this part puts the concatenation of lastname, firstname, managername, and associates it with their employee ID which will be used later on in another function/sub
    dctEmployeeID.Add UCase(strEmployee), rs!EmployeeID.Value
    rs.MoveNext
Loop
 
rs.Close
conn.Close
Set conn = Nothing
Set rs = Nothing

 

[gemma-the-husky]

what format do you need them to be in , in order to use them later ? - eg

table
csv
txt listing
internal array

 

[ChrisO]

There are many things that could be wrong…

You do not say if and/or where dctEmployeeID is defined.
If it’s not defined then it will be local.
If it is defined and the definition is local then it will go out of scope.
You could define it publicly and that should be okay.

You can also define a Function as datatype Dictionary and use the Function Name to load and retrieve the Dictionary entries.

DAO example:-

Option Explicit
Option Compare Text


[color=green]' A reference is required to:    Microsoft Scripting Runtime[/color]

Sub TestIt()
    Dim vntKeyIndex As Variant

    With FillEmployeeDictionary
        For Each vntKeyIndex In .Keys
            Debug.Print "Key: " & vntKeyIndex & "  Item: " & .Item(vntKeyIndex)
        Next
    End With

End Sub


Public Function FillEmployeeDictionary() As Dictionary
    Dim strEmployeeKey As String

    Set FillEmployeeDictionary = New Dictionary
        
    With CurrentDb.OpenRecordset(" SELECT EmployeeLastName," & _
                                        " EmployeeFirstName," & _
                                        " ManagerName," & _
                                        " EmployeeID," & _
                                        " Status" & _
                                 " FROM tblEmployeeInfo" & _
                                 " WHERE EmployeeID <> ''" & _
                                 "   AND Status='current'" & _
                                 " ORDER BY EmployeeLastName")

        Do Until .EOF
            [color=green]' Assemble the Employee Key.[/color]
            strEmployeeKey = !EmployeeLastName & !EmployeeFirstName & !ManagerName
            strEmployeeKey = Replace(strEmployeeKey, " ", "")
            
            [color=green]' This part puts the concatenation of LastName, FirstName, ManagerName,
            ' and associates it with their employee ID which will be used later on in another function/sub.
            '
            ' The Employee Key must be unique; for the moment we will ignore duplications.[/color]
            On Error Resume Next
                FillEmployeeDictionary.Add UCase(strEmployeeKey), CStr(!EmployeeID)
            On Error GoTo 0
            .MoveNext
        Loop

    End With
    
End Function

Chris.

 

[LPurvis]

Hi

Just passing through but I wanted to observe a thing or two.
First of all, you're opening a recordset, essentially pulling over every active Employee record. Fair enough - you want the user to select from any of those.
You're doing so in an ADO recordset - again fair enough (and actually could be of benefit here).

You use that recordset to iteratively fill your list control with the Employee records. If this list isn't particularly long then you'll find that a pretty quick and effective process. However if you're using anything newer than Access 2000 then you can bind the recordset directly to the list control.
Set Me.lstEmployees.Recordset = rs
(The listbox would need to be in Table/Query row source type mode - as its data bound, not list value driven as it is for AddItem additions.)

There's definitely some code missing - as you declare a connection, but don't assign or open it. Is this an external table or linked? i.e. you're using
Set conn = CurrentProject.Connection
at some point?

You then fill a Dictionary object, similarly as the listbox - and it's this dictionary you're wanting to subsequently refer to.
Chris has pointed you to the concept of both scope of variables and a function you can use ad hoc to call that data.
I agree with the concept - but use it sparingly.
You're pulling over close to a full table's worth of rows. Try to pull it once and re-use that loaded data.
I get the impression that's what the Dictionary use is all about, and that's commendable. Just somewhat unnecessary.
If you push your recordset scope such that it is persisted - or have that function call and return the recordset directly - you can use that recordset instead of the Dictionary middleman.
If you make your recordset client side then it will a) bind to the listbox more reliably and b) be able to be disconnected, so that you're holding that table data in memory without a connection to the database being persisted by the recordset still being open.

Just to be clear, that would make your code

rs.CursorLocation = adUseClient 'Set up client side
rs.Open "SELECT EmployeeLastName,EmployeeFirstName, ManagerName,EmployeeID FROM tblEmployeeInfo " & _
"WHERE EmployeeID <> '' AND Status='current'
ORDER BY EmployeeLastName", conn, adOpenStatic, adLockReadOnly 'Static because it's client side
Set rs.ActiveConnection = Nothing 'Disconnect the recordset
Set Me.lstEmployees.Recordset = rs 'Assign to the list control

I agree that a class would be slight overkill for this. How you hold selected items is still up for grabs.
What you can do is shape another field into the recordset such that you could use that for marking rows as selected. Or you could simply maintain a delimited list.
You can then still sort and filter the recordset - all useful functionality that isn't available in standard collection, arrays and structures - like that of the Dictionary even.

Just something to think about.

Cheers.

 

[ChrisO]

Well, a few extra thoughts on the subject…

--------------------------

Curious Dictionary:-
To illustrate my point let’s use named arguments with the Dictionary:-
FillEmployeeDictionary.Add Key:=UCase(strEmployeeKey), Item:=CStr(!EmployeeID)

strEmployeeKey is the Key and must be unique, Item need not be unique.
strEmployeeKey is a composite Key which might not be unique.
By the look of its name EmployeeID should be unique even if it’s a string.

So, in order to identify a unique Item we are using a, possibly, non-unique Key.
I’ve been wrong before but that seems backwards to me.
That was the reason for the On Error Resume Next in my code; uniqueness may have been compromised.

It seems better to me to reverse the Keys and Items as in:-
FillEmployeeDictionary.Add Key:=CStr(!EmployeeID), Item:=UCase(strEmployeeKey)
or, if we don’t want to force !EmployeeID to a String (which it may not be) then:-
FillEmployeeDictionary.Add Key:=!EmployeeID.Value, Item:=UCase(strEmployeeKey)

--------------------------

Duality of purpose:-
The code in post #1 does two things and I think that is one too many.
First it fills a List Box and then it Fills a Dictionary, one is not the other.
To my mind, a procedure should serve only one master.
In this case it should either fill the List Box XOR fill the Dictionary.
If both are needed then that requires two procedures.

--------------------------

Persistence:-
To be quite honest I have not used persistent data very much.
In a database environment I have not, knowingly, used it at all if the data could change.
(Even Value Lists like ‘January; February; March’ etcetera can change.)

However, I’m coming at this subject from a completely different angle.
Persistence, to me, seems to imply get once use many.
Indeed, it makes sense from the point of view of reducing network traffic; why, potentially, get the same data if it hasn’t changed?

Very loosely one could answer that question with; Schrödinger's cat.
How do we know the data hasn’t changed without hitting the network?

Example 1:
From my area of past employment:-
An oil refinery may use exception processing to attempt to reduce network traffic.
In essence, if a value in the field does not change by more than +/- X from last transmission then do not transmit the new value.
This makes perfect sense from the point of view that if a change is outside the range of our ‘need to know’ then why create needless network traffic only to find out we didn’t need to know it?

So the network is not over taxed and runs quietly doing its job.
Then, one day, a plant fault occurs. The plant is a series production and up-stream processes reflect badly on down-stream processes.
It’s a bit like a whip; shake the handle and the tip breaks the sound barrier.
The network is now required to process exceptions which were either not envisioned or conveniently glossed over.
--------------------------

Example 2:
Not from my area of past employment:-
The mobile phone system works rather well.
We have an earthquake and ‘ET phones home’.
The phone system fails because it was not designed to handle the network traffic.
--------------------------

In both cases we could lose the network when it is most likely to be required i.e. an emergency.
(Okay, it could be Schrödinger's cat or Pandora's Box but either way, the Ritz has hit the fan.)

If something can change, given enough time, it will. (Second Law)

Real-time data is always out of date; persistent data simple extends the probability that the change will be greater between samples.

Do we really want persistent data?
Do we want it to the point that it might be out of date and by how much?
Do we want it to reduce network traffic to the point of masking a potential network loading problem?

I think not; use the network as a continuous self diagnostic. Get it to fail at first instant not when really required.

Load it to the hilt; call on demand or at least hit it hard regularly and see if it survives in safe times.

Chris.