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

Thanks! Yes, that seems to be my problem - I need it as a string not an array
- I will try this - thanks again!

"Sue Mosher [MVP-Outlook]" wrote:

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

I tried this and got this message:
Type Mismatch: Cannot coerce parameter value. Outlook cannot translate your
string

here is my code:

Sub FindTypeName(CatID)
Dim rstProds
Dim strSQL

On Error Resume Next
If CatID "" Then
Set rstProds = CreateObject("ADODB.Recordset")
strSQL = "SELECT [TYPE] " & _
"from TASKTYPE " & _
"WHERE [TYPEID] =" & CatID & " " & _
"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
For i = 0 To UBound(lblType)
stringVar = CStr(lblType(i)) & " "
Next
MsgBox stringVar
End Sub

"Sue Mosher [MVP-Outlook]" wrote:

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

Which statement raises that error? What does lblType(i) return? Does lblType(i, 0) work? Will there ever be more than one item with the same TYPEID value?

--
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 ...
I tried this and got this message:
Type Mismatch: Cannot coerce parameter value. Outlook cannot translate your
string

here is my code:

Sub FindTypeName(CatID)
Dim rstProds
Dim strSQL

On Error Resume Next
If CatID "" Then
Set rstProds = CreateObject("ADODB.Recordset")
strSQL = "SELECT [TYPE] " & _
"from TASKTYPE " & _
"WHERE [TYPEID] =" & CatID & " " & _
"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
For i = 0 To UBound(lblType)
stringVar = CStr(lblType(i)) & " "
Next
MsgBox stringVar
End Sub

"Sue Mosher [MVP-Outlook]" wrote:

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


"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

Disregard my post about the type mismatch - that was my mistake but my real
problem is the stringVar value is empty or at least appears to be empty when
the MsgBox opens - I think it is again how I am assigning lblType variable?
here is the code I am using:

Sub FindTypeName(CatID)
Dim rstProds
Dim strSQL

On Error Resume Next
If CatID "" Then
Set rstProds = CreateObject("ADODB.Recordset")
strSQL = "SELECT [TYPE] " & _
"from TASKTYPE " & _
"WHERE [TYPEID] =" & CatID & " " & _
"ORDER BY [TYPE];"
rstProds.Open strSQL, m_adoNW, _
adOpenForwardOnly, adLockReadOnly
If rstProds.State = adStateOpen Then
lblType = rstProds.GetRows
For i = 0 To UBound(lblType)
stringVar = CStr(lblType(i)) & ""
Next
MsgBox stringVar
rstProds.Close
End If
End If
Set rstProds = Nothing

End Sub



"Sue Mosher [MVP-Outlook]" wrote:

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