Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    multiple reports - one subreport (Access97)

    I have 3 reports that share a subreport. I am attempting to use the tag property of each report and the detail_format event of the subreport to set the recordsource and apply filters, etc. Since these are multipage reports (one record per page) i'm getting Error # 2465 - "Can't find the field Tag referred to in your expression" The report opens up and displays the first record but when I move to the 2nd record, the error is generated. I have only written the sql statements so far and don't know how to code for this kind of thing... I think i need to hold the tag property value in a variable...and i think the detail_format event is the wrong place for this process to happen. Some background... the subreport contains signatures (links to word documents) - If anyone has an idea of what i'm trying to accomplish, please post back. here is what i have so far:

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    Select Case Parent!Tag.Value

    Case 1, "Lobbyist"
    Me.RecordSource = "SELECT DISTINCTROW jctblLegLobby.jctblLobbyLeg, jctblLegLobby.LegID, " & _
    " jctblLegLobby.LobbyID, tblLobbyist.LobbySign, tblLobbyist.LobbyTitle, tblLobbyist.LobbyName, " & _
    " tblLobbyist.LobbyIni FROM (tblLobbyist INNER JOIN jctblLegLobby ON tblLobbyist.LobbyID = " & _
    " jctblLegLobby.LobbyID) INNER JOIN qryLegwithonelobbyist ON jctblLegLobby.LegID = " & _
    " qryLegwithonelobbyist.LegID"
    Me.FilterOn = True
    Me.Filter = [tblLobbyist.LobbyIni] <> [Text8]

    Case 2, "Director"
    Me.RecordSource = "SELECT DISTINCTROW jctblLegLobby.jctblLobbyLeg, jctblLegLobby.LegID, " & _
    " jctblLegLobby.LobbyID, tblLobbyist.LobbySign, tblLobbyist.LobbyTitle, tblLobbyist.LobbyName, " & _
    " tblLobbyist.LobbyIni FROM (tblLobbyist INNER JOIN jctblLegLobby ON tblLobbyist.LobbyID = " & _
    " jctblLegLobby.LobbyID) INNER JOIN qryLegwithmorethanonelobbyist ON jctblLegLobby.LegID = " & _
    " qryLegwithmorethanonelobbyist.LegID"

    Case 3, "SignOwn"
    Me.RecordSource = "SELECT DISTINCTROW jctblLegLobby.jctblLobbyLeg, jctblLegLobby.LegID, " & _
    " jctblLegLobby.LobbyID, tblLobbyist.LobbySign, tblLobbyist.LobbyTitle, tblLobbyist.LobbyName, " & _
    " tblLobbyist.LobbyIni FROM (tblLobbyist INNER JOIN jctblLegLobby ON tblLobbyist.LobbyID = " & _
    " jctblLegLobby.LobbyID) INNER JOIN qryLegwithonelobbyist ON jctblLegLobby.LegID = " & _
    " qryLegwithonelobbyist.LegID"
    Me.FilterOn = True
    Me.Filter = [tblLobbyist.LobbyIni] <> [Text8]
    Me.LobbySign.Visible = False

    Case Else
    MsgBox "Please check the report format for this report", , "Transmittal Letter Report"

    End Select
    End Sub

    I am not held to this particular procedure... this is one solution to the problem i have. i basically am creating letters to various people and different people need to sign the letter based on a couple business rules. i have narrowed it down to 4 types of letters, three of which use this same subform. thanks for any ideas and help. Jenn.

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

    Re: multiple reports - one subreport (Access97)

    Jenn, I hope for you that others will contradict me; I don't think you can do it this way. The error you're getting now is caused by the !, for Tag is a property of a report, not a control. But this error masks a more serious problem - you can't change the record source of the subreport in the OnOpen of the main report, because the subreport is still closed, so it's too early, and you can't do it in the OnOpen of the subreport because formatting the report has already started by then, so it's too late.

    What you could do is set the record source of the subreport to a stored query (in the interface, not in code), and change the SQL of this query before opening the main report (use DAO: define a querydef, and set its SQL property).

  3. #3
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multiple reports - one subreport (Access97)

    Oh Hans, I am truly saddened by your news <img src=/S/sad.gif border=0 alt=sad width=15 height=15> yet I can see the hope in your message <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    the subreport is based on a stored query which was one of the Cases I presented in the original post and is as follows:

    qryLobbyistTransmittalSubreport
    SELECT DISTINCTROW jctblLegLobby.jctblLobbyLeg, jctblLegLobby.LegID, jctblLegLobby.LobbyID, tblLobbyist.LobbySign, tblLobbyist.LobbyTitle, tblLobbyist.LobbyName, tblLobbyist.LobbyIni
    FROM tblLobbyist INNER JOIN jctblLegLobby ON tblLobbyist.LobbyID = jctblLegLobby.LobbyID;

    If I understand you correctly Instead of changing the recordsource of the subreport at runtime, I should change the sql of the underlying query that is the current recordsource of the subreport. I have not ever done query definitions so I will search for some posts in the forum on that topic.

    In terms of procedure assembly... a query needs to be defined, then the report opened, printed, closed, choose the next query definition, open the proper report, print, close, etc til all 3 reports have been processed. In using this method, how can I open a specific report on the report tab and have the proper sql inserted?

    Thanks. Jenn.

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

    Re: multiple reports - one subreport (Access97)

    Hi Jenn,
    Unfortunately Hans' message is sad but true. May I suggest an alternative that would create a table with the full signature of each lobbyist, and make that the data source of your subreport. Then modify your query for the main report to include a calculated value that is bound to a hidden control on the main report. Link the child property of the subreport to the lobbyist ID, and the main property of the subreport to the calculated value in your query, and the subreport should automatically adjust the signature. Hope this makes sense - if not post back.
    Wendell

  5. #5
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multiple reports - one subreport (Access97)

    Thanks for agreeing with Hans, Wendell! <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>

    Anyway, I need to understand your solution more... I can't quite visualize it yet. I'll give alittle more background...

    tblLobbyist has all the lobby info, including signature field. jctblLegLobby is a junction table, autonum, with LegID and LobbyID... (many-many). the subreport's recordsource is based upon a query that joins these two tables. the LegID is used as the linking field back to the main report... the main report is based on a query that joins alot of info about the legislator.

    The problem to overcome is this:
    there is an employee (staffer) who must always sign the letter. his sign, name, title and initials are on the main report via a dlookup to an unrelated table tblCurrent. this person is also listed in tblobbyist and so one sql needs to filter him out if he shows up in the recordset. another situation is that someone else in tblLobbyist is higher in the chain of command (director) so when he shows up, the signatures need to be shifted around to accommodate the hierarchy. AND for each record where the director shows up, there will be a second entry in jctblLegLobby. mind you, this LobbyID will not be the guy from the first circumstance and should it ever be, well, someone else can figure that out. So the best I could come up with is the three reports with the shared subreport... fyi, the subreport is a two column report so the signs are side by side. what i've done in report1 is dlookup the staffer's name and turn the subreport invisible when he shows up in the recordset. report2 is a dlookup of the directors and staffers name and filtered them out of the recordset with subreport containing any additional lobbyist. then a third report that is setup like report1 and filters for the staffers own records and removes the signature cause he likes to sign certain letters personally. So having said that... should you offer an entirely different approach... I'll scream first... then be happy to hear you out... thanks to you both.

    oh, and I predict that very soon, someone will want to use a different signature based upon who the letter is being sent to... (for instance... first name only, versus full name... there are so many exceptions... i've forgotten the rules...

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

    Re: multiple reports - one subreport (Access97)

    OK - I spend some time looking at an old version of your database and I am <img src=/S/confused.gif border=0 alt=confused width=15 height=20> <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15>
    Let me try to summarize what I think I understand:<UL><LI>You have three different versions of the same letter, each with a different signature arrangement?
    <LI>You want to use the same subreport on all three letters.
    <LI>If two people sign the letter, the signatures should be side by side.
    <LI>It is possible that three people need to sign the letter.
    <LI>Some letters need to be signed manually.
    <LI>some people want to make the signature dependent on how well they know the addressee?[/list]Based on what I now think I know and don't know, would two (or three) subreports with different data sources (i.e. staffer, supervisor, director) for a given mailing work? If a person's signature isn't required, set the subform so it shrinks to zero height, or alternatively just let it be blank.

    BTW, have you looked at doing these with a mail merge in Word instead of an Access report? It seems to me you might want to use some of the more sophisticated formatting capabilities of Word. Just food for thought.
    Wendell

  7. #7
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multiple reports - one subreport (Access97)

    thanks Wendell, that old version is out... but yes you are very right in your interpretation of what I need...

    1 2
    3 4

    there are 4 signature spaces on each letter... two rows of two... just like in a real letter. the first space is occupied by someone who doesn't factor into this... his info comes from dlookup in tblcurrent.... the 2nd position is usually occupied by the staffer... cause he signs all letters and his info can stay in position 2 in all cases except when the subreport has the director listed as a lobbyist. positions 3 and 4 are used by the subreport. i've tried another variation for when the director is signing... that's turning the subreport to be vertical first and use positions 2 and 4 but the best results came with the former. the report is simply a transmittal letter... concatenating several fields into paragraph format and turns off and on some fields based on values. anyway, i'll get back to this tomorrow... i have an Access headache right now...

    if you think it's warranted, i can attach a scaled down version of the new db tomorrow... thanks.

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

    Re: multiple reports - one subreport (Access97)

    I don't blame you for having a headache! I think a cutdown version to post would be helpful - I'm sure there must be a reasonably straightforward solution if we can spot it. Just enough data to give us some test cases should be sufficient. But something I don't understand:
    <<positions 3 and 4 are used by the subreport. >>
    Are you are saying that the subreport will be totally blank unless the Supervisor and/or the Director need to sign it?
    Have a good night's rest!
    Wendell

  9. #9
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multiple reports - one subreport (Access97)

    HAHAHA! so much for resting Wendell!

    the subreport is never blank.

    there is at least one lobbyID for every LegID. so technically, position three is always filled with someone. i simply make the subreport invisible when the lobbyID expressed = the staffers lobby ID, cause he's a dlookup in position 2.

    sometimes there will be two lobbyID's for each LegID... and because the subreport is 2 columned, the two records fill position 3 and 4. the catch is that when there are two lobbyid's, one of them is always the director... and because of that... he needs to be in position 2 and the staffer moves to position 3... (this is the case where I tried to use a vertical subreport... (checked the down then across box in the page set up) and tried using positions 2 and 4 for the subreport and put the staffer in position 3. - inorder for this solution to work,, i would need to additionally change the page layout of the subform at runtime... and for all that work... i might as well make all individual reports, subreports and formats... i am resistant to the option because i prefer to change one letter rather than three. anyhow, i will post back with the db... it's the version i have at home so it's alittle different but close enough. it's in 2000 now but i'll convert to 97.

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: multiple reports - one subreport (Access97)

    <P ID="edit" class=small>(Edited by patt on 05-Mar-03 16:35. Ask for a diagram)</P>I notice that you have the following reports:
    RptTransmittalLetter
    RptTransmittalLetterAMPAC
    RptTransmittalLetterLobbyists

    What reports are you tring to setup?

    Couldn't you just change the DLookup's for the PACChair , PACDirector etc?

    Can you show a diagram of what you require? In this way you may only require one report.

    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  11. #11
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multiple reports - one subreport (Access97)

    Ok here's the db. let me know if you find other possibilities. thanks a bunch.
    Attached Files Attached Files

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

    Re: multiple reports - one subreport (Access97)

    Good morning -
    I took a fairly detailed look at your subreport design and found a couple of worrisome things. For one thing you have the subreport linked to the main report with LegID in both the child and master fields, but there is no LegID displayed on the subreport or on the main report, though it does exist in both datasource queries. But it seems to me that isn't the link you want. Shouldn't the link be to the ID of the person doing the signature. The strategy I would suggest is that you get all of the data you need in the main query, and then based on the conditions, either return a link for a specific signature or don't return a signature in link in some specific situations. In fact, I'm not sure the subreport is really of benefit here. You could do much the same with text boxes that collapse to nothing when there isn't any data. On the other hand, maybe lack of sleep has muddled my brain!
    Wendell

  13. #13
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multiple reports - one subreport (Access97)

    Ok, i think that the subreport idea isn't working...

    because there are various business rules that warrant how the transmittal report letter needs to be structured... how about using a transmittal id field in tbCampaign. this field could be updated to a specific number based on these rules and that field can be used in the query to choose the report to open for that legislator. the issue is how to write the code...

    rules are basically as follows:

    if the [camptype] is "other", OR if the [count of lobbyid] >1, update <trasmittalid> field to 1

    if the [camptype] = "federal", update the <transmittalid> field to 2

    if the [lobbyINI] = [CurrentINI] where [CurrentPos] = "PACStaff", update the <transmittalid> field to 3

    if the [count of lobby id] =1, update the <transmittalid> field to 4

    report "1" would put the director, staffer, (as dlookups) and the additional lobbyist (from the underlying query) in pos 2, 3 and 4 respectively

    report "2" would have the same layout yet have a different message in the text boxes and this camptype doesn't have lobbyists

    report "3" would have the staffer (dlookup) in position 2 and the signature field would be removed. (so he can sign his own)

    report "4" would have the staffer (dlookup) in position 2 and the additional lobbyist in position 3.

    reports 3 and 4 are very similar... perhaps its possible to combine them and simply disable the results of the lobbyist field when the value = the staffer and disable his signature... then turn everything back on when the lobbyist field is someone other than the staffer................this signature thing leads me to think there should be a yes/no "signown" field in tblLobbyist

    with this solution, there will be 3 or 4 reports each with different recordsources.

    anyone have concerns with a method like this one?

Posting Permissions

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