[AccessD] listbox with rowsource type (ala MS)

Henry Simpson hsimpson88 at hotmail.com
Fri Sep 12 12:07:27 CDT 2003


I would not open a recordset to get the fields collection.  The recordset 
you are opening would pull the entire table contents to the client.  You can 
iterate the fields collection from the linked table def object directly 
without pulling a single record.

I also don't like the manner in which your exit/error handler runs here.  
This function is called many times as separate calls and called again as the 
list is repainted and on each exit the routine tries to destroy oMeetTable = 
Nothing.  Only in the first call to the initialize should any objects be 
created and destroyed and it is sensible to close the object before setting 
the variable to nothing.  In all subsequent calls your error handler is 
called because there is no object to destroy as it was only created in the 
first call.

I would suggest an alternative that calls a separate sub procedure that 
returns a static variant containing the array to your function and only 
create/destroy object variables (in this case tabledef and possibly field 
variables) in that sub procedure.

Have you considered the possibility of using a list for each table and 
setting the row souce type property to 'field list'?

Hen

>From: Sad Der <accessd666 at yahoo.com>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: Acces User Group <accessd at databaseadvisors.com>
>Subject: [AccessD] listbox with rowsource type (ala MS)
>Date: Fri, 12 Sep 2003 03:01:17 -0700 (PDT)
>
>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

_________________________________________________________________
The new MSN 8: advanced junk mail protection and 2 months FREE*  
http://join.msn.com/?page=features/junkmail



More information about the AccessD mailing list