Transpose problem
Hi!
I have userform for extraction of records from .mdb file (using ADO). The userform contain 1 textbox for entering strings for search, commandbutton for triggering the search and listbox to show the search results. the extracted records are presented as multiline instead of one line for each record. when I tried to transpose it i got error message about type mismatch (vaData). I used the following code: Sub Populate_Combobox_Recordset() Dim cnt As ADODB.Connection Dim rst As ADODB.Recordset Dim stDB As String, stConn As String, stSQL As String Dim xlCalc As XlCalculation Dim vaData As Variant With Application xlCalc = .Calculation .Calculation = xlCalculationManual .EnableEvents = False .ScreenUpdating = False End With Set cnt = New ADODB.Connection stDB = "C:\Documents and Settings\ELancry\My Documents\chapter6b.mdb" stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & stDB & ";" stSQL = "SELECT * FROM tblContact WHERE LastName Like '%" & TextBox1.Text & "%'" With cnt .CursorLocation = adUseClient 'Necesary for creating disconnected recordset. .Open stConn 'Open connection. Set rst = .Execute(stSQL) End With With rst .MoveFirst 'To retrieve the Recordset. Set .ActiveConnection = Nothing 'Disconnect the recordset. vaData = Application.Transpose(.GetRows) End With cnt.Close With UserForm1 With .ListBox1 .Clear .ColumnCount = 9 .BoundColumn = 1 '(k - 1) .List = vaData .ListIndex = -1 End With End With With Application .Calculation = xlCalc .EnableEvents = True .ScreenUpdating = True End With Set rst = Nothing Set cnt = Nothing End Sub I will be happy to get help to solve this problem Eli |
All times are GMT +1. The time now is 12:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com