Combobox or Listbox

[Kathy]

Can a combobox and a listbox on a userform get their list items from a query or
table in an Access database? How?

Thanks,

Kathy

 

[Trevor Shuttleworth]

Kathy

please do not cross-post. The same people tend to look at all the groups so
you won't get more answers, just more irritated volunteers

Regards

Trevor

 

[onedaywhen]

This code selects two columns, a key and a column respectively. The
key is shown in the combo's first (hidden) column, the data in its
second (visible) column. I've used appropriate recordset settings in
case you want to be able to update the source.

'-----------------------
Option Explicit
Private Sub UserForm_Initialize()

Dim m_oConn As Object
Dim m_oRS As Object

Const strCONNECTION As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source="
Const strPATH As String = "C:\Tempo\New_Jet_DB.mdb"
Const strSQL As String = "SELECT RefID, Surname FROM PersonalDetails"

Dim vntArray As Variant

Set m_oConn = CreateObject("ADODB.Connection")
m_oConn.Open strCONNECTION & strPATH

Set m_oRS = CreateObject("ADODB.Recordset")

With m_oRS
.CursorLocation = 3 ' adUseClient
.CursorType = 3 ' adOpenStatic
.LockType = 4 ' adLockBatchOptimistic
.ActiveConnection = m_oConn
.Command = strSQL
.Open
End With

With ComboBox1

.ColumnCount = 2
.BoundColumn = 1
.TextColumn = 2
.ColumnWidths = "0;" ' first column invisible

vntArray = m_oRS.GetRows
.List = Application.Transpose(vntArray)

End With

End Sub

'----------------------