Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Print report from multi-select form (2000 SP3)

    Two (related) questions:

    1) I have an array on a multiple selection form that is used to loop through a dataset and print a report of group membership based on the variables in the array. How can I take the value of each item in the array and pass it to the report so that a useful committee name appears on my report? (The criteria in the underlying query which is used to filter the group listing is not necessarily a group name. Membership in some of the groups is age-based). Since I have already got the information I want residing in the VBA I use to open the report as varItem I just need a way to insert the value of varItem on the report. I

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

    Re: Print report from multi-select form (2000 SP3)

    I don't think I understand this, but I would try a little harder to fit the criteria into a query, or in the Where-condition argument of DoCmd.OpenReport. If necessary, you can use custom VBA functions in the query/where-condition, and you can assemble the SQL in code.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Print report from multi-select form (2000 SP3)

    Have you tried the grouping functions in Access reports? It lets you print multi-section reports from a single design, and should simplify your task significantly. Each section has it's own header, so you could print the GroupName in the Header of the report. As to the SQL for you data source, that can be set in VBA as you suggest, but may involve some fairly complex Select statements where you need to group by a given parameter such as age. In any event that sort of thing can be build into the SQL string as an expression. Without significantly more details, it's difficult to judge whether your basic approach is on target.
    Wendell

  4. #4
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print report from multi-select form (2000 SP3)

    The criteria *are* in the query and from there I *am* assembling the where condition in code. The problem is arising from the fact that there are a number of completely different and mutually exclusive conditions that constitute the definition of a group. I have an underlying union query which joins the set of members who have been assigned to groups with the set of members for whom I have birthdates. This query feeds the report. There is also a table tblCommittees which stores the definition of a group with, in addition to the group name, a series of fields fldEquals, fldBetween, fldAnd, fldGreaterThan, fldLessThan and an option group assignment which forces the math fields to contain only logical values: if fldEquals is not null, all the other math fields must be, etc.

    The option group value feeds the select case statement in the VBA which generates the where clause of the SQL.

    All of this works fine. Where I am hung up is just in being able to display the committee name on the report (since age-related committees are generated at run time, not in the query). Being any given age potentially allows you to belong to many different age-realted groups, which means the underlying query would become rather bloated if I filled all that data in (there are 1400 individuals in the Individuals table).

    The code which prints the report is very simple and I can post it if you want.

    Peter

  5. #5
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print report from multi-select form (2000 SP3)

    I am currently using the grouping functions in the Access reports. That specific problem has to do with the fact that groups need to be on separate pages, but ones involving a range of ages need to be grouped together. Currently the range of ages gets each age printed on a separate page because of a force new page command. The question becomes can I turn force new page off and on at will when I am printing multiple groups with multiple definitions?

    I am thinking I probably need to post a copy of the database so you can see how it is set up. I will try to strip it down to under 100k and post it.

    Peter

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

    Re: Print report from multi-select form (2000 SP3)

    About the new page problem: you can change the ForceNewPage in code, but you'll have to experiment to get the correct behavior. You can also put a page break on the report at the appropriate location (at the bottom of a section for instance). You can activate/deactivate it by making it visible/invisible in code.

    About displaying the committee names, you'll have to provide more information.

  7. #7
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print report from multi-select form (2000 SP3)

    Here is a stripped down copy of the database. You may need to add some more people back in to get a full range of possible reports.

    It will give you a much better understanding of my original questions.
    Attached Files Attached Files

  8. #8
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Attachment crashes Access XP

    FYI - I downloaded the attachment, everytime I opened the db Access almost immediately crashed with a fatal error and the XP automated error reporting mechanism sent a bug report off to never-never land. I'm running Access 2002 on Windows XP, which can be more sensitive (unstable) than A2K if there is anything flaky in a db. In fact, it was not possible to compact & repair db before error occurred; likewise, when tried to import objects from the db into a new blank db the fatal error immediately occurred. So there there may be something flaky in the db you attached, tho' whatever it is may not be evident in A2K. I would suspect the form, which I was once able to open briefly in design mode before app crashed.

  9. #9
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Print report from multi-select form (2000 SP3)

    As noted am unable to open attachment so cannot provide specifics. Some general info: Assume the report is generated from a form (if not how does user enter options?) The easist way to display variable info on report (such as Committee Name) is to store this data somewhere on form & reference open form on report. Example: Unbound textbox in report footer, ControlSource is set to:

    =[Forms]![Print_Rpt_frm]![Filter_Text1]

    where Print_Rpt_frm is open form and Filter_Text1is textbox on form. The textbox on report will display same text as form's textbox (which may or may not be hidden from user).

    Re: Force New Page property - this can be set in VBA when report opens. Here is an (generalized) example from same report, where the Group/Sort levels are set dynamically based on some user options when report opens (this MUST be done on report's Open event):

    Private Sub Report_Open(Cancel As Integer)

    Dim strFrm As String
    Dim intSortOpt As Integer

    strFrm = "PRINT_RPT_frm"
    intSortOpt = Forms(strFrm)!SortOpt_txt
    SortReport intSortOpt

    End Sub

    Private Function SortReport(ByRef SortOpt As Integer)

    Dim strCaption As String

    With Me

    Select Case SortOpt
    Case 1
    With .GroupLevel(0)
    .ControlSource = "FIELD1"
    .SortOrder = False 'Asc
    End With
    With .GroupLevel(1)
    .ControlSource = " FIELD2"
    .SortOrder = False 'Asc
    End With
    With .GroupLevel(2)
    .ControlSource = " FIELD3"
    .SortOrder = False 'Asc
    End With

    .GroupHeader0.ForceNewPage = 1 ' Before Section
    strCaption = "FIELD1 REPORT"

    Case 2
    With .GroupLevel(0)
    .ControlSource = " FIELD3"
    .SortOrder = True 'Desc
    End With
    With .GroupLevel(1)
    .ControlSource = " FIELD2"
    .SortOrder = True
    End With
    With .GroupLevel(2)
    .ControlSource = "FIELD1"
    .SortOrder = False 'Asc
    End With

    .GroupHeader0.ForceNewPage = 0 ' None
    strCaption = "FIELD3 REPORT"

    End Select

    .Caption = strCaption
    .PAGE_HDR_LBL.Caption = strCaption
    .PAGE_FOOTER_LBL.Caption = strCaption

    End With

    End Function

    Note that when report grouped by Field1, each group starts on a new page (ForceNewPage = 1, Before Section), but when grouped by Field3 (2nd option) there are no page breaks between groups (ForceNewPage = 0, None). The ForceNewPage property is a property of a Section object, in this example GroupHeader0 (first group level defined for report). Note SortOrder property (GroupLevel object) is non-intuitively False for Ascending and True for Descending, if applicable in your case. Another note: If the number of Group/Sort levels can vary based on user options, I'd recommend ensuring all necessary GroupLevels are predefined in report design mode. It is a lot simpler to manipulate properties as shown above when report opens than to fool with the CreateGroupLevel method, which is available only in report Design view (you'd use this with a report wizard). If necessary create "dummy" GroupLevels in design mode by simply sorting on same field twice.

    You will probably have to do some experimenting to get the ForceNewPage & other settings to work correctly with your specific report. I don't know how your loop works so this may or may not be adapted for what you're doing.

    HTH

  10. #10
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Attachment crashes Access XP

    In further response, I was able to set a reference to the multiSelect.mdb file in VB editor from another db and thus read the code w/o app crashing. It looks like you're using a multi-select listbox with the MulitPik class from the ADH (I also use many of the class modules from ADH in my own projects.) As for 1st question, I'd add textbox to frmMultiPik called something like "CommitteeName_txt" and then in the cmdChosen_Click sub, when looping thru the selected items, add a line like this in loop:

    Me.CommitteeName_txt = recCmttee("CommitteeName")

    Then as noted in previous reply, add textbox to report that refers to the form's textbox by setting ControlSource property to:

    =[Forms]![frmMultiPik ]![CommitteeName_txt]

    The textbox in both form & report will be updated each loop thru selected items. Though I have a question: In the cmdChosen_Click sub, if the idea is to loop thru selected items and open report for each item, shouldn't the last 2 lines of sub:

    strWhere = Mid(strWhere, 5)
    DoCmd.OpenReport "Print Committee List", acViewPreview, , strWhere

    be located INSIDE loop, not outside?? If inside loop, note only one report can be previewed at a time.

    I can view the report's code module but I don't see any report Open event. I can't view report itself. My previous reply has an example of how you can use the ForceNewPage property with report's open event. If necessary, add reference to frmMultiPik in this event and have some conditional code (as shown in my example) so event "knows" what to do. In the form's code, the option for each item appears to be based on "AgeCriteriaOptionGroup" - is that a control on form? - if not, assign this value to a textbox on form, then in report's Open event have a Select Case statement similar to the one on the form and conditionally set GroupLevel and group section settings like ForceNewPage, etc, based on this value as obtained from open form.

    HTH

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Attachment crashes Access XP

    FYI Mark, I have both 2000 and 2002 on my machine. The database opens and behaves in 2000 and I can open the objects in design view but when I tried to get to the code behind the report, I got repeated messages about interrupting code running. If I open the database and use Alt+F11 to go to the code, I don't see anything that would cause that behavior, but there is definitely something going on. I don't see any code in the form to destroy the class instance (yes, I know it shouldn't be necessary) and I wonder if that could be what's going on. When I try 2002, I get the same crash you reported.
    Charlotte

  12. #12
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Attachment crashes Access XP

    I am attaching a newly created copy of the DB in a newly created zip file. I can't imagine where the odd problems are coming in 2000 as it works fine on my computer. I will have to download my original and see if it crashes on my machine. As to XP, I'm not a fan of most of it. ;-)

    You are correct, I am using the MultiPik class from ADH and have used the form wholesale as well. The only alteration to any of the code is the on click event.

    I will check out the text box info in your most recent post and should be able to implement that.

    As to your question about the DoCmd.OpenReport, I have posted the db as it stood before I realized I had the problem with Grouping. I played with putting the Do.Cmd inside the loop but it only did the first item, then closed down. I will look at your notes a little more closely this afternoon, then post back with questions.

    Many thanks so far to all of you.

    Peter
    Attached Files Attached Files

  13. #13
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Attachment crashes Access XP

    I was able to solve both problems. Thanks, thanks, thanks to Mark. Your posts were sufficient information. Here is the code for the form and for the open event of the report.

    Private Sub cmdChosen_Click()
    Dim aSelected() As Variant
    Dim varItem As Variant
    Dim strSlct As Variant
    Dim strWhere As Variant
    Dim intOpt As Integer
    Dim db As DAO.Database
    Dim recCmttee As DAO.Recordset
    Dim strLeft As String

    Set db = CurrentDb()
    aSelected = mmp.SelectedItems
    Set recCmttee = db.OpenRecordset("tblCommittees")
    strWhere = ""
    For Each varItem In aSelected
    'Open a recordset of the items in the select box
    strSlct = "SELECT * FROM tblCommittees WHERE CommitteeName = '" & varItem & _
    "'"
    Set recCmttee = db.OpenRecordset(strSlct)
    intOpt = Nz(recCmttee("AgeCriteriaOptionGroup"))
    Me.txtCommitteeName = recCmttee("CommitteeName")
    Me.txtOptionGrp = intOpt

    'Select Case statement to select right option group
    Select Case intOpt
    Case 1
    strWhere = "Criteria = '" & recCmttee("CommitteeName") & "'"
    Case 2
    strWhere = "Criteria = '" & recCmttee("SingleDelimiter") & "'"
    Case 3
    strWhere = "Criteria BETWEEN '" & recCmttee( _
    "BetweenDelimiter") & "' AND '" & recCmttee("AndDelimiter") & "'"
    Case 4
    strWhere = "Criteria > '" & recCmttee("GreaterThanDelimiter") & "'"
    Case 5
    strWhere = "Criteria = '" & recCmttee("LessThanDelimiter") & "'"
    Case Else
    MsgBox "Help!"
    Exit Sub

    End Select

    DoCmd.OpenReport "Print Committee List", acViewNormal, , strWhere
    Next varItem

    End Sub
    *********************
    Private Const adhcParamForm As String = "frmMultiPik"

    Private Sub Report_Open(Cancel As Integer)
    Dim intSortOpt As Integer

    ' Perhaps you opened the form first?
    If Not IsOpen(adhcParamForm) Then
    DoCmd.OpenForm _
    FormName:=adhcParamForm, _
    windowmode:=acDialog
    End If
    ' Set the Cancel flag if the form isn't still open.
    ' That would mean the user pressed the Cancel button.
    Cancel = Not IsOpen(adhcParamForm)
    intSortOpt = Forms(adhcParamForm)!txtOptionGrp
    SortReport intSortOpt

    End Sub

    Private Function SortReport(ByRef SortOpt As Integer)
    With Me
    Select Case SortOpt
    Case 1 To 2
    .GroupHeader0.ForceNewPage = 1
    Case Else
    .GroupHeader0.ForceNewPage = 0
    End Select
    End With
    End Function

    *******************
    Now that I have the mechanics working, I have to do the dull bits which is putting all of the other little fields into reports and queries. At least it all works now <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>
    Peter

  14. #14
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Attachment crashes Access XP

    OK, glad you got it to work. One final note: A while later, I tried opening Access & instead of trying to open db directly, I converted file to Access 2002 format database, then was able to open database (and form & report) w/o any odd behavior or crashes. I've had problems in past with some of my own projects that worked fine in A2K, but when opened in Access 2002 (AXP) (in A2K file format) would suddenly crash for no apparent reason when you performed certain actions. It seems as if AXP has some "issues" with database files in A2K format.

Posting Permissions

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