Help with List box

[Stockwell43]

Hello,

I wondering if someone could please help me with a situation. I am asked to
create a database where the user fills in Customer Name and Address and also
select numerous documents needed to complete the loan. Then click a button to
create an email with that information in it. So my questions are the
following:

1. Can I create a list box so the user can select multiple documents say
maybe 8 out 20 instead of one like in a CBO?

2. After they make their selections, can they click a button to open an
email with these selections listed in the email?

I have created an email to pick up information from textboxes but from a
list box especially if numerous selections have made.

Any help would be most appreciated.

Thanks!!!

 

[Klatuu]

Yes, you can use a multi select list box. Then when you want to create the
email, you can call a function like this and it will return a string of all
the selected items. In this case, the items are separated with commas, so
they would be presented as:
HUD1, Good Faith Estimate, Inspection Report
If you want them to list like thisL
HUD1
Good Faith Estimate
Inspection Report

You can replace the commas with a line feed.

Private Function DocList() As STring
Dim strDocs As String

With Me.MyListBox
For Each varItem In .ItemsSelected
strDocs = strDocs & .ItemData(varItem) & ", "
Next varItem
End With
DocList = Left(strDocs, Len(strWhere) -1)
End Function

So to use it, append it to the text as

strMsgText = strMsgText & DocList

or to get a vertical list

strMsgText = strMsgText & Replace(DocList, ", ", vbNewLine)

 

[Stockwell43]

Hi Klatuu, thank you for responding.

Do I create a module for this to go in or does this go in the onclick event
of the button for the email? Sorry, I never used a list box before so this is
kind of new to me.

Thanks!

 

[Klatuu]

Put the function in the form's module. Usually, you put subs and functions
that are not event procedures at the top of the module After the Option
statments and any Module level variable declarations and before any event
procedures.
I assume you have a command button or some other event where you create the
email and send it. You would call it from there to add it to your message
text.

 

[Stockwell43]

I will start creating this tomorrow. It will be a simple quick database(I
hope). I will have the command button to open a new email and use the code to
prefill it with the list box information. When I get to that point, If Irun
into a situation, I will re-post. I am hoping this will go smooth.

Thank you again one of many times helping me. I will let you know how I make
out.

Thanks!!!

 

[Stockwell43]

Hi Klatuu,

I followed the instructions on placing a multi select list on my form from
this link:

http://support.microsoft.com/kb/827423

Works great. I have the text box "AddInfo" added and when I select from the
list box and click Display info button everything I selected shows up in the
textbox. However, The code I am using for the email (in the onclick event) is:

Private Sub cmdSendEmail_Click()
On Error GoTo EH
Dim SendTo As String, SendCC As String, MySubject As String, MyMessage
As String
SendTo = ""
SendCC = ""

MySubject = "Additional Information Needed"
MyMessage = Me.LoanNumber & vbCr & Me.CustomerFirst & " " &
Me.CustomerLast & vbCr & Me.ErrorCode & _
vbCr & Me.StatusUpdate & vbCr & Me.AddInfo
DoCmd.SendObject acSendNoObject, , , SendTo, SendCC, , MySubject,
MyMessage, True

EH:
If Err.Number = 2501 Then
MsgBox "This email message has not been sent. Message has been
cancelled."
End If
End Sub

This code works fine in all my databases with this feature but when I put it
in this database it gets hung up.
This Part: Private Sub cmdSendEmail_Click() is highlighted in yellow and
this part: .LoanNumber is highlighted blue.

I don't understand as I am only trying to pull the information from the text
the list box info is filling. It works fine for my comments field which is
similar because I can have a lot of choices selected so I made it a memo
field.

I have your code at the top so it is the piece of code behind the form.

What am I doing wrong??? The only difference is the list box and the data
isn't even pulling from that?

Thanks!!!

 

[Stockwell43]

Sorry, it works now. In your code a small piece was missing but I filled it
in and it works fine now.

For Each varItem In .ItemsSelected, After the In I put in the name of the
List box. My error for not looking harder. Sorry about all that but thank you
very much for your code and I have it saved for future use. You're very
knowledgable along with many others on this forum who have helped out many
times over and I want to say thanks to all for your help and patients!!!!

 

[Stockwell43]

Hi Klatuu,

I do have one question. When the choices display in the text box, it appears:
item 1,item 2,item 3,item 4

How can I space it after the commas to look like this:
item 1, item 2, item 3, item 4

Thanks!!