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
