Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Specific Pages in a Report (97/SR2)

    I have a database of all the equipment in our manufacturing plant. Each record for each piece of equipment, has quite a few details, one of which is it's physical location within the plant. The record for the equipment has a number for the location, which is then referenced, through a query, to another table, which has the location description.

    I have a report that lists all the equipment, sorted by physical location. Each location prints on a separate page and the overall report is over 30 pages long.

    What I would like to be able to do is allow the user to select one location to print a report. They could go to Print and select only the page(s) they want but that might be too complicated for some of these users. I would like to have a dialog box come up and prompt them to select the location. I know how to do a parameter query which would allow them to enter the information. The problem is that some of descriptions are long and they are never going to enter them exactly right.

    Is there a way to combine the parameter query and a list box so that they could choose from the list and get the report for only the location that they need? Thanks for any help.

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Specific Pages in a Report (97/SR2)

    Yes, you can and that is the way that makes the most sense. Creating the entire report, when you only want one location, is much less efficient. The way I do this is ...
    Create a dialog form which will collect the criteria for the report (in your case, the location). You can find the directions for creating this in the Access Help Index. Search on "criteria, reports". Then, "use a form to enter report criteria". On that dialog form, make a combo box which will dropdown and show all the locations. Place a button on that form which will open the report (attached to the OnClick Event).
    Now in your query change the criteria for location to something like: [Forms]![your form name]![your combo box name].
    When the user selects a location from the combo box and the button is clicked on the dialog form, the query will get the criteria from the combo box and the report wil print for only the selected location.

  3. #3
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Re: Specific Pages in a Report (97/SR2)

    Thank you for your help...but I still need more.
    I was able to design the dialog box, and add the criteria to the query. Unfortunately, when I select the item in the dropdown list and click OK, it just closes the form. As per the Access Help page, I created an OK button tied to a SetValue Macro, with properties of [visible] & No. I also tried creating a print preview button, both with a wizard, and manually, along with a macro for a print preview to the form. What happens is that when I select an item in the drop down and then click print preview, it asks for the criteria. Somehow it isn't passing the selected criteria to the query dialog box. (probably a step I missed). Any further help would be appreciated. Thanks.

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Specific Pages in a Report (97/SR2)

    I am a bit confused ...First you say "when I select the item in the dropdown list and click OK, it just closes the form", but later you say "when I select an item in the drop down and then click print preview, it asks for the criteria". Do both problems occur? (If the form is being closed, before the event fires, the query cannot see the textbox) And why do you have both buttons, isn't your goal to just print preview the report? Which button needs to work properly?

    What about the OnOpen and OnClose event of the report that is described in Help, did you include that? Also, check the query and make sure the criteria correctly refers to the textbox on the dialog form.

    If that doesn't lead you to a solution, attach a representative Db and I'll take a look at it.

  5. #5
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Re: Specific Pages in a Report (97/SR2)

    Sorry, didn't mean to cause confusion. When I read through the Help file, they make it sound like it should work if I created an OK button, so I did that. When it didn't work, I saw in your first post that I should create a button to open the report, which I did, but it didn't work either.
    I did include the macro described in Help, attached to the OnOpen and OnClose events. I believe that I have the correct criteria. When I run the query and manually enter one of the items, it does what it is supposed to do.
    Again, sorry for the confusion. I do appreciate your help.

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Specific Pages in a Report (97/SR2)

    No apology needed ...
    Lets' stick with the OK button, the query and the Help instructions. It sounds as if you believe you created those properly, but the OK button should not close the form. What is causing it to do that? Without some more information, I do not know how to help you further. Can you post a sample db with all the objects that cause this problem so that I can take a look?.

  7. #7
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Re: Specific Pages in a Report (97/SR2)

    I think it is the OK button on the form that is causing it not to work. I believe that I have it set up correctly, according to the Access Help file. The underlying macro has an action of SetValue, the Item is [visible] and the expression is No. This is all that is required, as per Help. When I click on OK, the report runs but the criteria does not get passed along. If I run the report and bypass the dialog box, the parameter query box opens up, I type in location, like "Yard", (I use that because it's short and hard to spell wrong) and everything works fine.
    I tried creating a stripped down version of the db but I can't get it under Woody's 100k Max. (I only have 5 records each in 2 tables, 1 query, 1 form, 1 report, 1 macro and 1 function, even tried compacting it) Maybe the information I have provided here will be enough.
    I appreciate your help. It's probably something real simple that I am missing. Thanks.

  8. #8
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Specific Pages in a Report (97/SR2)

    For lack of the ability to see yours, I made a small, fast (very fast before the weekend ... pardon it's appearance) sample db with the objects needed to perform the functionality you want. Open the form, select a location and the report prints for only the location selected on the form. Compare the attachment to yours and see if that helps.

    I'm all ready thinking of things I could have done more consistently on this, but the weekend has begun and I think you'll get the point.
    Attached Files Attached Files

  9. #9
    New Lounger
    Join Date
    May 2002
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Specific Pages in a Report (97/SR2)

    I assume you have a docmd.openreport on the onclick event for your OK button. Instead of messing around with the query, you can just add a where statement on the docmd.openreport: That way, the query will display all equipment for all locations, and your command button will limit the report to displaying only the selected location.

    docmd.openreport "rptEquipment",acviewpreview,,"LocationID="& me.cboLocationID

  10. #10
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Re: Specific Pages in a Report (97/SR2)

    Thanks for all of the help, I really appreciate it. I probably have a word misspelled or something like that. I tried to follow your example as closely as I could. One thing that I found a problem with is that there seems to be more than one version of IsLoaded. I copied the one from Northwind, yours is different, I don't know how much of a difference it makes. I zipped the db and attached it here, leaving only the items that are essential for this operation. If you could take a look at it and have any additional help to offer, that would be great. Thanks again for all of your efforts.
    Attached Files Attached Files

  11. #11
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Specific Pages in a Report (97/SR2)

    I believe you are all set ... it now works in the attachment.
    There were two issues. First, the query criteria was in the Physical Location field, rather than the Physical Location ID field, so Access couldn't find any records that met the criteria. Second, there was a slight difference in the spelling of the form name in the SetValue macro action.
    Attached Files Attached Files

  12. #12
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Re: Specific Pages in a Report (97/SR2)

    That worked very well, took me a few days to figure out how to do it. Thank you for your help.
    I also want to be able to print ALL records from this same dialog box. I don't know if this is the best way, but I just added a command button "ALL" and tied it to the report. Not having the report restricted by the query, which I had before, this works very well, it even closes the dialog box when it is completed. Thanks again for the help.

  13. #13
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Re: Specific Pages in a Report (97/SR2)

    John,

    I used your method to pull up my reports and it worked really well. Now I want to use it to pull up a report showing only specific dates within a range. This is the code I used in the event procedure (is that the right word, code?)

    Dim strDocName As String
    Dim strFilter As String

    strDocName = "rptMonthlyTotals"
    strFilter = "[Date]=Between Forms![fdlgMonthly]![BeginningDate]" And "Forms![fdlgMonthly]![EndingDate]"

    DoCmd.OpenReport strDocName, acViewPreview, , strFilter

    Date is the name of the text box on the report and fdlgMonthly is my dialog box.

    I'm sure it is just a typing error or something that I'm not seeing. Any help would be appreciated. Thanks.

    When I run it, I get an error that says "Syntax Error (missing operator) in query expression

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

    Re: Specific Pages in a Report (97/SR2)

    Take out the equal sign. Between and And are operators themselves. Also, concatenate the values from the form fields so that the actual date values are passed into the filter string. So your filter string should be

    "[Date] Between #" & Forms![fdlgMonthly]![BeginningDate] & "# And #" & Forms![fdlgMonthly]![EndingDate] & "#"
    Charlotte

  15. #15
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Re: Specific Pages in a Report (97/SR2)

    Thanks Charlotte.

    I copied the code into the expression, (cut and paste, so I couldn't make any typos) and when I run it, I just get a blank report. Is there another step I may be missing? Thanks.

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
  •