By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,336 Members | 2,307 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,336 IT Pros & Developers. It's quick & easy.

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

P: 4
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.
Mar 5 '08 #1
Share this Question
Share on Google+
1 Reply


Expert Mod 2.5K+
P: 2,545
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
Expand|Select|Wrap|Line Numbers
  1. If IsNull(Me![name of your dept select combo]) then
  2.     DoCmd.OpenForm "EntryForm" 
  3. else
  4.     DoCmd.OpenForm "EntryForm",,,"[DepartmentNumber] = '" & Me![name of your dept select combo] & "'"
  5. endif
This assumes your department number is a string value. If it is a number, change the else part to
Expand|Select|Wrap|Line Numbers
  1.     DoCmd.OpenForm "EntryForm",,,"[DepartmentNumber] = " & Me![name of your dept select combo]
-Stewart
Mar 9 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.