How to assign database value to a variable

G

Guest

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
 
S

Sue Mosher [MVP-Outlook]

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

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
 
G

Guest

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 said:
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

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers


Eric J said:
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
Click to expand...
Click to expand...
 
S

Sue Mosher [MVP-Outlook]

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

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers


Eric J said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers


Eric J said:
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 said:
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];"

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
Click to expand...
Click to expand...
Click to expand...
 
S

Sue Mosher [MVP-Outlook]

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

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers


Eric J said:
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 said:
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 said:
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

:

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];"
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers


Eric J said:
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 said:
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.

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

:

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];"


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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers


Eric J said:
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 said:
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.

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

:

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];"


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
Click to expand...
Click to expand...
Click to expand...
 
S

Sue Mosher [MVP-Outlook]

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

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers


Eric J said:
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 said:
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 said:
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

:

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.

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

:

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];"


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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers


Eric J said:
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 said:
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.

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

:

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];"


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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers


Eric J said:
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 said:
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

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

:

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.

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

:

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];"


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
Click to expand...
Click to expand...
Click to expand...
 
S

Sue Mosher [MVP-Outlook]

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

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers


Eric J said:
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 said:
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 said:
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

:

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
Click to expand...
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

:

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.

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

:

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];"


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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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


Sue Mosher said:
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

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers


Eric J said:
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 said:
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?

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

:

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


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

:

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.

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

:

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];"


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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combo box value? 1
Loading Data into a droplist 3
How to create login form using DAO.recordset on a unbound form 3
Assign default value to Optional Variable 4
trying to assign a variable to the field part of recordset!field 2
Assign Selection to a Variable 2
How to get value from column in Datalist into variable 2
Quad Cascading ComboBoxes 3

Top