Auto-separate Report

[Larz]

I have a report made which creates and groups data by customer, taken from a query that displays only active items. I have the page break set after each group to start a new customer with a new page. So the single report is multiple pages containing all active customers, where each new customer begins a new page. Now I have 50+ customers, however only a few are active at any given time.. so aside from creating a single report for each customer, is there a way to automatically create a report where it would make a single report for each customer?

My goal is to just call up the reports, then email it to the destined parties where it contains only their info.

 

[pbaldy]

This and the related link on looping should get you started:

http://www.granite.ab.ca/access/email/reporttomultiplerecipients.htm

 

[vbaInet]

By the way, you mentioned that you are using a Page Break control to start a new page for each customer, you don't need the Page Break control for this.

If you've already created a group based on the CustomerID field (which uniquely identifies each customer) then you can set the Force New Page property of this group's Footer section to After Section. Remember to remove the Page Break control if you're going to try this out or else you will get undesired results.

 

[Larz]

What about saving the report as multiple reports, say 10 active customers, one 48 page report, to save as 10 separate reports based on the grouping criteria?

 

[pbaldy]

I use the same basic technique but saving the report instead of emailing it (or both).

 

[Larz]

Paul, I read through the info, and forgive my ignorance, I have to create a form, then code a button to run the report, which will then take that 1 single report and split it into multiple ones?

The code on that page
Me.Filter = "ShipmentsID=" & Forms![Select Load List]![LoadID]
Me.FilterOn = True

goes into the report's on open event?

 

[pbaldy]

You don't have to create a form and button, it's just the most common way of letting the user start the process. If you have some other process in mind, we can come up with a way to start it.

Yes, that code goes in the report's open event. If you didn't want to use a form, you could use a global variable instead. You just need some way for the VBA loop to pass the current value to the report.

 

[Larz]

OK, im on this again... I just made a simple form for the report with a command button which contains the loop code. I click the button and get Error (2046) the command or action 'SaveRecord'; isn't available now. I am assuming since there are multiple users and I do not have exclusive access to the report that I am getting that error?

 

[vbaInet]

It looks like Paul hasn't been online for a while so I'll respond.

The loop code wasn't given to you to give you some pointers. There are some parts you don't need - the SaveRecord line being one.

Let's see your loop code as you have it. Enclose it in code tags:

http://www.access-programmers.co.uk/forums/showthread.php?p=1009015#post1009015

 

[Larz]

Thx VBA, Im travelling today... all i did was copy the code from that page Paul gave. I will come back here when i can have the DB open.

 

[Larz]

I don't have the code saved anymore... so im back to having the report, and looking for guidance from here to just have it separate it by the field used to group the report, and it appears the site is down or i cannot access it.

 

[vbaInet]

Slow down a bit, let's take it one step after the other. Did you get post #6 working? If you didn't you can't go forward.

 

[Larz]

I had grabbed code from here... .granite.ab.ca/access/email/recordsetloop.htm but i can't access this site any more. So now, i am back at the beginning. All i have is the report right now. I want to understand what im doing too...

 

[Larz]

Ok here is the code i was directed to...

On Local Error GoTo Some_Err
    
    Dim MyDB As Database, RS As Recordset
    Dim strBody As String, lngCount As Long, lngRSCount As Long
    
    DoCmd.RunCommand acCmdSaveRecord
    Set MyDB = DBEngine.Workspaces(0).Databases(0)
    
    Me!txtProgress = Null

   Set RS = MyDB.OpenRecordset _
      ("Email - Outstanding Promos")
    lngRSCount = RS.RecordCount
    If lngRSCount = 0 Then
      MsgBox "No promo email messages to send.", vbInformation
    Else
      RS.MoveLast
      RS.MoveFirst
      Do Until RS.EOF
        lngCount = lngCount + 1
        lblStatus.Caption = "Writing Message " & CStr(lngCount) _
          & " of " & CStr(lngRSCount) & "..."
        strTo = RS!cEmailAddress
        intMessageID = Year(Now) & Month(Now) & Day(Now) & Fix(Timer) & "_MabryMail"
        ' Send the email using some technique or other
        RS.Edit
        RS("cpeDateTimeEmailed") = Now()
        RS.Update
        RS.MoveNext
      Loop
    
    End If
    RS.Close
    MyDB.Close
    Set RS = Nothing
    Set MyDB = Nothing
    Close
    
    Me!txtProgress = "Sent " & CStr(lngRSCount) & " emails."
    lblStatus.Caption = "Email disconnected"
    MsgBox "Done sending Promo email. ", vbInformation, "Done"
    lblStatus.Caption = "Idle..."
    Exit Sub
    
Some_Err:
    'MousePointer = 0
    MsgBox "Error (" & CStr(Err.Number) & ") " & Err.Description, _
        vbExclamation, "Error!"
    lblStatus.Caption = "Email disconnected"

 

[vbaInet]

It's not the code we're concerned about because that code is the second phase to your solution. Have you managed to accomplish what you were trying to do in post #6 with one customer? Look at your post #6 and Paul's follow-up post.

 

[Larz]

I entered that code onto the reports on open, it doesn't do anything, won't allow me to go from design view to report view... this was also copied code so i assume there are variables i need to enter which pertain to my database / records? |

whereas "ShipmentsID=" would be the field that i want the report to split up by?
Forms![Select Load List]![LoadID] woudl be the query my data comes from to populate the report?

"ShipmentsID=" & Forms![Select Load List]![LoadID]

 

[vbaInet]

Alright, so after you copied it you substituted the right field names and control names in right? Let's see your amended code.