Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Optional subreport (XP)

    I

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Optional subreport (XP)

    Does your database have a record of whether an employee did these transition courses ?

    If a person did complete these courses, would you always want them included in the report?

    It seems to me that it would be better to let the database decide whether to include them, rather than rely on the user, but this obviously won't work if the db doesn't have records about who did them.
    Regards
    John



  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Optional subreport (XP)

    Thank you for the very prompt reply. That's a very good question. No

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Optional subreport (XP)

    OK

    I think there are two questions here. How does the user inidcate whehter they want the optional bit or not, and then how do you code it so you give them what you want.

    For the first:
    * My preference would be to place an option group with two radio buttons next to the command button. This avoids the extra step of having to respond to an intermedate form.
    * The easiest way to code it would be to have two different reports, one with the subreport, one without. The only problem with this is that you would need to remember to maintain both if you made any subsequent modifications.

    Dim stDocName As String
    if me!optGroup = 1 then
    stDocName = "rptTrtainingHistory"
    else
    stDocName = "rptTrtainingHistorywithtransition"
    end if
    Dim stLinkCriteria As String
    stLinkCriteria = whatever you have now
    DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

    The alternative with one report, would be to use the OnOpen event of the report to check which option was selected, then set the visible property of the subreport appropriately.

    You might also need to set the size of the subreport in code. I am not sure this will work, but you could make the subreport very small in design view and set its CanGrow property to yes, so that it will expand for its contents if it is visible, but not when not visible?
    Regards
    John



  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Optional subreport (XP)

    Y'know, the idea of two separate reports is interesting! I hadn't thought of that approach, and it would be a bit easier than the "alternative," which is the approach I had in mind.

    I've set up the report with a page break before the subreport, so the subreport occupies (and pretty much fills) a page all by itself. The first two pages are pretty much filled with the main transcript; the subreport is page 3; and page 4 is simply a pro forma Privacy Act statement.

    I'll try the two-report approach first. Thanks a heap for your help! <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Optional subreport (XP)

    Hello again! I've made progress with this little project, but this afternoon I hit a wall. This is my first attempt to create an option group, and I think I need a little tutoring. By the way, I followed your advice and built two transcripts (reports).

    I've put a command button on the form we use to locate the record we want to work with. This button opens a little form that displays the student's name and PID and asks the user to designate the transcript we want: (1) rptStudentHistory_Basic or (2) rptStudentHistory_Transition. I used the wizard to create this option group. I was not sure what you meant by placing the option group "next to the command button." On my form, the option group sits in the Detail section, and the command button to print sits in the form footer.

    Here's the code behind the command button (I used your sample code and, of course, modified it with my own object names):

    <font face="Georgia">Private Sub PrintReport_Click()
    On Error GoTo Err_PrintReport_Click
    Dim stDocName As String
    If Me!optGroup = 1 Then
    stDocName = "rptStudentHistory_Basic"
    Else
    stDocName = "rptStudentHistory_Transition"
    End If

    DoCmd.OpenReport stDocName, acNormal

    Exit_cmdOKPrintIt_Click:
    Exit Sub

    Err_cmdOKPrintIt_Click:
    MsgBox Err.Description
    Resume Exit_cmdOKPrintIt_Click

    End Sub
    </font face=georgia>

    The "printing" form opens as it should with the correct student information. But when I click on the command button, I get an error: "MS-Access can't find the field 'optGroup' referred to in your expression." Now, when the wizard finished creating the option group, I found the object has a lable "Frame 15."

    One more thing: Your sample code has three lines I don't know what to do with. Specifically, I don't know what "link criteria" are, but I suspect this has something to do with the problem...?

    <font face="Georgia">Dim stLinkCriteria As String
    stLinkCriteria = whatever you have now
    DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
    </font face=georgia>

    I know I've overlooked something

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

    Re: Optional subreport (XP)

    As it's night in Australia now, I'll try to answer.

    1. You should replace optGroup by the name you gave your option group. If it has a generic name, such as Frame37, please give it a meaningful name, then use this name in the code. You should not use the name of one of the option buttons here, but the name of the option group, i.e. the frame surrounding the option buttons.

    2. When you open a report (or form) in code, you can pass a where-condition to filter the records that will be displayed. For instance, if you want to display only a particular student, you would pass a where-condition that selects a specific PID. That is the stLinkCriteria in John's code. If you don't filter the report, you can omit it.

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Optional subreport (XP)

    Thanks, Hans! I see what you and John are saying now. Between the two of you, I'll probably get this form working! That'll have to wait till next Tuesday, though, since our Memorial Day three-day weekend is upon us.

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Optional subreport (XP)

    Hello, Hans (and John and anyone else who might look at this):

    I'm no longer getting the error message I wrote about earlier, but I'm not getting anything to print either. The problem, I'm sure, lies in my code, which follows:

    <font face="Georgia">Private Sub cmdOKPrintIt_Click()
    On Error GoTo Err_cmdOKPrintIt_Click

    Dim stDocName As String
    If Me!optSelectPrint = 1 Then
    stDocName = "rptStudentHistory_Basic"
    Else
    stDocName = "rptStudentHistory_Transition"
    End If

    DoCmd.OpenReport stDocName, View:=acViewNormal
    <font color=red>WhereCondition:="[PID]=" & Me.PID</font color=red> <font color=blue>The VBE displays this line in red, but I can't see why. Please see below.</font color=blue>

    Exit_cmdOKPrintIt_Click:
    Exit Sub

    Err_cmdOKPrintIt_Click:
    MsgBox Err.Description
    Resume Exit_cmdOKPrintIt_Click

    End Sub
    </font face=georgia>

    The line in red is a code snippet I borrowed from another form in this same database. I modified it, of course, but it's basically the same line that works perfectly elsewhere. For example, I have a form where the user searches for a specific student via a record-selector combo box. The form displays basic information (name, PID). Then the user can click a command button to open another form with the full record on that student. This second form has a button that opens the form for printing the transcript (the form in question). Through each of these steps, the form opens with the current record on display, and this happens because of this line of code (my WhereCondition).

    So what's wrong here? Thanks a million for your help!

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

    Re: Optional subreport (XP)

    WhereCondition is an argument to OpenReport. It must either be on the same line as OpenReport:

    <code>DoCmd.OpenReport stDocName, View:=acViewNormal, WhereCondition:="[PID]=" & Me.PID</code>

    or you must use a space and an underscore after the first line to indicate that the instruction will be continued:

    <code>DoCmd.OpenReport stDocName, View:=acViewNormal, _</code>
    <code> WhereCondition:="[PID]=" & Me.PID</code>

    Note that there is a comma after acViewNormal in both cases, to separate the arguments.

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Optional subreport (XP)

    <img src=/S/doh.gif border=0 alt=doh width=15 height=15> <img src=/S/blush.gif border=0 alt=blush width=15 height=15> <img src=/S/hiding.gif border=0 alt=hiding width=70 height=24>

    Thanks, Hans!

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Optional subreport (XP)

    Oops! One more thing, Hans:

    The button now works, but with one little problem. After I click the OK button, I get a parameter box asking me to enter the student's PID. Of course, the code is supposed to have sorted that out so the user shouldn't have to answer that question. What am I missing?

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

    Re: Optional subreport (XP)

    The Me.PID in the code should refer to the control on the form that holds the student's PID. Since you get prompted for PID, the name of that control is probably different from PID. You should use that name in the code, for example Me.cboPID (substitute the correct name here)

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Optional subreport (XP)

    The Properties sheet for the form says the name of the control that holds the PID is simply "PID". It's a textbox.

    So...Me.PID ought to work, right?

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

    Re: Optional subreport (XP)

    In that case, is PID part of the record source of the report?

Page 1 of 2 12 LastLast

Posting Permissions

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