Results 1 to 9 of 9
  1. #1
    aamer is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Location
    Pakistan
    Posts
    276

    Help Needed Fixing the Code

    I am using the following code in a list box, its working fine.
    With this list box I select the customers whose report I want to view.

    How can I change this code that by cliking on the button it selects all the customers.


    See the Code I am using.

    Private Sub cmdOpenReportMultiple_Click()
    On Error GoTo Err_Handler

    Const REPORTNAME = "General Purchase"
    Const MESSAGETEXT = "No Account Title's Selected"

    Dim varItem As Variant
    Dim strCustomerIDList As String
    Dim strCriteria As String
    Dim ctrl As Control

    Set ctrl = Me.lstCustomers

    If ctrl.ItemsSelected.Count > 0 Then
    For Each varItem In ctrl.ItemsSelected
    strCustomerIDList = strCustomerIDList & "," & ctrl.ItemData(varItem)
    Next varItem

    ' remove leading comma
    strCustomerIDList = Mid(strCustomerIDList, 2)

    strCriteria = "CoId In(" & strCustomerIDList & ")"

    DoCmd.OpenReport REPORTNAME, _
    View:=acViewPreview, _
    WhereCondition:=strCriteria
    Else
    MsgBox MESSAGETEXT, vbExclamation, "Invalid operation"
    End If
    Exit_Here:
    Exit Sub

    Err_Handler:


    MsgBox Err.Description, vbExclamation, "Error"
    Resume Exit_Here
    End Sub

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    You have a very short customer list?

    Could just run unfiltered report.

    If strCustomerIDList <> "" Then strCriteria = "CoId In(" & strCustomerIDList & ")"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    aamer is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Location
    Pakistan
    Posts
    276
    Thx for your help june7

    I used the following code and it worked

    On Error GoTo Err_Handler

    Dim u As Integer
    Dim ctrl As Control

    Set ctrl = Me.lstCustomers

    For u = 0 To ctrl.ListCount - 1
    ctrl.Selected(u) = True
    Next u

    Exit_Here:
    Exit Sub

    Err_Handler:
    MsgBox Err.Description, vbExclamation, "Error"
    Resume Exit_Here
    End Sub


    For your info, I have no knowledge on writing codes or understanding them as someone would with knowledge of using VBA, all I do is use a little brain and play with the codes, pick something from here and there and use it to my needs.
    ofCouse when u reply with your guidance it gives me the idea's how to play with the codes.

    One thing I could not figure out where I further need your guidance. What and where should I add, that this report is emailed to XXXXXX@gmail.com


    thanks
    aamer

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    What?

    You want code to automatically email report?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    aamer is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Location
    Pakistan
    Posts
    276
    yes please

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Check out SendObject method. It will attach report as a PDF to an email.

    Or with report open in Print Preview, right click > Send To
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    aamer is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Location
    Pakistan
    Posts
    276
    June

    Thx for your help

    I added the following code it works as a charm but....


    DoCmd.SendObject acSendReport, "General Purchase wo Varification", acFormatPDF, "aamer.sheikh@gmail.com", , , "Supplier Purchase Ledger Ledger", "Attached please find the Supplier Purchase Ledger"


    I want to add the current date in the subject line of email. I don't know how to do that. Can you help me fix it.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Do concatenation:

    "Supplier Purchase Ledger " & Date(),
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    aamer is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Location
    Pakistan
    Posts
    276
    Thank you it works perfect now

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Date code needed
    By spider in forum Programming
    Replies: 1
    Last Post: 06-19-2013, 06:38 AM
  2. code needed to filter form using combo boxes
    By drjim in forum Programming
    Replies: 1
    Last Post: 06-29-2012, 01:50 PM
  3. Help fixing a code to change password programatically
    By smartflashes in forum Programming
    Replies: 3
    Last Post: 01-19-2012, 10:20 PM
  4. Help Needed Fixing User ID
    By aamer in forum Access
    Replies: 1
    Last Post: 10-29-2010, 07:27 PM
  5. Code needed when Database is Opened
    By Access_Headaches in forum Access
    Replies: 7
    Last Post: 08-13-2010, 01:03 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums