Go Premium for a chance to win a PS4. Enter to Win


Shall I Use Database Control in this situation?!?

Posted on 1999-01-12
Medium Priority
Last Modified: 2013-11-25
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.
Question by:aniston
  • 5
  • 3
  • 2
  • +3

Expert Comment

ID: 1455819
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!

Author Comment

ID: 1455820
Can u give me a "start point" i could look at in Books Online?

Expert Comment

ID: 1455821
You could also fill from the first inistiated form. This would cut down on the disk I/O.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 1455822
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

Author Comment

ID: 1455823
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 forseeing...how to access a GLOABL data control on multi-instances...PLEASE HELP!!

Expert Comment

ID: 1455824
Did you try my solution?

Expert Comment

ID: 1455825
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.


Author Comment

ID: 1455826
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

Author Comment

ID: 1455827
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.


Author Comment

ID: 1455828
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.
LVL 15

Expert Comment

ID: 1455829
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

LVL 15

Accepted Solution

ameba earned 320 total points
ID: 1455830
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?

Expert Comment

ID: 1455831
Do you add the items when the form is visible? IF so, use LockWindowUpdate to disable the refreshing.

Expert Comment

ID: 1455832
Yes, look at DAO (Data access objects). its at the start

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question