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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top