Creating collection

[dfistric]

Hi,

Is it possible to create a new collection, fill it with values from some table and that that collection can be accessible from any module or form after program is started.

I would like to use collection as public variable for storage of some variables
which will be used very often inside whole program.

Reason for this is that I can't declare public variables in front because users can enter their own inside one table, and to use dlookup or query to pull them out each time it's needed is a bit slow.

I'm currently using following code to fill up collection, but I can access it only inside that procedure (testing it with debug.print).

Public Function ClassFill()
    Dim PrintersColl As New Collection ' Create a Collection object.
      
    Dim rst As Recordset, dbs As Database
    
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset("tbl_Printer_con_codes")
    
    Do Until rst.EOF
        PrintersColl.Add Item:=Chr$(rst!printer_Code_1) & rst!Printer_Code_2 & Chr$(Nz(rst!Printer_Code_3, 0)), key:=rst!Printer_Job
        rst.MoveNext
    Loop
    Debug.Print PrintersColl.Item("INIT")
End Function

If not using collection, do you have any other suggestions.

Any help is very appreciated

 

[Banana]

While I think collection are great assets, I usually use it to manage objects, not data. Recordset and queries almost always will be efficient than collection or even arrays.

Just exactly what are you trying to prevent users from doing that hits the server too much?

 

[dfistric]

Thx for answer Banana,

I managed to do the following function which is started when application is launched.

 Public Function ClassFill()
       Dim PrintersColl As New Collection ' Create a Collection object.
      
    Dim rst As Recordset, dbs As Database
    
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset("tbl_Printer_con_codes")
    
    Do Until rst.EOF
        PrintersColl.Add Item:=Chr$(rst!printer_Code_1) & rst!Printer_Code_2 & Chr$(Printer_Code_3),key:=rst!Printer_Job
        rst.MoveNext
    Loop
   
End Function

Values are following for example:

printer_Code_1     printer_Code_2      printer_Code_3                       PrinterJob
chr$(27)                     "E"                        1                                    BOLD
chr$(27)                     "E"                        0                                    NORM

and when I do

printersColl.Item("BOLD") I get value chr$(27) & "E" & 1 , great

but only inside this function.

Is it possible (or I missed something) that I can use printersColl.Item("BOLD") from any module.

Why I need that. I'm parsing some strings which are loaded from text file. Inside that file I have
codes like this [BOLD] [NORM] who are only placeholders for real data which will be filled from
inside my program and then again saved as new file but this time with some special codes.

Sample:

[998BOLD] This is just a print test [998NORM] using an normal font.

Output:

E1 This is just a print test E0 using a normal font

In my parse routine, when I find for example. BOLD Printerjob code, I want to put its IDcode value instead

something like this

Function Parsing(Parse_code as string)
' Full code = [998BOLD] but I need only BOLD part
Parse_code_no = mid$(parse_code,2,3) ' 998 part which I know is printer code
Parse_code_function = mid$(parse_code,5,4) ' BOLD part which I know is printerjob name from my database

Select case parse_Code_no

Case....
Case....

Case 998
    'aha, printer code lets take name of the job

    Parsing = PrintersColl.Item(Parse_code_function) ' it's equal to PrintersColl.Item("BOLD")

end select

end function

I would like to use this values as global because there is lot of parsing involved (function is called many times, so if I put ClassFill() function inside the parsing routine it will slow it down) so I would like to speed it as much
as possible.
True, I could declare global constants and fill them with values and it will work, but the problem is that number of
codes is not fixed, user can add them by himself into a table so I need each time the program starts to load them to some global constants.

I hope I explained better this time. Maybe there is some simpler solution but can't think of it.

Any help is appreciated.

 

[Banana]

You'd need to declare the collection as a public:

Public MyCollection As New Collection

Public Function(...)

...

End Function

 

[dfistric]

Thank you very much.It works.