List Box Data Transfer (1 Viewer)

[Novice1]

I have a table: Field Name = ClassReasonID, Data Type = Number, List Box, Data source is another table.

The list box field works fine in the form. The data in the query looks fine.

I'm trying to write an e-mail but when I reference the ClassReasonID I get a "Type Mismatch" error.

I tried referencing ClassReasonID.value but get the same error

With a single value field I have no problem but the multi value is giving me fits

__________________________

DoCmd.SendObject acSendNoObject, , acFormatTXT, Me.SubjectEmail, , , "Classification Update", Me.Grade & " " & StrConv(Left([RName], InStr([RName], ",") - 1), 3) & ", " & _
vbCr & vbCr & "We recently received and processed the following classification update from your unit:" & vbCr & vbCr & _
" Individual: " & Me.Grade & " " & Me.RName & vbCr & _
" Classification Update: " & Me.ClassReasonID & vbCr & _
" Date Updated: " & Format([DateUpdated], "dd mmm yy") & vbCr & vbCr & _
"Please let us know if you have any questions or concerns." & vbCr & vbCr & _
"vr" & vbCr & vbCr & vbCr & _
[TFirstName] & " " & [TLastName] & ", " & [TechTitle] & vbCr & _
[Position] & vbCr & _
[TechPhone], True

 

[sneuberg]

Where is this error occurring? In the concatenation of the message text? What field is mulivalued? Could you upload this database?

 

[Novice1]

The error occurs when the e-mail is constructed. Works fine without the Me.ClassReasonID reference (which is the multivalue field. Too difficult to send database (FE BE split; personal data contained therein).

 

[sneuberg]

I can't replicate the error. Please look at the attached database and see if you can see what the difference between your situation and this is. Note: I took out

 StrConv(Left([RName], InStr([RName], ",") - 1), 3) &

of the message text as it was causing me problems and didn't seem related to your problem.

 

[Novice1]

Steve ... thanks.

I don't have a problem when using the combobox, it's when I use a listbox with multiple values. The listbox saves the multi values in ClassReasonID, which show when I run the query (see atch) but not when I run the code for the e-mail in the form I get an error (Type Mismatch).

When using a combobox I show the relationship in the query with the feeding table but apparently listboxes store the data differently because I cannot show that relationship.

Am I referencing the field incorrectly in the e-mail?

 

[sneuberg]

Sorry I've never worked with multivalued fields before but I guess it's time I learn a bit. I finally got the field defined the way you do and get your error. So the question is, if it's not a string or variant what is it? Debug.Print VarType(Me.ClassReasonID) produces 8204 and a google search of that seem to indicate it's an array. So if you change

Me.ClassReasonID

to

Me.ClassReasonID(0)

you get rid of the error but that produces 1, the value of the ID. That's probably not what you want but we are making progress.

What do you want? The selected values in the second column; comma delimited?

 

[Novice1]

I'm looking for the selected values in the second column; comma delimited

I'm still hunting the web for a solution. Very frustrating. Thanks for your help

 

[sneuberg]

Yeah this is frustrating. A query of the table shows that field exactly like you want it but if you do a DLookup on the field you get the comma delimited IDs. There's got to be a simple answer to this.

 

[sneuberg]

I think the way to handle this is just like any other listbox, i.e., iterate through is and form a string. This at least works.

Dim strMessageText As String
Dim lngRow As Long
Dim strMsg As String

With Me.ClassReasonID
    For lngRow = 0 To .ListCount - 1
        If .Selected(lngRow) Then
            strMsg = strMsg & ", " & .Column(2, lngRow)
        End If
    Next lngRow
End With

' strip off leading comma and space
If Len(strMsg) > 2 Then
    strMsg = Mid(strMsg, 3)
End If

strMessageText = Me.Grade & " " & StrConv(Left([RName], InStr([RName], ",") - 1), 3) & ", " & _
vbCr & vbCr & "We recently received and processed the following classification update from your unit:" & vbCr & vbCr & _
" Individual: " & Me.Grade & " " & Me.RName & vbCr & _
" Classification Update: " & strMsg & vbCr & _
" Date Updated: " & Format([DateUpdated], "dd mmm yy") & vbCr & vbCr & _
"Please let us know if you have any questions or concerns." & vbCr & vbCr & _
"vr" & vbCr & vbCr & vbCr & _
[TFirstName] & " " & [TLastName] & ", " & [TechTitle] & vbCr & _
[Position] & vbCr & _
[TechPhone]

DoCmd.SendObject acSendNoObject, , acFormatTXT, Me.SubjectEmail, , , "Classification Update", strMessageText, True