Problem displaying single record in userform listbox from recordset

**carpking** · 07-20-2009, 09:11 AM

I am trying to populate a 3 column listbox in a userform from SQL Server via ADO. When the result set consists of more than one record, there is no problem and the data is displayed properly (ie each piece of data is in its appropriate column) eg...

StockCode..........QtyReqd.........JobDeliveryDate
test1...................1................. 01/01/1900
test2...................1..................31/12/1900
test3...................3..................18/02/1900

however when the recordset returns only a single record, the data does not transpose and views as below (ie each piece of data in the record is on a different line in the first column

StockCode...........QtyReqd.........JobDeliveryDate
test1
1
01/01/1900

here is my code

Please Login or Register  to view this content.

I have tried removing the Application.Transpose eg ...

Please Login or Register  to view this content.

However the data still displays incorrectly. Can anyone help?

this question has also been placed on Mr Excels forum at: http://www.mrexcel.com/forum/showthread.php?t=403014

**romperstomper** · 07-20-2009, 09:33 AM

Try using this function instead of Application.Transpose:

Please Login or Register  to view this content.

**carpking** · 07-20-2009, 09:54 AM

Wow, thanks romperstomper for the amazingly quick reply!!! your function has done it, the single record is now displaying horizontally. Just one problem the second piece of data "Qty Reqd" is now missing ... so the display is now

StockCode..........QtyReqd.........JobDeliveryDate
test1................... ................. 01/01/1900

any idea how to fill it in? Thanks again for the help

**romperstomper** · 07-20-2009, 10:04 AM

I can't replicate that. Have you tried stepping through the function to see what's happening?

**carpking** · 07-20-2009, 10:15 AM

Hi romper, from what i can see when stepping through the code the following is happening ...

when lngcol =0 varOut = test1
when lngcol =1 varOut = 1
when lngcol =2 varOut = 01/01/1900
when lngcol =3 varOut = <Subscript out of range>

... so it seems that the for loop that increments lngcol is perhaps looping 1 time too many ??? could that be the problem ?

**romperstomper** · 07-20-2009, 10:18 AM

Shouldn't do otherwise you'd be getting an error at runtime and it wouldn't explain why the 1 is missing. You haven't done anything with the column widths, have you?

**carpking** · 07-20-2009, 10:25 AM

the column widths are set at 100pt;49.95pt;49.95pt in the properties window. havent changed anything in the code except the reference to your function

Please Login or Register  to view this content.

its wierd ?!? I appreciate all your help and effort. I will try changing the column widths and font size and see if it makes a difference

**romperstomper** · 07-20-2009, 10:34 AM

Try using:

Please Login or Register  to view this content.

so that you can examine in the Locals window exactly what gets put into rcArray.

**carpking** · 07-21-2009, 05:58 AM

Hi, this is what is happening with the arrays.

When the recordset is put into rcArray (rcArray = Recordset.Getrows)
rcArray(0,0) = "test1"
rcArray(1,0) = "1"
rcArray(2,0) = "01/01/1900"

The transposegetrows function then puts the "rcArray" array into vardata
vardata(0,0) = "test1"
vardata(1,0) = "1"
vardata(2,0) = "01/01/1900"

the function then transposes the array as such
transposegetrows(1,1) = "test1"
transposegetrows(1,2) = "1"
transposegetrows(1,3) = "01/01/1900"

and such
varout(1,1) = "test1"
varout(1,1) = "1"
varout(1,1) = "01/01/1900"

could the problem not perhaps be that the array should be ...
transposegetrows(0,1) = "test1"
transposegetrows(0,2) = "1"
transposegetrows(0,3) = "01/01/1900"
since the original rcArray has values in row = 0 and not row = 1

**romperstomper** · 07-21-2009, 06:13 AM

Originally Posted by carpking

Hi, this is what is happening with the arrays.

...
and such
varout(1,1) = "test1"
varout(1,1) = "1"
varout(1,1) = "01/01/1900"

I presume you meant:
varout(1,1) = "test1"
varout(1,2) = "1"
varout(1,3) = "01/01/1900"
? The output array is 1 based, so there is no 0 element. It shouldn't be a problem (otherwise you wouldn't get any data from that row). I am still puzzled as to why the 1 doesn't make it into the listbox though, when it is definitely in the array.

**carpking** · 07-21-2009, 06:23 AM

yes you are correct! ... sorry copy and paste mistake.

Yep its got me stumped. I am going to try and pull through more coloumns of data to see if the second column is the only one that is left out if there are numerous columns, also i will apply it to a select query returning more than 1 result to see if it occurs for all results.

**carpking** · 07-24-2009, 02:54 AM

Hi Romper ...ok this is what has happened. I have played with the sql query changing the order of the cols. The other information always appears and the "qtyreqd" data "1" is always not displayed, no matter where in the order of cols it is. Also I repeated the "qtyreqd" col in the sql query and increased the no of cols in total to 5 and ensured that 2 results are returned ... and surprise surprise the "qtyreqd" col is always blank everytime and for every record! The next step was to make sure that there was actually data in the array before and ofter the transpose function ... and i have attached a pic showing the Local window at the point just after transposing the array. Does this help any ?

**romperstomper** · 07-24-2009, 03:24 AM

The only thing I notice is that that column is of type Decimal, which isn't really a native Excel type. If you convert it to Double (either in the SQL or in the code) does it come through OK?