Dynamic Dropdown Lists

[Gaylord]

Hi,

I've got this code, which quite happily collects a list of DSN's from the PC, but I want to place this into a drop down list on an Access Form... Any ideas?

Thanks

Jamie


Private Declare Function SQLDataSources Lib "ODBC32.DLL" _
(ByVal henv&, ByVal fDirection%, ByVal szDSN$, ByVal cbDSNMax%, _
pcbDSN%, ByVal szDescription$, ByVal cbDescriptionMax%, _
pcbDescription%) As Integer
'
Private Declare Function SQLAllocEnv% Lib "ODBC32.DLL" (env&)
'
Dim SQL_SUCCESS As Long
Dim SQL_FETCH_NEXT As Long

Function GetDSNsAndDrivers()
SQL_FETCH_NEXT = 1
SQL_SUCCESS = 0
Dim i As Integer
Dim sDSNItem As String * 1024
Dim sDRVItem As String * 1024
Dim sDSN As String
Dim sDRV As String
Dim iDSNLen As Integer
Dim iDRVLen As Integer
Dim lHenv As Long 'handle to the environment

On Error Resume Next
cboDSNList.AddItem "(None)"
'get the DSNs
If SQLAllocEnv(lHenv) <> -1 Then
Do Until i <> SQL_SUCCESS
sDSNItem = Space$(1024)
sDRVItem = Space$(1024)
i = SQLDataSources(lHenv, SQL_FETCH_NEXT, sDSNItem, 1024, _
iDSNLen, sDRVItem, 1024, iDRVLen)

If sDSN <> Space(iDSNLen) Then
cboDSNList.AddItem sDSN
End If
Loop

End If

End Function

 

[FancyPrairie]

Which version of Access are you using?

There are a couple of ways to do it. One method is the easiest but you are limited to 2048 characters in the list box if you're using Access 2000 or below. If you are using Access 2000 or below and need more than 2048 characters, I suggest writing your results to a table because it gets more complicated to write the code to add items to a list box.

 

[PHV]

Doesn't the AddItem method work ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244