[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 289
  • Last Modified:

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.
0
aniston
Asked:
aniston
  • 5
  • 3
  • 2
  • +3
1 Solution
 
genuinegeniusCommented:
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
 
anistonAuthor Commented:
Can u give me a "start point" i could look at in Books Online?
0
 
jjbyersCommented:
You could also fill from the first inistiated form. This would cut down on the disk I/O.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
MikeP090797Commented:
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
 
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 forseeing...how to access a GLOABL data control on multi-instances...PLEASE HELP!!
0
 
MikeP090797Commented:
Did you try my solution?
0
 
eab111098Commented:
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
 
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
0
 
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.

0
 
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.
0
 
amebaCommented:
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
 
amebaCommented:
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
 
MikeP090797Commented:
Do you add the items when the form is visible? IF so, use LockWindowUpdate to disable the refreshing.
0
 
genuinegeniusCommented:
Yes, look at DAO (Data access objects). its at the start
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 5
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now