Programming vba label/text box

[cimthog]

Hi, please can anyone help me out with this.
I have been struggling with it for days and no result yet.
I am trying to run SQL queries that retrieve Tables stored in SQL server and then place the table in a VBA FORM label or textbox.
So far there has not been any success as this is only returning back the query I give in on my FORM label.
I am sure the query is working because I can see the result in an excel sheet.
Please can someone tell me where I am getting it wrong.
I have also attached a screen shot of the FORM to this question.
Thanks

Private Sub ENTER_Click()
Dim cn As Object
Dim rs As New ADODB.Recordset
Dim strFile As String
Dim strCon As String
Dim strSQL, strInput As String
strCon = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=KBOW;Data Source=10.9.0\KADE;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False;"
Set cn = CreateObject("ADODB.Connection")
cn.Open strCon
If ComboBox1.ListIndex = -1 Then
MsgBox "No Test Selected!", , "KBOW"
 ElseIf ComboBox1.Value = "Functional Test" Then
strSQL = "SELECT ModuleId,EntryDate FROM inventoryModuleLocation INNER JOIN " _
& " dbo.InventoryLocationList ON dbo.InventoryLocationList.LocationCode=dbo.inventoryModuleLocation.LocationCode; "
Set rs = CreateObject("ADODB.RECORDSET")
rs.ActiveConnection = cn
rs.Open strSQL
    For iCols = 0 To rs.Fields.Count - 1
Worksheets("Sheet2").Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
Sheet2.Range("A2").CopyFromRecordset rs
ResultLabel.Caption = strSQL
    Next
End sub 
 

[Rabbit]

Line 21. You set the caption to strSQL.

[cimthog]

Thanks for your help Rabbit.
I did not get that please.
Am I supposed to name my caption strSQL?
I just tried that but it did not work

[Rabbit]

No. The reason the caption is showing the SQL code is because you set it to strSQL which contains the SQL code. If you want to set it to the value returned by the SQL, then you need to set it to a value from the recordset. Similar to what you did for the cells on line 19.

[cimthog]

Thank you so much Rabbit
I just edited my code and I have that expression written as :
ResultLabel.Caption = rs.Fields(iCols).Name
But it only returned first cell value of the last columns in the database.
I would be glad if you can probably give me a sample expression in this case.
Thanks for your help

[Rabbit]

I said similar, not exactly the same. It depends on what you actually want to show in the caption, you haven't told us exactly what should be displayed in the caption. If you want to display the whole row, you need to concatenate the result of each cell.

[cimthog]

Thanks Rabbit for the effort.
Basically I am trying to display a table in the caption.The table is coming from an SQL server. Like I said earlier, each time I run this script I am able to see my result perfectly on excel sheet "sheet2" but the problem is writing the same result into the Label on my FORM instead of Excel.
Another Idea I got is to first write the table into an excel sheet and then read the data from excel sheet into the Label.I have not being successful with this also.
I am sorry for sounding so amateur like. This is actually my first VBA project.

[cimthog]

I added this line to my code
ResultLabel.Text = rs.Fields(0).Name & rs.Fields(1).Name & rs.Fields(2).Name

After this I got the very first row on my table which are just table headers. I was not able to get the rest. Do I need a loop for this? I tried one but not working.

[Rabbit]

That's because you're using the .Name property. That returns the name of the field, not the value. Leave that out if you want to retrieve the value. And yes, you will need a loop if you want to concatenate multiple rows together.

But why would you want to display it like this? Displaying an entire table in a caption is such a weird way to display table data when you can just use a table.