Populating Textbox from Combox Using Dao

[Martin Cameron]

Problem After a user selects a client's name from a combobox, I want to
select the addrss details from an excel speadsheet using DAO - someting like:
sqlStr="select * from ClientNames where name like '" & ComboBox.value & "'"
The excel spreadsheet has four columns: Name, Street, Suburb, City.

Here's the code I use to populate the ComboBox:

<code>
Dim docPath As String
Dim FName As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long

' Open the database
docPath = ThisDocument.Path
docPath = Environ("USERPROFILE") + "\My Documents"
FName = "test.xlsx"
Set db = OpenDatabase(docPath & "\" & FName, False, False, "Excel 8.0")

' Retrieve the recordset
Set rs = db.OpenRecordset("SELECT name FROM `ClientNames`") ' where name
like 'Martin Cameron'")

' Determine the number of retrieved records
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With
' Set the number of Columns = number of Fields in recordset
ClientNames.ColumnCount = rs.Fields.Count
' Load the ListBox with the retrieved records
ClientNames.Column = rs.GetRows(NoOfRecords)

' Cleanup
rs.Close
db.Close

Set rs = Nothing
Set db = Nothing
</code>

 

[alborg]

Try this:

Private Sub UserForm_Initialize()

Dim FName as String, docPath as String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long

' Open the database
docPath = Environ("USERPROFILE") + "\My Documents"
FName = "test.xlsx"
Set db = OpenDatabase(docPath & “\†& FName, False, False, "Excel 8.0")

' Retrieve the recordset
Set rs = db.OpenRecordset("SELECT myDatabase.* FROM myDatabase WHERE
myDatabase.name Like " & Chr(34) & "Martin Cameron*" & Chr(34) & ";â€

' Determine the number of retrieved records
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With

' Set the number of Columns = number of Fields in recordset
ListBox1.ColumnCount = rs.Fields.Count

' Load the ListBox with the retrieved records
ListBox1.Column = rs.GetRows(NoOfRecords)

' Cleanup
rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

End Sub

 

[Doug Robbins - Word MVP]

See the article "Load a ListBox from a Named Range in Excel using DAO" at:

http://www.word.mvps.org/FAQs/InterDev/FillListBoxFromXLDAO.htm

The above will work equally well with a combobox. Use it to add all of the
data to the combobox in multiple columns and then use the .BoundColumn
property of the combo box to obtain the information from the desired column
for the selected record.


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP