combobox

[AxeldraX]

is there a way to link a databasetabel(Acces) in a combolist in Excell

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

[onedaywhen]

Definitely, but what do you mean by 'link'? Display a list of values
(read only) or data bind MS Access style (read write). Also, if the
combobox/listbox on a userform or a worksheet?

AxeldraX <Axeldra...@excelforum-nospam.com> wrote in message news:<Axeldra...@excelforum-nospam.com>...

[AxeldraX]

i mean binding data from Acces to a combobox in a userform of Excel

[onedaywhen]

Excel's combobox controls link to Excel ranges and don't have a
DataSource property for binding a recordset. Therefore you have to
handle the binding yourself. This is easy enough if you just want use
the combobox to display a column of data but is a little bit more
involved if you want to write back to the database changes made by the
user. Here's a demo:

1. Add a new userform
2. Add a combobox called ComboBox1
3. Paste the code below into the .
4. Edit the constant strPATH for your database; you will also need to
amend strCONNECTION if your DB has protection/security: for details
see
http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForMicrosoftJet

5. Edit the SQL to return a key column (e.g. RefID) and a data column
(e.g. Surname) respectively from your table (e.g. PersonalDetails).
6. Run the userform, drop down the combobox and view the data column.
When an item is selected, the hidden bound (key) column will be
returned by the combobox's Value property, the visible text (data)
column by its Text property.

I haven't provided code to write back changes to the database because
it's difficult to generalize e.g. do you want to update the database
on the KeyDown event, the AfterUpdate event or the UserForm_Deactivate
event? The general approach is to propagate the change from combobox
to the recordset and invoke its Update or BatchUpdate method as
appropriate.

'---------------------------------
Option Explicit

Private m_oConn As ADODB.Connection
Private m_oRS As ADODB.Recordset

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

Private Sub UserForm_Initialize()

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
.Open strSQL

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

Private Sub UserForm_Terminate()

Set m_oRS = Nothing
Set m_oConn = Nothing

End Sub
'---------------------------------

AxeldraX <Axeldra...@excelforum-nospam.com> wrote in message news:<Axeldra...@excelforum-nospam.com>...