Hello
I have an excel Userform that is populating 2 listboxes from and access db (2003). I am using a SQL statement to pull the info into the listbox.

The first SQL Statement executes and populates as it should, but when it runs the second sql statement, it gives me a type mismatch.

Here is some of the code:

SQL statement 1 which works and populates properly:
sSQL = "SELECT Customers.CustID, Customer.Company " & _
    "FROM Customers ORDER BY Customers.CustID;"

SQL Statement 2 which gives the type mismatch:
stSQL2 = "SELECT Customer.Company,Quotes.QuoteID " & _
    "FROM Query1;"

Here is where the error is generated: In the first listbox, the only difference is the variable name and it is vaData instead of vaData2. The datatypes for ID fields are both autonumber, the FK datatype is number.

With .lbquotes
            .Clear
            .ColumnCount = 2
            .ColumnWidths = "0;1"
            .List = Application.Transpose(vaData2) 'This line generates the error
            .ListIndex = -1
End with

Any Ideas would be great.

TIA
Terry

Well a type mismatch is just that, the type on the right of the .List assignment is not the same type on the right..

The list property expects an array of variants. It was my understanding that Application.Transpose is now obselete, and that you can just use a Range object directly...

I would say break out the Appplication.Transpose code, and assign it to a variant array above this code block, see what that is returning and work with that.

Thats error trapping!

Best Regards,
Rob