Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Open Report based on Dlookup (2002)

    Hi,

    I have a group of 5 reports that will open from a cmd button. The determining factor as to whether a report opens or not needs to based on whether the report has been checking as a "yes" from tblPerformanceImprovementChoices. I'm trying to use dlookup to determine if the report should up but keep getting an error that states "you canceled the previous action". The code I'm using to open the report is:

    If DLookup("NTUActive", "tblPerformanceImprovementChoices") = True Then
    DoCmd.OpenReport "rptNTUByMonth", acViewPreview

    What am I missing/doing wrong?

    Thanks,
    Leesha

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

    Re: Open Report based on Dlookup (2002)

    The DLookup will lookup the value of NTUActive in the first record it encounters. Shouldn't there be a Criteria argument to specify which record you want? Look up DLookup in the online help for more info.

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Open Report based on Dlookup (2002)

    Hi Hans,

    I didn't realize it looked at the first record it encountered. Each report has a separate entry in the table as yes/no. IE NTUMonth, NTUQuarter, ARMonth, ARQuarter. Each report has an autonumber. There could be 11 different reports (I'm not done building the reports yet), therefore 11 different records.

    Leesha

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

    Re: Open Report based on Dlookup (2002)

    How can you identify the record for a specific report? Is there some kind of ReportID?

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Open Report based on Dlookup (2002)

    Hi Hans,

    Each record has an autonumber. So for example [NTUMonth] is autonumber 1 and is identified as "yes" in the yes/no portion. [NTUQuarter] is autonumber 2 and is set as "no".

    Leesha

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

    Re: Open Report based on Dlookup (2002)

    The next question is how to work this into the Criteria argument of DLookup. We need something like

    If DLookup("NTUActive", "tblPerformanceImprovementChoices", "[SomeField] = " & SomeValue) = True Then
    DoCmd.OpenReport "rptNTUByMonth", acViewPreview

    SomeField is the name of the AutoNumber field in tblPerformanceImprovementChoices. We need a way to determine SomeValue. For this example, it should be 1 from your description. Where does it come from on the form?

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Open Report based on Dlookup (2002)

    Right now the form simply has a command button on it and a textbox to filter the year that will be in the report. I was using the code to and the value of the "yes/no" in the record to determine whether the report should open or not. I'll set it up anyway you suggest.

    While on this thread, please let me pick your brain in how you would suggest I set up a similar situation. After I get this piece to work, the next step will eventually be to open a report that contains subreports. The user is able to pick the performance improvement studies they wish to run. These choices may differ from office to office. The final report should open with only the subreports that correspond to the "yes" in tblPerformanceImprovementChoices. If the user didn't select a report it will not open. I was planning on using code similar to the code you helped me with last week regarding having a field in a subreport visible based on the option chosen from a form. In this instance my goal was that the user would click a command to open the report from a form. The form will be indentical no matter what office is using it. The report would then be customized so to speak, based on the "yes/no" values in tblPerformanceImprovementChoices". How would you set it up if it were you?

    Thanks,
    Leesha

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

    Re: Open Report based on Dlookup (2002)

    But where does the BTUMonth or BTUQuarter or whatever choice come from? In other words, how does the user indicate this choice?

  9. #9
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Open Report based on Dlookup (2002)

    My original thought (which is now seeming to be a bad way to set it up) is that the user indicates their choice of reports in tblPerformanceImprovement
    Choices, by either selecting yes or no for each report choice. This is meant to be a one time function (unless they need to update their choices at a later time). The goal is that on a monthly or quarterly basis, they go to frmPIReports, enter the year for the report(s) and click the cmd button. The report(s) would open based on the yes no choices in tblPerformanceImprovementChoices. I thought I could do this by using dlookup. Dumb idea I guess.

    Leesha

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

    Re: Open Report based on Dlookup (2002)

    Users should never edit a table directly.

    If the number of reports is limited, you could put unbound check boxes on the form, one for each report. This will work for up to 8 reports or so, after that it becomes unwieldy. If you have more, you could use a multi-select list box. In both cases, the On Click code of the command button would test which items are selected, and open the corresponding reports.

  11. #11
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Open Report based on Dlookup (2002)

    Oh God Hans, I NEVER let the users edit the tables. I may struggle with code, but I'm a control freak with the tables. They'd be entering the yes/no via form to the table as a one time only shot and then could edit if need be.

    Re your idea of doing unbound check boxes to a form, thats an interesting idea. Two questions. First, would this work if the report being openned contained subforms that would or would not be visible based on whether the box was checked, and second, is there a way to have the form retain the check boxes or "remember" the checkboxes so the user doesn't have to check them off each time they run the report?

    Leesha

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

    Re: Open Report based on Dlookup (2002)

    If you want to remember the settings, you might as well use check boxes bound to Yes/No fields in a table. If you have multiple users, you would either have to create a set of records for each user, so that individual preferences can be saved, or if you have a split backend/frontend design where each user has his/her own copy of the frontend, you could store the table in the frontend.

    Concerning subreports, you can use check boxes or a multi-select list box for those too.

  13. #13
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Open Report based on Dlookup (2002)

    OK, so I'll bind the form to tblPerformanceImprovementChoices and place the report names with their yes/no boxes on the form. As for the code behind the command button, I'm assuming I'd use an if/than statement ................if xbox = true then....................If this is correct, how do I write the code so that it multiselects reports or subreports so that more than one opens at a time, vs just the first report that is found in the if/than statement?

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

    Re: Open Report based on Dlookup (2002)

    Write a separate If ... End If block for each check box corresponding to a "main" report.

    If chkThis = True Then
    DoCmd.OpenReport "rptThis", acViewPreview
    End If

    If chkThat = True Then
    DoCmd.OpenReport "rptThat", acViewPreview
    End If

    etc. (substitute the appropriate names.)

    You'll have to write code in the On Open event of each report to hide unwanted subreports. Since this code runs in the report, you will have to refer to the check boxes as Forms!frmSomething!chkSubreport1 etc. (with the appropriate names substituted, of course.)

  15. #15
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Open Report based on Dlookup (2002)

    Thanks Hans! Can't wait to give it a shot.


    Leesha

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
  •