Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Selected Subreport item, referenced in a Query (97)

    I have a subreport derived from a Query, (ALLOCATED) that has these headings,:- NICKNAME NAME
    So data in the two field shown would be:- NICK Nicholas Smith
    plus more records etc..

    on the 'onclick' event of this subreport i have a pointer to a report, The report is generated from a Query.
    One of the Criteria in the Query is NICKNAME.

    What i need to do, is point the Query to the field that has been selected (The user will click the NAME field), i Do not want to show the Nickname field, i have just included it here, the Query HAS to run from the Nickname field.

    So i image, i'm looking for something along the lines of Criteria in Allocated Query = Forms![Myform]![Allocated_Subreport]!Recordsource.NAME

    Or something like this. I've tried a few different permutations, but can't get it to work...

    Help! <img src=/S/beep.gif border=0 alt=beep width=15 height=15>

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Selected Subreport item, referenced in a Query (97)

    I'm not sure I understand what you are doing, but I do know that you reference a subform by the name of the subform control that is on the mainform (which may or may not be the same as the subform's name in your database). So, to reference a specific control in the subform, you would have something like this (substituting the actual names of the mainform, the subform control, and the control on the subform you want:
    Forms!mainformname!subformControlName.Form!yourCon trolName
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selected Subreport item, referenced in a Query (97)

    yes mark, i understand the general way its done, but i need to point the query to the specific record that has been selected.

    That is, whatever long name is selected on the record, i want to send to the query, the short name associated with that record.

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

    Re: Selected Subreport item, referenced in a Query (97)

    If you are trying to reference NAME from the form [Allocated_Subreport] then try the following:
    = Forms![Myform]![Allocated_Subreport].Form!NAME
    But is this what you are asking?
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: Selected Subreport item, referenced in a Query (97)

    You can't send anything to a query that is already open. You can pass criteria to a query in code, or you can reference a control on an open object like a form in the query and use that as a parameter. I think you're going to have to explain exactly what you mean by "pointing" the query at a selected record. What are you trying to do with this query anyhow?
    Charlotte

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selected Subreport item, referenced in a Query (97)

    the easiest way to get the syntax correct is to use the Builder. Ensure that your form is open, then open the query that your report is based on in design view. Move the cursor to the Criteria row of the appropriate field, and right-click the mouse. Navigate down through the forms to the specific control that you want to use as the criteria. When the report based on the query opens, it will use the value of the field in the currently selected record as its criteria.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  7. #7
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selected Subreport item, referenced in a Query (97)

    I will try your suggestion pat, and the builder option, thanks..

    When a user selects a particular person from the subreport, (by clicking the full name on the subreport) a new report will open, this report needs as a criteria, the short version , ie; the 'NAME' field of the person selected.

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Selected Subreport item, referenced in a Query (97)

    Referencing the form/subform from the query will automatically reference the current record. I guess I'm not following what you are trying to do.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Selected Subreport item, referenced in a Query (97)

    Steve,

    Could we get the terminology straight, please? You keep referring to reports, but it's pretty obvious you are talking about forms. While you may use something to report information from the database, the word "report" in Access has a very specific meaning. When you talk about clicking on something in a report, you can't be talking about an Access report object. It makes it horribly confusing to try to untangle exactly what you are talking about, even for those of us who have been doing this for years. Please be more specific in Access terms instead of using user terminology. You need an answer from a developer perspective, so you have to cast the question in those terms.
    Charlotte

  10. #10
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selected Subreport item, referenced in a Query (97)

    Its ok Charlotte, the other helpful posters figured it out, and answered my question..

    Thanks Guys. <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35> <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: Selected Subreport item, referenced in a Query (97)

    Hey Steve,
    Well, what was the solution? We all need to know !!
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  12. #12
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selected Subreport item, referenced in a Query (97)

    Hi, patt... Thanks for your tip.. you were correct of course...

    The bit that i Didn't understand, was that in a subform, if you reference it, then the user selects a particular record, when you refer to the subform with :-

    Form![Mymainform]![mysubform].[myfield]

    The field that is referenced is always the correct one thats selected. AND, the field doesn't have to be shown on the subform at all.

    I was doing things like : Form![Mymainform]![mysubform].Form![myfield] and other weird combinations. <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

    Ahhhhhh its been a great day today.. I actually managed my first SQL !!!!!!!!!!
    I merged two queries with a union Thing-ame-jig !!! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

    SELECT [memoid], [field2], [field3]
    FROM [myquery1]

    UNION ALL [memoidsimilar], [field4], [field99]
    FROM [myquery2]

    Pointed it to my form, and TADA! --- it worked ! <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>




    Thanks again for all the help. I'm like British Rail, I'm slow.. but i get there eventually... <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

Posting Permissions

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