automating report print....

[totomalas]

I have developed a report in Access 2007 that runs on three queries, each promting an input from the user...this report is used for a meeting and it should be printed nine times...what I do now is open the report nine times, with each print i enter the same inputs for the first two queries and a different input for the third one...so this is becoming a hassle for me.. :(

Query1 takes the meeting date
Query2 takes the budget date
Query3 takes the member's ID ( the list of ID's is stored in a table in the database)

so in each print, the only difference is the member's ID...but now I enter the meeting date and the budget date again and again for each print.. :(

is there a way to have a loop that takes the input for the first two queries once and stores it in a variable, and reads the value for the third query from the table (with each cycle it reads the next record and gives it to the query, instead of promting the user to enter it)?...

Or atleast save the inputs for the 1st two queries and prompt only for the member's ID..


I am really desperate to have this Loop...thanks

p.s I dont have any experience in VB programming

[ADezii]

@totomalas

1. Create a Form, and for the sake of simplicity, let's call it frmCriteria.
2. Create 2 Text Boxes on this Form and Name them txtMeetingDate and txtBudgetDate.
3. In the appropriate Fields in your Queries, instead of the Parameter Prompts, set the Criteria to the Form Fields, as in:
   

   [Forms]![Form1]![txtMeetingDate]     'for the [Meeting Date] Field
   [Forms]![Form1]![txtBudgetDate]      'for the [Budget Date] Field

4. The Criteria will be read via the Form Fields and you will not be prompted for a Meeting or Budget Date.
5. You can still set the Criteria for ID in the usual manner in conjunction with the above, as in:
   

   [ENTER Employee ID]

6. If you have a mechanism for retrieving the Employee ID, you can automate the prompting of that value also.
7. Before opening the Reports that are based on these Queries, you should make sure that Valid Dates are entered in txtMeetingDate and txtBudgetDate, as in:
   

   If IsDate(Me![txtMeetingDate]) And IsDate(Me![txtBudgetDate]) Then
     DoCmd.OpenReport "<Report Name>", acViewNormal Or acViewPreview, , , acWindowNormal
   Else
     MsgBox "You must enter Valid Dates for both Meeting and Budget", vbExclamation, _
            "Missing/Invalid Date Entries"
   End If

8. Any questions, feel free to ask.

[totomalas]

Thanks alooooooot..

this is a great solution...it works very well....

No I dont have any mechanism for retrieving the Employee ID...I was thinking of some sort of a loop in VB that takes all the records in the Employee Table one by one..all i have is nine employee, and their names must be on those report,,each by himself only...but i dont know how to write such loop.. :s

[ADezii]

@totomalas
The following code will loop through the Employees Table of the sample Northwind Database, and Print to the Immediate Window the Employee's ID, First Name, and Last Name. Oddly enough, this Table also contains 9 Employees.

Dim MyDB As DAO.Database
Dim rstEmployee As DAO.Recordset
 
Set MyDB = CurrentDb
Set rstEmployee = MyDB.OpenRecordset("Employees", dbOpenForwardOnly)
 
With rstEmployee
  Do While Not .EOF
    Debug.Print ![EmployeeID] & " " & ![FirstName] & " " & ![LastName]
      .MoveNext
  Loop
End With
 
rstEmployee.Close
Set rstEmployee = Nothing

OUTPUT:

1 Nancy Davolio
2 Andrew Fuller
3 Janet Leverling
4 Margaret Peacock
5 Steven Buchanan
6 Michael Suyama
7 Robert King
8 Laura Callahan
9 Anne Dodsworth

[totomalas]

Yes that works..this is the code that worked for me... THANKS :)


Dim MyDB As DAO.Database
Dim rstEmployee As DAO.Recordset
Dim Name As String
Dim Title As String
Set MyDB = CurrentDb
Set rstEmployee = MyDB.OpenRecordset("Members", dbOpenForwardOnly)

With rstEmployee
Do While Not .EOF
DoCmd.OpenReport "Test", acViewReport, "", "", acNormal
Name = ![Mamber's Name]
Title = ![Title]
[Reports]![Test]![txtName] = Name
[Reports]![Test]![txtTitle] = Title
Debug.Print ![ID] & " " & ![Mamber's Name] & " " & ![Title]
.MoveNext
DoCmd.OpenReport "Test", acViewNormal, "", "", acNormal

Loop
End With
rstEmployee.Close
Set rstEmployee = Nothing

[ADezii]

@totomalas
Glad you got it all to work for you.