Hi, sorry you replied before I got my other post up - the type mismatch error
was my fault and did not relate to this but the problem I am having is that
it seems the stringVar is empty (the message box is blank) so I think its how
I am assing lblType
I assign lblType to the recordset then run the code you gave me but its
coming up empty? Thanks for you time

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

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:

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

I forgot to append:

stringVar = stringVar & " " & CStr(lblType(i))

What does UBound(lblType) return? In other words, do you have any rows at all?

--
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, sorry you replied before I got my other post up - the type mismatch error
was my fault and did not relate to this but the problem I am having is that
it seems the stringVar is empty (the message box is blank) so I think its how
I am assing lblType
I assign lblType to the recordset then run the code you gave me but its
coming up empty? Thanks for you time

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

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:

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?


"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

I will give this a try - it seems like it will work - thanks again for all
your help


"Sue Mosher [MVP-Outlook]" wrote:

I forgot to append:

stringVar = stringVar & " " & CStr(lblType(i))

What does UBound(lblType) return? In other words, do you have any rows at all?

--
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, sorry you replied before I got my other post up - the type mismatch error
was my fault and did not relate to this but the problem I am having is that
it seems the stringVar is empty (the message box is blank) so I think its how
I am assing lblType
I assign lblType to the recordset then run the code you gave me but its
coming up empty? Thanks for you time

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

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:

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?


"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