filter "on load"

mmmk

Registered User.
Local time
Today, 03:34
Joined
Jun 29, 2010
Messages
39
Hello. I am creating a couple forms, one for each Area. When a user clicks on the form for their Area, I want them to only see the records related to their area. I think I should be using the "on load" event, but I have no idea how to set this up. Do I use the "Expression Builder" or "Code Builder"? I am clueless. Thanks in advance for any suggestions.
 
The Area I am referring to is more like a "Department Name", where employees will be given form permissions based on their department. This was requested to ensure employees could not alter data outside their department. This is why I have forms for each area, its was requested by the users, but i dont want the users to see all the data, just records related to their area. Thanks
 
As Paul said, you don't need a form for each area. You can limit what they see in a SINGLE form.
 
I understood the request. Either of the methods I suggested would present a filtered set of records to the user. Both would require the area to be available somewhere, but so would your multi-form solution. How would you know what form to open in your solution?
 
Sorry, I did not fully understand your link. Could you explain how I would accomplish this with single form? I am still very new to Access and unaware of most of its functionality. With multiple forms I would just name them "area_name_form", so the users would know where to go.
 
That only limits them if they choose to let it, but you can have a combo box that lists areas on a form. Have them choose their area and then click on a button to open the data form, using the technique from the link. That will restrict the data form to the records for that area.
 
And if it is vital that they do not choose a department for which they are not authorized to view the records you can use a table to assign people to their department (or departments) and then open the form automatically for them by first getting their network user ID (using this code is my preference) and then match it up in the table.
 
This sounds like a cool idea, and much more secure. I have no idea how to go about setting it up tho..
 
Paul - do you want to take that or do you want me to try to explain it? I'm okay if you want to.
 
Okay, here's a sample that I worked up for you. This is how it works:

1. There is a table called tblUsersForms and it stores the User's Network Login ID (you'll have to add yours for it to open a form for you. For anyone not in the table it opens a message box that says they do not have access to any of the data. Normally I would also have it close but not for this sample so you can get to the parts you need.

2. There is a tblDepartments table which has a primary key, a description and the SQL statement for their form.

3. When you open the database it first finds your user ID using the fOSUserName function that is in the basUserInfo module in the database.

4. Once it knows your user login name it then goes to see which department you are a part of. It returns that DeptID so then it can get the SQL String for the form.

5. The function opens the form and passes the SQL string to it, using the OpenArgs method, and then it sets the form's recordset in the form's On Open event.

6. The whole thing is started off by a MACRO (named AutoExec, which then just runs the procedure - a public function named AutoExec).


Take a look and see if you can understand it enough to implement. Paul and I are here (as well as many others) to be able to answer your questions.
 

Attachments

Wow, Thank you for the walk-thru. Im going to take a try at it and we'll see how it goes. I will probably be back with some ?s.
 
Before I try Bob's method, I would like to see if the filter on load will accomplish what im looking for. I am teaching myself as I go and this is my first attemp with VB code:

Private Sub Form_Load()
Dim StrFilter As String
StrStatus = "Ops"
StrFilter = "[Area] = '&StrStatus&'"
Me.Filter = StrFilter
FilterOn = True
End Sub

I found this code online and tryed to apply it. I know something is wrong here.
 
You would need to change this line:

StrFilter = "[Area] = '&StrStatus&'"

to this:

StrFilter = "[Area] = '" & StrStatus & "'"
 
OK, I updated my code. When I open the form, it asks for a Parameter Value for Area. When I enter Ops, the form says it is filtered, but still shows other areas, including Ops. How do I get the form to automatically filter to "Ops". Thanks Bob.
 
OK, I updated my code. When I open the form, it asks for a Parameter Value for Area. When I enter Ops, the form says it is filtered, but still shows other areas, including Ops. How do I get the form to automatically filter to "Ops". Thanks Bob.

Well, first of all - do you actually have a field in the form that is opening called Area? If not, that would be the primary problem.
 
The Area field is not in the forms record source table. is this the problem?
 

Users who are viewing this thread

Back
Top Bottom