Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Reports and VBA?? (Access 2007)

    HI, Glad to see the sites come back up.

    OK, While I'm familier with VBA within Access, I'm a bit of a novice when it comes to reports, I have detailed the task I'm looking to perform:

    We need to extract from a Filtered list specific Data, and then create an individual Document which is structured by an Access report for Emailing (This should all be automatic and will be driven by a SendObject command in a macro and emailed via Outlook 2007). In the perfect world this should be an automatic process for the operator.

    I see the task break down into these steps:

    1) Create a table with one entry and link it to the Report (The file name can be structured using the date, it would be nice if we could use a unique indentifiying number contained within the data row in conjunction with it.) ( all entries will have the same structure)

    2) Output the report to a holding directory and then repeat for all entries in the Table.

    3) Send the created Files via Outlook to specified addresses.

    Is this possible, I'm not scared of a bit of Fiddling about with code to get it right, but don't know the Report object tags for dynamicly changing the Report name and Record Source.

    This is probably clear as mud, but I can always supply more detail if necessary.

    TX

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

    Re: Reports and VBA?? (Access 2007)

    That should be possible, but can you provide some more information?
    - Where do the data come from?
    - Where do the e-mail addresses come from?
    Please try to give specific answers.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Reports and VBA?? (Access 2007)

    Sorry I thought a Generalization would do it:

    The Data Comes from a Table Called [CT Reports] containing [ID], [Amount],[Agent] (There are more but the additional fields are handled by the Report)

    The email Address will be entered into the Macros SendObject Command so we don't have to worry about it to much, But I can hardcode them if necessary as they will change very rarely, and it may be quicker anyway if you've a way of outputting to Outlook direct from VBA I'd be gratefull.

    The report is named: Reports![Play] at the moment and we will need to change the underlying Table referenced via [play].RecordSource

    Hope that enough. Thank-You!

  4. #4
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Reports and VBA?? (Access 2007)

    I've been working on it by the Way and I'm wracking my brains about how to Run Code thats been constructed via a String:

    Comp1 = "Reports![" & "Play " & Date & "].RecordSource = @CS Complete@"
    Comp1 = Replace(Com1, "@", Chr(34))

    To give the result:

    Reports![Play 10/10/2007].RecordSource = "CS Complete"

    So I can change the Record Source refered to on printing????

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

    Re: Reports and VBA?? (Access 2007)

    See <post:=275,460>post 275,460</post:> - it contains sample code that shows how to send individual reports for each record in a table or query.

  6. #6
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Reports and VBA?? (Access 2007)

    Thanks that gives me stuff to play with.

    My VBA vocab is so limited I sometimes get completely stuck.

    Oh well "Practise, Practise,Practise"

Posting Permissions

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