[AccessD] listbox with rowsource type (ala MS)

IMO 4 seconds is too long to load each tab page as well.  Your callback
routine is repeatedly opening and closing what looks like the same
recordset or else you pasted the same acLBInitialize case code in 3
times.  A field list as the rowsourcetype would be a faster way to get a
list of fields for a single table.

Charlotte Foust

-----Original Message-----
From: Sad Der [mailto:accessd666 at yahoo.com] 
Sent: Friday, September 12, 2003 2:01 AM
To: Acces User Group
Subject: [AccessD] listbox with rowsource type (ala MS)


Hi group,

i've got a problem. I'm using a tab control. On all
tabs i've got listboxes wich are filled with field
names of a couple of tables. These listboxes are
filled using the rowsource type thingy. This seems to
be the original MS-way-of-programming.

Below I pasted the code. In the property RowSourceType
of the listbox I fill in the function name. 

When I programmed it it worked fine. The client is on
the desktop of the user and the BE is on our test
network. It took about 4 seconds to load each
tab-page.

Now it's in production (on another network) and if a
switch between tab pages now takes almost 2 MINUTES!!!

Any ideas how this is possible??
 
tia

Sander
Private Function ListMeetVelden(fld As Control, id As
Variant, row As Variant, col As Variant, code As
Variant) As Variant
  
  Static strVelden()  As String
  Static lngRows      As Long
  Dim varRetVal       As Variant
  Dim oMeetTable      As DAO.Recordset
  Dim i               As Integer
  
  On Error GoTo ListMeetVelden_Error
  
  Select Case code
    Case acLBInitialize
      ReDim strVelden(0)
      'fill array with fields.
      Set oMeetTable = CurrentDb().OpenRecordset(Name:="tblxxxx",
Type:=dbOpenSnapshot, Options:=dbOpenForwardOnly)
      With oMeetTable
        For i = 0 To .Fields.Count - 1
          strVelden(UBound(strFields)) = "C_" &
.Fields(i).Name
          ReDim Preserve strVelden(0 To
UBound(strVelden) + 1)
        Next i
      End With
      
      Set oMeetTable = Nothing
      'fill array with fields.
      Set oMeetTable = CurrentDb().OpenRecordset(Name:="tblxxx",
Type:=dbOpenSnapshot, Options:=dbOpenForwardOnly)
      With oMeetTable
        For i = 0 To .Fields.Count - 1
          strVelden(UBound(strFields)) = "D_" &
.Fields(i).Name
          ReDim Preserve strVelden(0 To
UBound(strFields) + 1)
        Next i
      End With
      
      Set oMeetTable = Nothing
      'fill array with fields.
      Set oMeetTable = CurrentDb().OpenRecordset(Name:="tblxxx",
Type:=dbOpenSnapshot, Options:=dbOpenForwardOnly)
      With oMeetTable
        For i = 0 To .Fields.Count - 1
          strVelden(UBound(strVelden)) = "E_" &
.Fields(i).Name
          ReDim Preserve strVelden(0 To
UBound(strVelden) + 1)
        Next i
      End With
      
      ReDim Preserve strVelden(0 To UBound(strVelden)
- 1)
      
      lngRows = UBound(strVelden) + 1       ' array is
0-based
      varRetVal = lngRows
    Case acLBOpen
      varRetVal = Timer       'Unique ID for control
    Case acLBGetRowCount
      varRetVal = lngRows
    Case acLBGetColumnCount
      varRetVal = 1
    Case acLBGetColumnWidth
      varRetVal = -1      'Default of -1 uses default
column width
    Case acLBGetValue
      varRetVal = strVelden(row)
    Case acLBEnd
      Erase strVelden
  End Select

  ListMeetVelden = varRetVal

ListMeetVelden_Exit:
  Set oMeetTable = Nothing
  Exit Function
ListMeetVelden_Error:
  Set oMeetTable = Nothing
  Call g_oGenErr.Throw("xxx.frmxxx", "tblxxxx")
End Function



__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com