hindersaliva
asked on
Excel VBA - UserForm Label Caption to show recordset data dynamically
You have an extra "+" before the ")"
Actually try
.Caption = rsData.Fields.Item + cells(10, intColumn).Value
.Caption = rsData.Fields.Item + cells(10, intColumn).Value
And if that doesn't then try
.Caption = rsData.Fields.Item + ActiveSheet.cells(10, intColumn).Value
or
.Caption = rsData.Fields.Item + Sheets(Sheet name).cells(10, intColumn).Value
.Caption = rsData.Fields.Item + ActiveSheet.cells(10, intColumn).Value
or
.Caption = rsData.Fields.Item + Sheets(Sheet name).cells(10, intColumn).Value
ASKER
Thanks Martin,
I did this to check that strFieldname is correct. It is.
But the result is the same.
I feel it needs to have the (" and ") and the .Value at the end.
Any suggestions please?
I did this to check that strFieldname is correct. It is.
strFieldname = Cells(10, intColumn).Value
Msgbox strFieldname
Set theLabel = UserForm1.Controls.Add("Forms.Label.1", "Test" & intColumn, True)
With theLabel
.Caption = rsData.Fields.Item + strFieldname
.Left = 100
.Width = 300
.Top = 15 * iItem
End With
But the result is the same.
I feel it needs to have the (" and ") and the .Value at the end.
Any suggestions please?
I added a value to C10 on the active worksheet and then did this in a userform and it worked as expected. Note that for testing I added the two constants and changed line 9.
In your code if you put a breakpoint at the .Caption = ... line, what is the value of rsData.Fields.Item?
Dim strFieldName As String
Dim theLabel As Control
Const iItem = 20
Const intColumn = 3
strFieldName = Cells(10, intColumn).Value
MsgBox strFieldName
Set theLabel = UserForm1.Controls.Add("Forms.Label.1", "Test" & intColumn, True)
With theLabel
.Caption = "marty" + strFieldName
.Left = 100
.Width = 300
.Top = 1 * iItem
End With
End Sub
In your code if you put a breakpoint at the .Caption = ... line, what is the value of rsData.Fields.Item?
ASKER
Yes Martin. That would work. As does this ....
This actually displayed the value of "theFieldname" from the recordset rsData. But the problem seems to be constructing a string to put in the line .Caption = that dynamically substitutes the actual field names for "TheFieldname".
.Caption = rsData.Fields.Item("theFieldname").Value
This actually displayed the value of "theFieldname" from the recordset rsData. But the problem seems to be constructing a string to put in the line .Caption = that dynamically substitutes the actual field names for "TheFieldname".
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'm glad that works.
ASKER
Sorry Martin. I was foolishly thinking I had to construct the statement. I need more oxygen.
Thanks for your help.
Yes
.Caption = rsData.Fields.Item(3).Valu e
would work too.
Thanks for your help.
Yes
.Caption = rsData.Fields.Item(3).Valu
would work too.
ASKER
Thanks Martin.
You're welcome and I'm glad I was able to help.
If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.
Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015 and 2016
Experts Exchange Top Expert Visual Basic Classic 2012 to 2016
If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.
Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015 and 2016
Experts Exchange Top Expert Visual Basic Classic 2012 to 2016