Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
combobox
is there a way to link a databasetabel(Acces) in a combolist in Excel ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
combobox
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 wrote in message ... 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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
combobox
i mean binding data from Acces to a combobox in a userform of Exce ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
combobox
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/...orMicrosoftJet 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 wrote in message ... i mean binding data from Acces to a combobox in a userform of Excel ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
For Each Combobox | Excel Discussion (Misc queries) | |||
fill combobox depending on selection from another combobox | Excel Discussion (Misc queries) | |||
combobox value | Excel Discussion (Misc queries) | |||
ComboBox | Excel Programming | |||
combobox | Excel Programming |