Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Aug 2004
    Posts
    47
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Separate Individual Reports by Account ID (2003)

    I know this has been discussed before but I still need a little extra help.

    I am trying to create reports in Access and save them under my c: folder so I can email report to each individual. Each individual can only view his/her account info but not others. I don’t know how to separate individual report by Account ID. I am attaching my DB here. There is obviously something missing in the coding because it generate reports that everyone can view other’s account info too.

    Thank you in advance for your assistance.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Separate Individual Reports by Account ID (2003)

    The variable gstrAccountID should be a public variable defined in the standard module.
    There should be code in the On Open event of the report that sets the record source of the report to an SQL string that selects the records for the AccountID.
    You should select unique values of AccountID in the recordset, otherwise you'll produce the same report several times.

    See attached version (ideas from <post:=275,460>post 275,460</post:>, where you probably found the code too)
    Attached Files Attached Files

  3. #3
    Lounger
    Join Date
    Aug 2004
    Posts
    47
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Separate Individual Reports by Account ID (2003)

    Thanks a lot. It works.

  4. #4
    Lounger
    Join Date
    Aug 2004
    Posts
    47
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Separate Individual Reports by Account ID (2003)

    Hans,

    Can I ask another related question?

    1. How can I name the file saved under the folder as “AccountID+ Email Address”? Currently it is just AccountID.
    2. How can I save the file as PDF file instead of SNP file? I have full version of Adobe already.

    Thanks.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Separate Individual Reports by Account ID (2003)

    1. The line that saves the .snp file is

    strFilename = "C:Tempaccount" & gstrAccountID & ".snp"

    To add the e-mail address:

    strFilename = "C:Tempaccount" & gstrAccountID & rst![EMail Address] & ".snp"

    But keep in mind that the file name will look strange because of the dots in the e-mail address.

    2) Access 2003 doesn't have built-in support for exporting to PDF. You can set the printer to Adobe PDF and print the report; I think it will use the caption of the report as filename. Or you can download and install Stephen Lebans' free ReportToPDF. The download comes with a sample database that demonstrates how to use it.

  6. #6
    Lounger
    Join Date
    Aug 2004
    Posts
    47
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Separate Individual Reports by Account ID (2003)

    Hans,

    Thanks for your reply. I will take a look at the solution on PDF this afternoon. I just tested on the file name but after I hit the testrpt button, it tells me that “item not found in this collection.” What does that mean? Thanks.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Separate Individual Reports by Account ID (2003)

    That error message means that the code specifies a non-existing item, for example a report name that doesn't exist, or something like that. To get more detailed information, turn the line
    <code>
    On Error GoTo ErrHandler
    </code>
    into a comment temporarily by inserting an apostrophe ' in front of it:
    <code>
    ' On Error GoTo ErrHandler
    </code>
    Now click the button again. When the error message is shown, click Debug, and see which line is highlighted. This may give you an idea where the problem lies.

  8. #8
    Lounger
    Join Date
    Aug 2004
    Posts
    47
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Separate Individual Reports by Account ID (2003)

    Hans,

    It highlighted the line
    strFilename = "C:Tempaccount" & gstrAccountID & rst![EMail Address] & ".snp"

    The program runs ok if I delete the “& rst![Email Address]”, do I have to define “Email Address” somewhere? Thanks.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Separate Individual Reports by Account ID (2003)

    I apologize, it's my fault, I should have tested it before posting. The EMail Address field is not in the recordset.

    Change the line

    Set rst = dbs.OpenRecordset("SELECT DISTINCT AccountID FROM teststatement", dbOpenForwardOnly)

    to

    Set rst = dbs.OpenRecordset("SELECT DISTINCT AccountID, [EMail Address] FROM teststatement", dbOpenForwardOnly)

    PS Do characters such as brackets in your own posts look strange to you?

  10. #10
    Lounger
    Join Date
    Aug 2004
    Posts
    47
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Separate Individual Reports by Account ID (2003)

    Thanks. Now it works.

    The characters in my own posts look ok for me. Why do you ask? Does it show different to others?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Separate Individual Reports by Account ID (2003)

    Below is an example of what I see. It might well be my browser...
    Attached Images Attached Images
    • File Type: png x.PNG (1.7 KB, 0 views)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •