Form Coding Help (1 Viewer)

[ecupirate]

Form Coding Help

I would like a suggestion of how to do the below. My database consist of eight different departments with their salary information. I have built 8 reports & 8 Queries that contain the exact same info except the department, my goal is just to build one report and one query and have a parameter to filter the data. My problem is that one department is not allowed to see the others salary info. That is the reason for the 8 different queries/reports. I have a Access secured form for each department, with the reports running on a onclick event

How do I code the forms to use the same query and pass that department names as the parameter. Something like a parameter query, but hard coded behind the form. I Have multiple expressions in my query and my report has a really detailed format. Can I do anything like the below, or if i have to do a SQL statement how do I reference the report I want to fill from my query.

DoCmd.OpenReport "rptSalaryInfo", acPreviewacForm, where department = "BusLoanOps"

 

[RichMorrison]

Sounds like 2 problems:
1) how to use a Department parameter in a query,
2)how to allow each user to only see their own department.

1) You can Design and save a query with a criteria value such as Forms![myForm]![txtDepartment] where "txtDepartment" is a text box on your form. Use this query as the RecordSource for a report.

2) You need to get each user's Department into txtDepartment but not let them change to another Department. You can make txtDepartment "locked".

But how to assign a locked value for txtDepartment ? The simplest way I can think of is make a table with UserName and Department. When the form opens, look up Department based on CurrentUser(), a function that returns the User Name from the Access login form. Of course, you or someone has to maintain the User/Department table.

HTH,
RichM