To print a report that has a serial number

basicjohn

New Member
Joined
Aug 9, 2005
Messages
14
I have created a form where you can add a record to a table with an autonumber used as the report serial number. I am able to preview all the report. Is there a way to print these reports only limited to the records that I have added through the form. Meaning for example user A enters 3 records ( no. 1, 3, 5 ), and user B enters 2 records ( no. 2 & 4 ). How can user A only prints his 3 reports only as it is not in sequence. In the table I the following fields : autonumber (Serial number), date, User name.
And the next day when I enter records again those old ones should not be printed anymore.
Hope to hear from you guys .....
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
If all records are date-stamped and marked with the user's name, all you need to do is create criteria in the query that underlies the report.
Set the DateCreated field's Criteria to Date()
Set the UserName field's Criteria to the current user's name -- if htis is available in a field, use that field.

When the report opens, it will have just the data you require.

Denis
 

basicjohn

New Member
Joined
Aug 9, 2005
Messages
14
Not quite understand

Dear Sydney,

Thank you for the reply BUT I do not quite understand. The fields of the username and the dates are in the table. What query will extract these reports and be printed by the username? Do you mean that when they click on an icon then a query runs and only print those reports that is today?

Could you please elaborate more on how to do this. Thanking you in advance.
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Quick explanation ...
The table has DateCreated (presumably automatic) and UserName (should be automatic too). To do this, set the Default Values as follows --
DateCreated should be =Date()
UserName should be =Environ("username")
The form doesn't even need to display these fields: place them somewhere on the form and set their Visible property to No.

Over to the report...
There's a couple of ways to do this. One uses code, as follows.
Open the report in Design view and go to the Properties. Click the Events tab.
In the Open line, double-click. You will see Event Procedure. Click the Builder button to go to the code window.
Paste these 2 lines into the blank line in the procedure:
Code:
  Private Sub Report_Open(Cancel As Integer)
    Me.Filter = "[DateCreated] = " & Date() & " And [UserName] = " & Environ("username")
    Me.FilterOn = True

The filtering is now hands-off. When a user runs the report, only records created by that user on that day will be displayed.

Note: You will need to change field names to suit

Denis
 

Watch MrExcel Video

Forum statistics

Threads
1,118,530
Messages
5,572,702
Members
412,481
Latest member
nhantam
Top