Opening a report from a form so that the last report is visable and sorted by a field

[daverskully]

Table: Data
Form: Entry Form

Fields:
1) Department
2) DepartmentNumber
3) AccountExecutive
4) ReportDate
5) ID
6) 7 yes/no fields
7) 6 memo fields



Problem: I would like to be able to open the last updated report in the EntryForm form, filtered (filtering the best option?) by way of Department (open the report that was last entered for that department), so that it can be viewed while the individual is entering a new form.

Currently, I have a button set up to take me to the Data Reports, however, it lists all reports for all departments and does not go to the last one for the department.
I need to have it so that it filters each department, based on a department that I select, and also have it so that it goes to the last record of that department. I'm having a lot of trouble with this, I don't really even know where to start, it's been a while since I've used filters.

[Stewart Ross]

Table: Data
Form: Entry Form

Fields:
1) Department
2) DepartmentNumber
3) AccountExecutive
4) ReportDate
5) ID
6) 7 yes/no fields
7) 6 memo fields

Problem: I would like to be able to open the last updated report in the EntryForm form, filtered (filtering the best option?) by way of Department (open the report that was last entered for that department), so that it can be viewed while the individual is entering a new form.

Currently, I have a button set up to take me to the Data Reports, however, it lists all reports for all departments and does not go to the last one for the department.
I need to have it so that it filters each department, based on a department that I select, and also have it so that it goes to the last record of that department. I'm having a lot of trouble with this, I don't really even know where to start, it's been a while since I've used filters.

Hi Dave. terminology is a little confusing in your post, as you are referring to what must be user data as reports - easy to confuse with Access print-formatted reports.

The recordsource for your EntryForm form is either the Data table or a query based on that table. You can show users the most recent data by basing the recordsource for this form on a query (if it isn't already) that includes all fields from the Data table. The query would be sorted by report date in descending order.

To filter for a specific dept to view you can select a department by adding a combo box to the form with the Data Reports button you mention then applying a filter for the department when opening the EntryForm form.

The rowsource for the combo would be the table which holds your departments, and you would include the department number and department name in the combo. You could then apply a filter that filters the EntryForm records for the chosen department when the button you referred to is pressed. The code for this in the on-click event of your button is like

If IsNull(Me![name of your dept select combo]) then
    DoCmd.OpenForm "EntryForm" 
else
    DoCmd.OpenForm "EntryForm",,,"[DepartmentNumber] = '" & Me![name of your dept select combo] & "'"
endif

This assumes your department number is a string value. If it is a number, change the else part to

    DoCmd.OpenForm "EntryForm",,,"[DepartmentNumber] = " & Me![name of your dept select combo]

-Stewart