Shall I Use Database Control in this situation?!?

My situation is this:

I have a form with a single Listbox control on it. During run-time the form is made into several new instances of that form. I do this by:

Dim FormInstances() as Form1

ReDim FormInstances(1 to 3) as Form1
Set FormInstances(1) = New Form1
Set FormInstances(2) = New Form1
Set FormInstances(3) = New Form1

The Listbox control that resides on these forms holds about 4,000 different items (which is displayed to the user). These 4,000 items are loaded from disk. When I create a new instance of Form1 I must refill the ListBox with these 4,000 items. Is there a better way to have these 4,000 items already filled in when I create a new instance or must I ".AddItem" for each of these items when I create a new instance?  When I create a new instance (which puts the 4,000 items into the new form's Listbox each time) on my Pentium II 300 MHz it takes about 15 secs to complete the entire task. On my 486 system it takes a WHOPPING 2 mins to complete the entire task (just for the 1 new instance!). There has got to be a way to have the Listbox control already filled in with these items.

I read that there are Data Controls that can fill an entire Listbox with a data table automatically. And I can even set a feature to have that database automatically update itself and all its "bounded" Listbox controls (from the other instances)  when I simply change an item contained in the Listbox. The only problem I have seen with this is that it doesn't look like you can get access to a Data Control  that is defined on a separate form. I was hoping to define the Data Control object on a unique form (non-instanceable Form different in look and behavior to the instanced ones. Consider it the PARENT form that creates the Form instances) and then get access to it when I create a Form instance. It looks like a Data Control can only be defined and accessed in the Form you are currently working with. You cannot get access across Forms automatically.
Is it best to put in the time to fully understand Databasing and use this to solve my problem(s) or is there a much easier way of figuring things out (maybe a technique I don't know about or haven't even considered)?

So, summarizing:

I want to create be able to create instances of a  form which contains a Listbox that is AUTOMATICALLY filled with a HUGE amount of items that I preloaded in earlier into the original Form (who was instanced). I do not want to have that SLOW delay of windows copying each item 1-by-1 into the new instanced Form's Listbox. As I said earlier, on a 486 this takes in upwards of 2 mins to complete just the copying process into the Listbox.
Who is Participating?
If I use only 1 field Title (string 250), and don't fill Itemdata

' on Form1
    tmpSql = "Select top 4000 Title from Titles"
' in Form2
            .AddItem varrkeys(0, i)

it takes 6 sec. for 3 forms (on P120/64). On your P2 it should be ~3 sec?
Are you using databound listboxes (take data from a database)
These can be found in components/custom controls

Look in VB books online (comes with VB) for details on how to use them. It requires no code!
anistonAuthor Commented:
Can u give me a "start point" i could look at in Books Online?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

You could also fill from the first inistiated form. This would cut down on the disk I/O.
Try this to speed it up a bit:

Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long


SendMessage List1.Hwnd, LB_INITSTORAGE, 4000, 100000
anistonAuthor Commented:
jjbyers, i have loaded the entire 4000 items into a global array and even a listbox and then read it from their when creating duplicate copies rather than always going to the disk to read them in again...that would be really inefficient (as u were hinting to). Unfortunately, this does not solve my problem. It looks as if Databound Listboxes in conjunction with the data control looks to be the only answer. BUT, there is a DEVILISH problem i am to access a GLOABL data control on multi-instances...PLEASE HELP!!
Did you try my solution?
Would you mind sharing with us why you have a form with only a listbox on it being instantiated several times? Not knowing this, your problem may not be with populating listboxes but with the concept of how to solve your original problem.

To answer the DATA control problem. Put it on a separate form and access it like this


the reason you couldn't access it via "datacontrol.xxxx" is because public and private scope apply to module level and not project level. There is a good reason for this, but, Microsoft could have created project as well as module level scoping.

anistonAuthor Commented:
Mike P, thanks for the solution, however, i noticed no speed differences. Maybe i am implementing it wrong or something. I just added what you said into my code and place the SendMessage call inside the Form's Load Event and when i ran it i noticed no speed increases. What is it doing?  It looks like it is increasing the amount of memory that can be held in the Listbox before maybe it updates the screen and sends out events?!?  This could help but if i decide to open up say 100 instances of this form then the time that it takes will still add up considerably.  I dunno
anistonAuthor Commented:
eab, OOPS...i should have said in my original message about the Data Control that i don't have problems accessing the data control across form's BUT i DO have problems accessing the DataSource field, and DataField option of the Listbox. I want to be able to set the DataSource field to the Data Control that is found on another Form...That is what i was intending to say. I TOTALLY forgot about mentioning this. Sorry about this. I have played around in VB and can't figure out how to get this access.
I already new i could access the Data Control on a different form via form1.Data1.xxxx. Sorry about that.

As for the reason i want to INSTANCE multi forms that each have their own listbox is because the full app i have created makes use of Winsock and i want to allow the user to select (from the listbox) the sites to call out. Each instance that a user requests for may have a different list to call. Because of this the newly instanced form's Listbox must be refilled in again.

anistonAuthor Commented:
eab, i had to reject your answer for the sake that

1. I made a CHEEZY mistake of not including the fulld details about "not being able to access the Data Control across forms".

2. You didn't answer my primary problem.

If there were a way to give portion points without having the question CLOSED i would do it for your answer..Sorry.
Maybe try without data controls:
You will need 2 forms and 1 module. Example uses table Titles in Biblio.Mdb (it has >8000 rows). I used 3 fields, with only 1 field it should be faster.

' module
Option Explicit
Public MyWorkspace As DAO.Workspace
Public mydb As DAO.Database
Public varrkeys As Variant ' our variant array

' Form1
Option Explicit
Private Declare Function GetTickCount Lib "kernel32" () As Long
Private tempRS As DAO.Recordset

Private Sub Form_Click()
    Dim tmpSql As String, i As Integer, x As Form
    Dim tim0 As Long
    tim0 = GetTickCount ' start timing
    ' Define the SQL statement
    tmpSql = "Select top 4000 Title, [Year Published], ISBN from Titles"
    ' Open the recordset
    Set tempRS = mydb.OpenRecordset(tmpSql, dbOpenForwardOnly)
    Caption = "Filling variant array..."
    ' fill variant array
    varrkeys = tempRS.GetRows(10000)
    Set tempRS = Nothing
    ' show forms
    For i = 1 To 3
        Caption = "Filling form " & i
        Set x = New Form2
        x.Move Left + 330 * i, Top + 330 * i
    ' report time
    Caption = (GetTickCount - tim0) / 1000 & " sec"
End Sub

Private Sub Form_Load()
    Dim gDBName As String
    gDBName = "C:\vb5\Biblio.mdb"
    Set MyWorkspace = Workspaces(0)
    Set mydb = MyWorkspace.OpenDatabase(gDBName)
End Sub

Private Sub Form_Unload(Cancel As Integer)
End Sub

' Form2, add listbox
Option Explicit

Private Sub Form_Load()
    Dim i As Integer, numrecords As Integer
    With List1
        .Visible = False
        .Move 60, 60, ScaleWidth - 120, ScaleHeight - 120
        numrecords = UBound(varrkeys, 2)
        For i = 0 To numrecords
            .AddItem varrkeys(2, i) & vbTab & varrkeys(1, i) & vbTab & varrkeys(0, i)
            .ItemData(.NewIndex) = i
        .Visible = True
    End With
End Sub

Private Sub List1_Click()
    Caption = "row " & List1.ListIndex
End Sub

Do you add the items when the form is visible? IF so, use LockWindowUpdate to disable the refreshing.
Yes, look at DAO (Data access objects). its at the start
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.