[AccessD] listbox with rowsource type (ala MS)

Hi Sander

Henry is right (Subject: Where False, was listbox with rowsource
type).
I did a test but with small tables only.

However, assuming each table has a normal autonumber primary key,
modify the query like this: 

  SELECT
    tblOne.*,
    tblTwo.*,
    tblThree.*
  FROM
    tblOne,
    tblTwo,
    tblThree
  WHERE
    tblOne.ID Is Null And
    tblTwo.ID Is Null And
    tblThree.ID Is Null;

That should reveal the field names in a split second.

/gustav


> 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