I created a custom form that has 3 combo boxes pulling values from a SQL
database - the 2nd and 3rd combo boxes are filtered based on previous
selections. My problem is that I need the text value of the combo box so I
can put that selected value in the subject of the message. In the code below
I pass in the selected value of the combo box and try to get the Type name so
I can put that in the subject line (instead of the typeid) but when I try to
assign the result to a variable I get nothing - any suggestions are greatly
appreciated.

Sub FindTypeName(TypeID)
Dim rstProds
Dim strSQL
Dim lblType
On Error Resume Next
If TypeID "" Then
Set rstProds = CreateObject("ADODB.Recordset")
strSQL = "SELECT [TYPE] " & _
"from TASKTYPE " & _
"WHERE [TYPEID] =" & TypeID & " " & _
"ORDER BY [TYPE];"
rstProds.Open strSQL, m_adoNW, _
adOpenForwardOnly, adLockReadOnly
If rstProds.State = adStateOpen Then
lblType = rstProds.GetRows
rstProds.Close
End If
End If
Set rstProds = Nothing
End Sub

Don't you need single quotation marks around the string value in the SELECT statement? Like:

strSQL = "SELECT [TYPE] " & _
"from TASKTYPE " & _
"WHERE [TYPEID] ='" & TypeID _
"' ORDER BY [TYPE];"

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003
http://www.turtleflock.com/olconfig/index.htm
and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumpstart.aspx

"Eric J" Eric wrote in message ...
I created a custom form that has 3 combo boxes pulling values from a SQL
database - the 2nd and 3rd combo boxes are filtered based on previous
selections. My problem is that I need the text value of the combo box so I
can put that selected value in the subject of the message. In the code below
I pass in the selected value of the combo box and try to get the Type name so
I can put that in the subject line (instead of the typeid) but when I try to
assign the result to a variable I get nothing - any suggestions are greatly
appreciated.

Sub FindTypeName(TypeID)
Dim rstProds
Dim strSQL
Dim lblType
On Error Resume Next
If TypeID "" Then
Set rstProds = CreateObject("ADODB.Recordset")
strSQL = "SELECT [TYPE] " & _
"from TASKTYPE " & _
"WHERE [TYPEID] =" & TypeID & " " & _
"ORDER BY [TYPE];"
rstProds.Open strSQL, m_adoNW, _
adOpenForwardOnly, adLockReadOnly
If rstProds.State = adStateOpen Then
lblType = rstProds.GetRows
rstProds.Close
End If
End If
Set rstProds = Nothing
End Sub

Hi, I am not sure if that would apply in this case since the TypeID is a
number value? I will give it a try but I was thinking it might be with how I
create the recordset and get the value out of the recordset but I am not
sure. I will try your suggestion - thanks

"Sue Mosher [MVP-Outlook]" wrote:

Don't you need single quotation marks around the string value in the SELECT statement? Like:

strSQL = "SELECT [TYPE] " & _
"from TASKTYPE " & _
"WHERE [TYPEID] ='" & TypeID _
"' ORDER BY [TYPE];"

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003
http://www.turtleflock.com/olconfig/index.htm
and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumpstart.aspx

"Eric J" Eric wrote in message ...
I created a custom form that has 3 combo boxes pulling values from a SQL
database - the 2nd and 3rd combo boxes are filtered based on previous
selections. My problem is that I need the text value of the combo box so I
can put that selected value in the subject of the message. In the code below
I pass in the selected value of the combo box and try to get the Type name so
I can put that in the subject line (instead of the typeid) but when I try to
assign the result to a variable I get nothing - any suggestions are greatly
appreciated.

Sub FindTypeName(TypeID)
Dim rstProds
Dim strSQL
Dim lblType
On Error Resume Next
If TypeID "" Then
Set rstProds = CreateObject("ADODB.Recordset")
strSQL = "SELECT [TYPE] " & _
"from TASKTYPE " & _
"WHERE [TYPEID] =" & TypeID & " " & _
"ORDER BY [TYPE];"
rstProds.Open strSQL, m_adoNW, _
adOpenForwardOnly, adLockReadOnly
If rstProds.State = adStateOpen Then
lblType = rstProds.GetRows
rstProds.Close
End If
End If
Set rstProds = Nothing
End Sub

Ah, that was a key detail.

What about the lblType array? That should contain the data from the recordset, but I don't see that you're doing anything with it.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003
http://www.turtleflock.com/olconfig/index.htm
and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumpstart.aspx

"Eric J" wrote in message ...
Hi, I am not sure if that would apply in this case since the TypeID is a
number value? I will give it a try but I was thinking it might be with how I
create the recordset and get the value out of the recordset but I am not
sure. I will try your suggestion - thanks

"Sue Mosher [MVP-Outlook]" wrote:

Don't you need single quotation marks around the string value in the SELECT statement? Like:

strSQL = "SELECT [TYPE] " & _
"from TASKTYPE " & _
"WHERE [TYPEID] ='" & TypeID _
"' ORDER BY [TYPE];"


"Eric J" Eric wrote in message ...
I created a custom form that has 3 combo boxes pulling values from a SQL
database - the 2nd and 3rd combo boxes are filtered based on previous
selections. My problem is that I need the text value of the combo box so I
can put that selected value in the subject of the message. In the code below
I pass in the selected value of the combo box and try to get the Type name so
I can put that in the subject line (instead of the typeid) but when I try to
assign the result to a variable I get nothing - any suggestions are greatly
appreciated.

Sub FindTypeName(TypeID)
Dim rstProds
Dim strSQL
Dim lblType
On Error Resume Next
If TypeID "" Then
Set rstProds = CreateObject("ADODB.Recordset")
strSQL = "SELECT [TYPE] " & _
"from TASKTYPE " & _
"WHERE [TYPEID] =" & TypeID & " " & _
"ORDER BY [TYPE];"
rstProds.Open strSQL, m_adoNW, _
adOpenForwardOnly, adLockReadOnly
If rstProds.State = adStateOpen Then
lblType = rstProds.GetRows
rstProds.Close
End If
End If
Set rstProds = Nothing
End Sub

Sorry for leaving that out - I think you have stated my problem but I am not
sure how to fix it (obviously a novice with this) - I want to put the result
of this query into the body of the message - the reason for the query is
because I have combo boxes that pull values from a database and the user can
pick a value - then I need the result to be passed to the body of the message
- if I grab the value from the combo box it is the ID value and I need the
text - So I was trying to get the text value from this query and put the
result into a variable and then pass that into the message body - I assum its
not working because I am not assigning it to an array? So I am trying to see
how to get this value into a variable that I can pass to the message body.
Thanks for replying

"Sue Mosher [MVP-Outlook]" wrote:

Ah, that was a key detail.

What about the lblType array? That should contain the data from the recordset, but I don't see that you're doing anything with it.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003
http://www.turtleflock.com/olconfig/index.htm
and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumpstart.aspx

"Eric J" wrote in message ...
Hi, I am not sure if that would apply in this case since the TypeID is a
number value? I will give it a try but I was thinking it might be with how I
create the recordset and get the value out of the recordset but I am not
sure. I will try your suggestion - thanks

"Sue Mosher [MVP-Outlook]" wrote:

Don't you need single quotation marks around the string value in the SELECT statement? Like:

strSQL = "SELECT [TYPE] " & _
"from TASKTYPE " & _
"WHERE [TYPEID] ='" & TypeID _
"' ORDER BY [TYPE];"


"Eric J" Eric wrote in message ...
I created a custom form that has 3 combo boxes pulling values from a SQL
database - the 2nd and 3rd combo boxes are filtered based on previous
selections. My problem is that I need the text value of the combo box so I
can put that selected value in the subject of the message. In the code below
I pass in the selected value of the combo box and try to get the Type name so
I can put that in the subject line (instead of the typeid) but when I try to
assign the result to a variable I get nothing - any suggestions are greatly
appreciated.

Sub FindTypeName(TypeID)
Dim rstProds
Dim strSQL
Dim lblType
On Error Resume Next
If TypeID "" Then
Set rstProds = CreateObject("ADODB.Recordset")
strSQL = "SELECT [TYPE] " & _
"from TASKTYPE " & _
"WHERE [TYPEID] =" & TypeID & " " & _
"ORDER BY [TYPE];"
rstProds.Open strSQL, m_adoNW, _
adOpenForwardOnly, adLockReadOnly
If rstProds.State = adStateOpen Then
lblType = rstProds.GetRows
rstProds.Close
End If
End If
Set rstProds = Nothing
End Sub

GetRows returns an array of values. An array can be a variable. What you want is a string, though, right? So, you need to process the array. This is one way:

For i = 0 to UBound(lblType)
stringVar = CStr(lblType(i)) & " "
Next
MsgBox stringVar

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003
http://www.turtleflock.com/olconfig/index.htm
and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumpstart.aspx

"Eric J" wrote in message ...
Sorry for leaving that out - I think you have stated my problem but I am not
sure how to fix it (obviously a novice with this) - I want to put the result
of this query into the body of the message - the reason for the query is
because I have combo boxes that pull values from a database and the user can
pick a value - then I need the result to be passed to the body of the message
- if I grab the value from the combo box it is the ID value and I need the
text - So I was trying to get the text value from this query and put the
result into a variable and then pass that into the message body - I assum its
not working because I am not assigning it to an array? So I am trying to see
how to get this value into a variable that I can pass to the message body.
Thanks for replying

"Sue Mosher [MVP-Outlook]" wrote:

Ah, that was a key detail.

What about the lblType array? That should contain the data from the recordset, but I don't see that you're doing anything with it.

"Eric J" wrote in message ...
Hi, I am not sure if that would apply in this case since the TypeID is a
number value? I will give it a try but I was thinking it might be with how I
create the recordset and get the value out of the recordset but I am not
sure. I will try your suggestion - thanks

"Sue Mosher [MVP-Outlook]" wrote:

Don't you need single quotation marks around the string value in the SELECT statement? Like:

strSQL = "SELECT [TYPE] " & _
"from TASKTYPE " & _
"WHERE [TYPEID] ='" & TypeID _
"' ORDER BY [TYPE];"


"Eric J" Eric wrote in message ...
I created a custom form that has 3 combo boxes pulling values from a SQL
database - the 2nd and 3rd combo boxes are filtered based on previous
selections. My problem is that I need the text value of the combo box so I
can put that selected value in the subject of the message. In the code below
I pass in the selected value of the combo box and try to get the Type name so
I can put that in the subject line (instead of the typeid) but when I try to
assign the result to a variable I get nothing - any suggestions are greatly
appreciated.

Sub FindTypeName(TypeID)
Dim rstProds
Dim strSQL
Dim lblType
On Error Resume Next
If TypeID "" Then
Set rstProds = CreateObject("ADODB.Recordset")
strSQL = "SELECT [TYPE] " & _
"from TASKTYPE " & _
"WHERE [TYPEID] =" & TypeID & " " & _
"ORDER BY [TYPE];"
rstProds.Open strSQL, m_adoNW, _
adOpenForwardOnly, adLockReadOnly
If rstProds.State = adStateOpen Then
lblType = rstProds.GetRows
rstProds.Close
End If
End If
Set rstProds = Nothing
End Sub