UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

3 Pages V   1 2 3 >  
 Reply to this topicStart new topic
> "selecing" All Items In A List Box    
 
   
jcbrackett
post Mar 11 2012, 06:30 PM
Post #1

UtterAccess Member
Posts: 43
I want to select all items in a list box before it gets emailed into the body of an email. Problem is the code i currently have does not "actually" select them all, it just highlights them. If i manually select a row in the listbox it will send that out just fine, but if i highlight multiple rows it will only send out one. Here is the code that I have so far, everything works fine except only one row gets sent to the body of them email, i want them all sent.

CODE
Private Sub Command12_Click()
Dim subject As String, Body As String
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
  
  On Error Resume Next
  Set OutApp = GetObject(, "Outlook.Application")
  If OutApp Is Nothing Then
    Set OutApp = CreateObject("Outlook.Application")
  End If
  On Error GoTo 0
  
  Set OutMail = OutApp.CreateItem(olMailItem)
  
  With OutMail
  
  For intCurrentRow = 0 To ListBox1.ListCount - 1
ListBox1.Selected(intCurrentRow) = True
Next intCurrentRow


  
      
    .To = Me.Combo0.Column(1)
    .subject = [Text2]
    .Body = [Text3] & vbNewLine & vbNewLine & Me.ListBox1.Column(1) & ", " & Me.ListBox1.Column(2) & ", " & Me.ListBox1.Column(3) & ", " & Me.ListBox1.Column(4) & ", " & Me.ListBox1.Column(5)
    .Send
  End With
  
  Set OutMail = Nothing
  Set OutApp = Nothing
  
End Sub
Go to the top of the page
 
+
theDBguy
post Mar 11 2012, 06:35 PM
Post #2

Access Wiki and Forums Moderator
Posts: 47,949
From: SoCal, USA
Hi,

What version of Access are you using? Knowing the Access version may help us determine a more appropriate response to your question.

Your code gives me the impression that your Listbox is not a multi-select listbox. If it is, you'll have to loop through the ItemsSelected collection to get alll the selections into your email.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)
Go to the top of the page
 
+
jcbrackett
post Mar 11 2012, 07:13 PM
Post #3

UtterAccess Member
Posts: 43
It is Access 2007 where i do a majority of the coding. I have the property box as extended for multi-select. I am not quit sure how to loop through each row to select it before it is placed into the body of the email.
Go to the top of the page
 
+
theDBguy
post Mar 11 2012, 09:49 PM
Post #4

Access Wiki and Forums Moderator
Posts: 47,949
From: SoCal, USA
QUOTE (jcbrackett @ Mar 11 2012, 05:13 PM) *
...I am not quit sure how to loop through each row to select it before it is placed into the body of the email.

You are already doing that. Your code selects all the items in the Listbox, doesn't it? If so, all you need to do next is loop through the selected items. Here's an example of how to loop through the selected items in a listbox:

Dim varItem As Variant
For Each varItem In Me.ListboxName.ItemsSelected
Debug.Print Me.ListboxName.ItemData(varItem)
Next

Just my 2 cents.. (IMG:style_emoticons/default/2cents.gif)
Go to the top of the page
 
+
jcbrackett
post Mar 11 2012, 10:00 PM
Post #5

UtterAccess Member
Posts: 43
Yes it actually does select them all, but it does not show them in the body of the email. I added the code you suggested and nothing in the body as well. If I manually highlight them it will only place one row in the body.
Go to the top of the page
 
+
theDBguy
post Mar 11 2012, 10:02 PM
Post #6

Access Wiki and Forums Moderator
Posts: 47,949
From: SoCal, USA
Hi,

QUOTE (jcbrackett @ Mar 11 2012, 08:00 PM) *
Yes it actually does select them all, but it does not show them in the body of the email. I added the code you suggested and nothing in the body as well. If I manually highlight them it will only place one row in the body.

The code I gave you only shows you how to loop through the selections. The selections showed up in the Immediate Window, didn't they?

To add them to your email body, you will have to use that technique and modify your code.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)
Go to the top of the page
 
+
jcbrackett
post Mar 11 2012, 10:20 PM
Post #7

UtterAccess Member
Posts: 43
I am not sure what the code you suggested actually does. I am kinda of dumb when it comes to listboxes. I want it to select them all, which it does. but problem is they do not show in the body of the email. I am so lost. I have been searching for a while on how to do this.
Go to the top of the page
 
+
theDBguy
post Mar 11 2012, 11:56 PM
Post #8

Access Wiki and Forums Moderator
Posts: 47,949
From: SoCal, USA
Okay, looking back at your code, it looks like your listbox has more than one column. If you're always going to send everything in the listbox anyway, it might be easier to just work with its Row Source.

Can you post the SQL for the Row Source of the Listbox? Thanks.
Go to the top of the page
 
+
arnelgp
post Mar 12 2012, 01:58 AM
Post #9

UtterAccess Ruler
Posts: 1,090
you should change this portion:
CODE
    .Body = [Text3] & vbNewLine & vbNewLine & Me.ListBox1.Column(1) & ", " & Me.ListBox1.Column(2) & ", " & Me.ListBox1.Column(3) & ", " & Me.ListBox1.Column(4) & ", " & Me.ListBox1.Column(5)


to:
CODE
    Dim varValue as Variant
    Dim strBody as String
    
    strBody = [Text3] & vbNewLine
    For each varValue in Me.ListBox1.ItemSelected
        strBody = vbNewLine & Me.ListBox1.Column(1,varValue) & ", " & Me.ListBox1.Column(2,varValue) & ", " &  Me.ListBox1.Column(3,varValue) & ", " & Me.ListBox1.Column(4,varValue) & ", " & Me.ListBox1.Column(5,varValue)
    Next

    .Body = strBody


This post has been edited by arnelgp: Mar 12 2012, 01:59 AM
Go to the top of the page
 
+
jcbrackett
post Mar 12 2012, 07:19 AM
Post #10

UtterAccess Member
Posts: 43
QUOTE (arnelgp @ Mar 12 2012, 02:58 AM) *
you should change this portion:
CODE
    .Body = [Text3] & vbNewLine & vbNewLine & Me.ListBox1.Column(1) & ", " & Me.ListBox1.Column(2) & ", " & Me.ListBox1.Column(3) & ", " & Me.ListBox1.Column(4) & ", " & Me.ListBox1.Column(5)


to:
CODE
    Dim varValue as Variant
    Dim strBody as String
    
    strBody = [Text3] & vbNewLine
    For each varValue in Me.ListBox1.ItemSelected
        strBody = vbNewLine & Me.ListBox1.Column(1,varValue) & ", " & Me.ListBox1.Column(2,varValue) & ", " &  Me.ListBox1.Column(3,varValue) & ", " & Me.ListBox1.Column(4,varValue) & ", " & Me.ListBox1.Column(5,varValue)
    Next

    .Body = strBody


I tried this code but it errors when it goes to send the email. at the .send command
Go to the top of the page
 
+
jcbrackett
post Mar 12 2012, 07:21 AM
Post #11

UtterAccess Member
Posts: 43
QUOTE (theDBguy @ Mar 12 2012, 12:56 AM) *
Okay, looking back at your code, it looks like your listbox has more than one column. If you're always going to send everything in the listbox anyway, it might be easier to just work with its Row Source.

Can you post the SQL for the Row Source of the Listbox? Thanks.



rowsource for the list box is:
SELECT [XqryAGEDfor email].[Tech Number], [XqryAGEDfor email].[Date Issued], [XqryAGEDfor email].[Equipment Name], [XqryAGEDfor email].[Access Card ID], [XqryAGEDfor email].[Serial Number] FROM [XqryAGEDfor email];
Go to the top of the page
 
+
arnelgp
post Mar 12 2012, 09:56 AM
Post #12

UtterAccess Ruler
Posts: 1,090
sorry for that, miss something there
CODE
    Dim varValue as Variant
    Dim strBody as String
    
    strBody = [Text3] & vbNewLine
    For each varValue in Me.ListBox1.ItemSelected

        strBody = strBody & vbNewLine & Me.ListBox1.Column(1,varValue) & ", " & Me.ListBox1.Column(2,varValue) & ", " &  Me.ListBox1.Column(3,varValue) & ", " & Me.ListBox1.Column(4,varValue) & ", " & Me.ListBox1.Column(5,varValue)

    Next

    .Body = strBody


This post has been edited by arnelgp: Mar 12 2012, 09:59 AM
Go to the top of the page
 
+
jcbrackett
post Mar 12 2012, 10:29 AM
Post #13

UtterAccess Member
Posts: 43
still failing at the .send command.
Go to the top of the page
 
+
theDBguy
post Mar 12 2012, 10:30 AM
Post #14

Access Wiki and Forums Moderator
Posts: 47,949
From: SoCal, USA
QUOTE (jcbrackett @ Mar 12 2012, 05:21 AM) *
rowsource for the list box is:
SELECT [XqryAGEDfor email].[Tech Number], [XqryAGEDfor email].[Date Issued], [XqryAGEDfor email].[Equipment Name], [XqryAGEDfor email].[Access Card ID], [XqryAGEDfor email].[Serial Number] FROM [XqryAGEDfor email];

Okay, thanks. So, if it was me, I would just probably use something like:

CODE
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strBody as String

Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT [XqryAGEDfor email].[Tech Number], [XqryAGEDfor email].[Date Issued], [XqryAGEDfor email].[Equipment Name], [XqryAGEDfor email].[Access Card ID], [XqryAGEDfor email].[Serial Number] FROM [XqryAGEDfor email]", dbOpenSnapshot)

With rs
     Do While Not .EOF
          strBody = strBody & ![Tech Number] & ", " & ![Date Issued] & ", " & ![Equipment Name] & ", " & ![Access Card ID] & ", " & ![Serial Number] & vbCrLf
          .MoveNext
     Loop
End With

DoCmd.SendObject , , , Me.Combo0.Column(1), , , Me.Text2, Me.Text3 & vbCrLf & vbCrLf & strBody, False

rs.Close
Set rs = Nothing
Set db = Nothing

(untested)
Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

PS. Just as a note... Your listbox row source doesn't seem to have enough columns as compared to your original code where you're asking to email the 2nd to the 6th columns.

This post has been edited by theDBguy: Mar 12 2012, 10:31 AM
Go to the top of the page
 
+
jcbrackett
post Mar 12 2012, 10:39 AM
Post #15

UtterAccess Member
Posts: 43
error pops up with too few parameters. Expected 1. This line is highlighted in debug mode.

CODE
Set rs = db.OpenRecordset("SELECT [XqryAGEDfor email].[Tech Number], [XqryAGEDfor email].[Date Issued], [XqryAGEDfor email].[Equipment Name], [XqryAGEDfor email].[Access Card ID], [XqryAGEDfor email].[Serial Number] FROM [XqryAGEDfor email]", dbOpenSnapshot)
Go to the top of the page
 
+
jcbrackett
post Mar 12 2012, 10:40 AM
Post #16

UtterAccess Member
Posts: 43
My listbox only shows 5 columns, not sure what you mean.
Go to the top of the page
 
+
theDBguy
post Mar 12 2012, 10:42 AM
Post #17

Access Wiki and Forums Moderator
Posts: 47,949
From: SoCal, USA
Hi,

QUOTE (jcbrackett @ Mar 12 2012, 08:39 AM) *
error pops up with too few parameters. Expected 1. This line is highlighted in debug mode.

CODE
Set rs = db.OpenRecordset("SELECT [XqryAGEDfor email].[Tech Number], [XqryAGEDfor email].[Date Issued], [XqryAGEDfor email].[Equipment Name], [XqryAGEDfor email].[Access Card ID], [XqryAGEDfor email].[Serial Number] FROM [XqryAGEDfor email]", dbOpenSnapshot)

Is [XqryAGEDfor email] a query or a table. If it's a query, we will need the row source for it to fix the error.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)
Go to the top of the page
 
+
theDBguy
post Mar 12 2012, 10:44 AM
Post #18

Access Wiki and Forums Moderator
Posts: 47,949
From: SoCal, USA
QUOTE (jcbrackett @ Mar 12 2012, 08:40 AM) *
My listbox only shows 5 columns, not sure what you mean.

Listbox column index starts with 0. So, when your code has something like "Me.ListBox1.Column(5)," you are actually referring to the sixth column. I'm surprised you didn't get an error in your code if you only have 5 columns in your listbox.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)
Go to the top of the page
 
+
jcbrackett
post Mar 12 2012, 10:47 AM
Post #19

UtterAccess Member
Posts: 43
it is a query, if you mean all the rows for the query they are as follows in this order.

Tech Info, Date Issued, Equipment Name, Access Card ID, Serial Number, Days In Inventory, Days On Truck, tech Number.

I only want the Tech Number, Date Issued, Equipment Name, Access Card ID, Serial Number in the body of the email though.

Also after i were to get this working i need to trim the Date Issued data down to just the date as it will show the time issued if not. In the column the date comes before the time stamp, if that matters.
Go to the top of the page
 
+
theDBguy
post Mar 12 2012, 10:51 AM
Post #20

Access Wiki and Forums Moderator
Posts: 47,949
From: SoCal, USA
Hi,

QUOTE (jcbrackett @ Mar 12 2012, 08:47 AM) *
it is a query, if you mean all the rows for the query...

I'm sorry, I still had the Listbox in mind when I wrote the above. I meant, we need to see the SQL for that query. I need to determine if it is a parameter query.

Thanks.
Go to the top of the page
 
+
« Next Oldest · Access Modules · Next Newest »
 

3 Pages V   1 2 3 >
Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 21st May 2013 - 12:32 PM