[AccessD] listbox with rowsource type (ala MS)

Hi Sander

Maybe I'm missing something, but why all this trouble to get a field
list? 

1. Create a select query like this:

  SELECT
    tblOne.*,
    tblTwo.*,
    tblThree.*
  FROM
    tblOne,
    tblTwo,
    tblThree
  WHERE
    False;

and save it as, say, qdyFieldList.
This will retrieve all fields from the tables but no records.

2. In your form, adjust the combobox to have FieldList as
RowSourceType and specify qdyFieldList as the RowSource.

This should load in a fraction of a section.

/gustav


>>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