VBA Label/Textbox programming for SQL table

[Adeniji Segun]

Hi, please can anyone help me out with this.
I have been struggling with it for days and no success 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.
According to the code below, the name of my label is ResultLabel.
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.23.30.8\KBOW;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

 

[Adeniji Segun]

Hi Adeniji,
I'm not SQL literate but, strSQL = "SELECT ModuleId,EntryDate FROM inventoryModuleLocation INNER JOIN " which is text string.
Recordset rs is the data that you are writing to Sheet2.
Maybe
ResultLabel.Caption = rs
good luck

Thank you for your help but I am actually interested in writting the result instead to the label. I have definetely tried this expression "ResultLabel.Caption = rs" but it is returning type mismatch error.

 

[Adeniji Segun]

Would the result be coming from the loop?
For iCols = 0 To rs.Fields.Count - 1
ResultLabel.Caption = rs.Fields(iCols).Name
next

Thanks scottyg. I just tried that yesterday and it seems to be working. The only issue is that it is returning only one cell in the last column of the table. I tried to do concatenation by trying : ResultLabel.Caption = rs.Fields(0).Name & rs.Fields(1).Name & rs.Fields(2).Name & rs.Fields(3).Name & rs.Fields(4).Name. This is for a table with 5 columns and all the first column of the first rows was returned but the rest of the rows were not returned. I am suspecting that I might need a loop for this, however I do not know how to construct the loop yet. Any idea please? Secondly I also figured out that it seems not to be a good idea to place a table in a label so I am thinking of probably using a Listbox instead. Is this a good idea or is there a better way of displaying a table in a VBA form?

 

[Surendran Setty]

Hi Adenijii,

Try this code. The code would loop through all recordset rows but display data in result label for first five rows since label is not ideal to show large amount of data. As mentioned by you, Listbox is ideal but Listbox is fine only for dataset having few columns and also number of rows should preferably be less than 100.

Private Sub ENTER_Click()
On Error GoTo ERR_Handler
  
  'set reference to 'Microsoft ActiveX Objects 6.1 Library for early binding
  Dim cn As ADODB.Connection
  Set cn = New ADODB.Connection
  
  Dim rs As ADODB.RecordSet
  Set rs = New ADODB.RecordSet
  
  Dim strFile As String
  Dim strCon As String
  Dim strSQL, strInput As String, strResultLabel As String, RowCount As Integer
 
 strCon = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=KBOW;Data Source=10.23.30.8\KBOW;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False;"
 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;"
  
  rs.ActiveConnection = cn
  rs.Open strSQL
  
  If rs.BOF Or rs.EOF Then
  
  'No data fetched from sql
  rs.Close
  GoTo Exit_Here
  
  End If
  
  'Enter column labels in row 1 in Sheet2
  For iCols = 0 To rs.Fields.count - 1
  Worksheets("Sheet2").Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
  Next
  
  'Copy recordset data in Sheet2
  Sheet2.Range("A2").CopyFromRecordset rs
  
  'Create ResultLabel string for each row in recordset
  strResultLabel = "ModuleId" & vbTab & "EntryDate"
  Do Until rs.EOF 'loop through all rows in recordset
  
  RowCount = RowCount + 1
  
  'we don't know how many rows are there in the recordset
  'so show data for only first 5 rows in result label now
  'later modify the row count as per requirement
  If RowCount > 5 Then Exit Do
  
  strResultLabel = strResultLabel & vbCrLf & rs![ModuleId] & vbTab & rs![EntryDate]
  rs.MoveNext
  
  Loop
  
  ResultLabel.Caption = strResultLabel
  
  rs.Close
End If
Exit_Here:
  Set rs = Nothing
  Set cn = Nothing
  
ERR_Handler:
  MsgBox Err.Number & " - " & Err.Description
  Resume Exit_Here
End Sub

Regards,
Surendran

 

[Surendran Setty]

Hi,

one small correction in code but very important as otherwise it will hang.

Modify this code snippet
Exit_Here:
Set rs = Nothing
Set cn = Nothing

to

Exit_Here:
Set rs = Nothing
Set cn = Nothing
Exit Sub

Ensure to add Exit sub statement before running the macro.

Regards,
Surendran

 

[Adeniji Segun]

Hi,

one small correction in code but very important as otherwise it will hang.

Modify this code snippet
Exit_Here:
Set rs = Nothing
Set cn = Nothing

to

Exit_Here:
Set rs = Nothing
Set cn = Nothing
Exit Sub

Ensure to add Exit sub statement before running the macro.

Regards,
Surendran

I am very appreciative. The modifications you made on my code gave me alot of insight on how to go about with the solution.
However, after running this code, my GUI returned only : " ModuleId EntryDate"
I am trying to figure out where the problem might be. I place below what I was expecting to see as my result.
Thanks ones again Surendran

 

[Surendran Setty]

Hi Segun,

If you have set label height to default, it would show only one line. Increase the label height and check. But in text box, all data can be placed but if it is set to show count of lines lesser than the total count, still you would be able to see all the data by moving the cursor up or down inside the text box.

In the attached image file, you can see two labels and two text boxes with same data with different heights. In code, i am using sql to fetch data from Excel sheet but recordset logic is same as given earlier. Also attached the Excel macro file to test at your end. Also note Text Box Multiline property is set to Yes in userform.

Regards,
Surendran

 

[Adeniji Segun]

Hi Segun,

If you have set label height to default, it would show only one line. Increase the label height and check. But in text box, all data can be placed but if it is set to show count of lines lesser than the total count, still you would be able to see all the data by moving the cursor up or down inside the text box.

In the attached image file, you can see two labels and two text boxes with same data with different heights. In code, i am using sql to fetch data from Excel sheet but recordset logic is same as given earlier. Also attached the Excel macro file to test at your end. Also note Text Box Multiline property is set to Yes in userform.

Regards,
Surendran

 

[Adeniji Segun]

Thank you very much and I really appreciate your effort in helping me out with my project.
That absolutely solved my problems.
I got back to continue with the project and it is making quite a lot of sense now. Although I still have some other challenges but I am making progress.I would not mind if you can help me look at the edited code above and my new challenge. Its supposed to be something I know but I do not just know what is wrong here.