Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Shall I Use Database Control in this situation?!?

Posted on 1999-01-12
14
269 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
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!
0
 

Author Comment

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

Expert Comment

by:jjbyers
ID: 1455821
You could also fill from the first inistiated form. This would cut down on the disk I/O.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 8

Expert Comment

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

Const LB_INITSTORAGE=&H01A8

SendMessage List1.Hwnd, LB_INITSTORAGE, 4000, 100000
0
 

Author Comment

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

Expert Comment

by:MikeP090797
ID: 1455824
Did you try my solution?
0
 
LVL 1

Expert Comment

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

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
 

Author Comment

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

Author Comment

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

0
 

Author Comment

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

Expert Comment

by:ameba
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)
    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
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?
0
 
LVL 8

Expert Comment

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

Expert Comment

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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
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…

856 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