By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,188 Members | 844 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,188 IT Pros & Developers. It's quick & easy.

Programming vba label/text box

P: 5
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub ENTER_Click()
  2. Dim cn As Object
  3. Dim rs As New ADODB.Recordset
  4. Dim strFile As String
  5. Dim strCon As String
  6. Dim strSQL, strInput As String
  7. 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;"
  8. Set cn = CreateObject("ADODB.Connection")
  9. cn.Open strCon
  10. If ComboBox1.ListIndex = -1 Then
  11. MsgBox "No Test Selected!", , "KBOW"
  12.  ElseIf ComboBox1.Value = "Functional Test" Then
  13. strSQL = "SELECT ModuleId,EntryDate FROM inventoryModuleLocation INNER JOIN " _
  14. & " dbo.InventoryLocationList ON dbo.InventoryLocationList.LocationCode=dbo.inventoryModuleLocation.LocationCode; "
  15. Set rs = CreateObject("ADODB.RECORDSET")
  16. rs.ActiveConnection = cn
  17. rs.Open strSQL
  18.     For iCols = 0 To rs.Fields.Count - 1
  19. Worksheets("Sheet2").Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
  20. Sheet2.Range("A2").CopyFromRecordset rs
  21. ResultLabel.Caption = strSQL
  22.     Next
  23. End sub 
  24.  
Attached Images
File Type: png dbscreen.png (24.7 KB, 43 views)
Jul 1 '15 #1
Share this Question
Share on Google+
8 Replies


Rabbit
Expert Mod 10K+
P: 12,365
Line 21. You set the caption to strSQL.
Jul 1 '15 #2

P: 5
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
Jul 1 '15 #3

Rabbit
Expert Mod 10K+
P: 12,365
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.
Jul 1 '15 #4

P: 5
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
Jul 2 '15 #5

Rabbit
Expert Mod 10K+
P: 12,365
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.
Jul 2 '15 #6

P: 5
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.
Jul 6 '15 #7

P: 5
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.
Jul 6 '15 #8

Rabbit
Expert Mod 10K+
P: 12,365
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.
Jul 6 '15 #9

Post your reply

Sign in to post your reply or Sign up for a free account.