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

    Print blank report if parameter Null (A2K, SR1)

    My report draws its data from a query. In the query, one of the fields is Payroll_ID (from a table of employees). For the Payroll_ID field, on the Criteria: line in design view, I've entered: "[Enter student's Payroll ID]." Thus, when I run the report, Access asks me to enter the student's (employee's) payroll ID--then the report opens to display the student's identifying information and a complete list of all the courses available to him or her.

    If I do not enter a payroll ID--or if I enter an invalid payroll ID--the report opens with no student information and no course information whatsoever. It's a virtually empty report. From time to time, I need to print out a complete list of courses without any student ID. How can I do this? I got the idea to ask this question from a post on the Access MVP site ( http://www.mvps.org/access/queries/index.htm ). ("I'm using a control on a form as a parameter for my query.") The particulars in that post differ somewhat from mine: I'm concerned with a report based on a query, not a query based on a form, so I can't figure out how to write the criteria statement in my query, nor exactly where to put it. I hope someone can help me translate. TIA!

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Print blank report if parameter Null (A2K, SR1)

    Assuming the query is based on a parameter query, try inserting * for a Payroll ID. For example, if a payroll ID is 12345, insert *****. This may provide what you need or based on the query setup or may provide multiple courses listed many times.

    Actually, If you need to print out a complete list of courses, wouldn't you have that data in a separate table that you could print from? Create a query against this table to generate the courses available. I am assuming that your personnel table would link to a course table as a one to many relationship.


    HTH
    Regards,

    Gary
    (It's been a while!)

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

    Re: Print blank report if parameter Null (A2K, SR1)

    I think it would be best to create a separate report to list all courses without student information. You can copy part of the existing report into it.

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

    Re: Print blank report if parameter Null (A2K, SR1)

    Gary and Hans: Thank you both for your replies. I like your idea, Gary, to use a wildcard for the Payroll_ID. I just tried that, but I got an error message. Guess I need to re-write something to allow for a wildcard...?

    I see now that I failed to clearly explain the purpose of my report. The report is not just a list of our courses--it's a "form" (in the traditional, i.e., "non-Access" sense of the word). The report--we call it a training plan--serves as a paper record of a student's course completions in a learning lab. Once we create the training plan on paper, we fill in such things as the date the student completed a course. It's a day-to-day, pencil-on-paper kind of thing.

    Typically, we create a training plan by launching Access, opening the database, and selecting the correct report. When the report launches, it asks for the student's payroll ID number (4 digits). We enter the numbers, and the report opens on-screen with the student's identifying information in the respective fields. In the detail section, the report lists the courses available to the student.

    The problem is that if we do not enter a valid payroll ID, the detail section appears empty. The report header and footer, page header and footer--all those areas appear, although with blanks where the student's personal information would otherwise appear. From time to time, we like to print out these training plans with no student information on them. That is, we want to print a generic, blank training plan, and we'll fill in a new student's information with pen and ink. The system I've created does not allow us to print such a blank "form."

    You are both quite correct: I could easily print a list of courses with a query, but I'm after more than just a list. I hope I've explained the situation more clearly now--I apologize for the fog! I think the easiest fix will be to enable the use of **** in place of the payroll ID. Thanks again!

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

    Re: Print blank report if parameter Null (A2K, SR1)

    I fear that, basically, you are trying to make Access do something it wasn't built for.

    Some potential workarounds:

    (1) Create a blank form in Excel or Word that mimics the design in Access.
    (2) Make a copy of the report. In the copy, clear the Record Source of the report and the Control Source of the controls displaying student info.
    (3) Make a copy of the students table. Delete all records but one, and clear the fields of the remaining record. Next, make a copy of the report, and set the Record Source of this copy to the new table.
    (4) Create a dummy student with Payroll_ID = (for example) 9999. Leave the personal info blank. Set this record up so that all courses are available to this dummy student.
    To print an empty form, select Payroll_Id = 9999.
    To prevent this dummy record from messing up your student counts etc., you must include Payroll_ID <> 9999 in queries where appropriate.
    Not a very attractive option IMHO, but the only one that allows you to print a blank "form" in the existing setup.

    Regards,
    Hans

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

    Re: Print blank report if parameter Null (A2K, SR1)

    Gawley, Hans--I thought you could do just about anything in Access. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Thanks for the suggestions. I'll give 'em a try tomorrow.

  7. #7
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print blank report if parameter Null (A2K, SR1)

    Hans CAN <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    And usualy with only a tenth of the code!! <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23>

    Peter

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

    Re: Print blank report if parameter Null (A2K, SR1)

    You *can* do just about anything in Access. However, the result might not be worth all the effort required. <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>
    Charlotte

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

    Re: Print blank report if parameter Null (A2K, SR1)

    But if you remove the parameter, aren't you going to get one form for each of the students, with personal info filled in? It wouldn't be a blank report, would it? I hope for you that I am mistaken...

    Regards,
    Hans

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

    Re: Print blank report if parameter Null (A2K, SR1)

    Aw, shucks! It just occurred to me that there's one simple (albeit crude) way to do what I need to do. I can just temporarily remove the parameter from the query, print a bunch of training plans (reports), then put the parameter back in. <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15> I mean, it ain't like we do this every day, and it ain't like I've got two full pages of code! We might print out some blanks once a month.

    Update: Hans, I tried your idea #3, and it worked beautifully! I had to modify the approach just a wee bit. I had to retain the data source (a query) for the report--otherwise, the resulting report would have no course information. So I also created a copy of the query and modified it to remove any reference to students.

    Thanks!! <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

    Thanks, everybody!

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

    Re: Print blank report if parameter Null (A2K, SR1)

    I believe you're absolutely right, but see my update to my most recent post. Thank you!

Posting Permissions

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