Solved

Shall I Use Database Control in this situation?!?

Posted on 1999-01-12
14
257 Views
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.
0
Comment
Question by:aniston
  • 5
  • 3
  • 2
  • +3
14 Comments
 

Expert Comment

by:genuinegenius
Comment Utility
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!
0
 

Author Comment

by:aniston
Comment Utility
Can u give me a "start point" i could look at in Books Online?
0
 
LVL 3

Expert Comment

by:jjbyers
Comment Utility
You could also fill from the first inistiated form. This would cut down on the disk I/O.
0
 
LVL 8

Expert Comment

by:MikeP090797
Comment Utility
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

Const LB_INITSTORAGE=&H01A8

SendMessage List1.Hwnd, LB_INITSTORAGE, 4000, 100000
0
 

Author Comment

by:aniston
Comment Utility
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!!
0
 
LVL 8

Expert Comment

by:MikeP090797
Comment Utility
Did you try my solution?
0
 
LVL 1

Expert Comment

by:eab111098
Comment Utility
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

form1.datacontrol.xxxxxx

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.

later.
ed.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:aniston
Comment Utility
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
0
 

Author Comment

by:aniston
Comment Utility
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.

0
 

Author Comment

by:aniston
Comment Utility
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.
0
 
LVL 15

Expert Comment

by:ameba
Comment Utility
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)
    tempRS.Close
    Set tempRS = Nothing
    ' show forms
    For i = 1 To 3
        Caption = "Filling form " & i
        DoEvents
        Set x = New Form2
        x.Show
        x.Move Left + 330 * i, Top + 330 * i
        DoEvents
    Next
    ' 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)
    mydb.Close
End Sub

----------------------------------
' Form2, add listbox
Option Explicit

Private Sub Form_Load()
    Dim i As Integer, numrecords As Integer
    With List1
        .Visible = False
        .Clear
        .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
        Next
        .Visible = True
    End With
End Sub

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

0
 
LVL 15

Accepted Solution

by:
ameba earned 80 total points
Comment Utility
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?
0
 
LVL 8

Expert Comment

by:MikeP090797
Comment Utility
Do you add the items when the form is visible? IF so, use LockWindowUpdate to disable the refreshing.
0
 

Expert Comment

by:genuinegenius
Comment Utility
Yes, look at DAO (Data access objects). its at the start
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
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…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now