Opening a form with only certain records of a table

[Knitter]

I have a form based upon a table. This table holds records for all departments. For security purposes, I need the form to only allow the user to see records related to his/her department only (usually, there is more than one record). Also, if the user needs to add a new record, I need the bound text control to enter that user's department automatically, and now allow them to change it.

I suspect that this text control (txtBusinessArea) should be visible, but not enabled. I can pass the department name, i.e, "Financial Services" to the form. Also, should this work be performed in the load event or the open event of the form?

 

[Garridon]

I have something similar, though I did it very differently that what you're describing. I used Microsoft's security wizard to password protect the database, assigning different permissions for forms, queries, reports, etc. For instance, the sales department has a menu where they can add records, edit records, view reports and such. They cannot access the purchasing department's menu because they don't have permission to do so. For each department, the query criterias are set up to show only their records.

Linda Adams
Visit my web site for writing and Microsoft Word tips:

http://www.hackman-adams.com

Official web site for actor David Hedison:

http://www.david-hedison.com

 

[RottPaws]

I'll assume you have some mechanism for storing the department of the current user. If you want, just ask and I can tell you how I handle this. (I don't like the Access security setup, so I created my own login, etc)

In the Form_Open event, change the recordsource to a query based on the current user's department:

Private Sub Form_Open(Cancel As Integer)
Form.RecordSource = "SELECT * " & _
"FROM TableName " & _
"WHERE Dept = '" & DeptVariable & "'"

End Sub

This way, you have a single form to maintain rather than separate forms/queries for each department. Also, if a new department is created, you shouldn't have to make any changes to your form to support it.

Set the Default Value property on the form to the DeptVariable. This will automatically put that in for new records.

Set either the Locked Property to True (Yes) or the Enabled property to False (No) to prevent the user from changing the department information. If you set Enabled to True and Locked to False, the user will be able to select text in the field and copy, but not change the field's contents. If Enabled is False, the Locked becomes irrelevant. _________
Rott Paws

...It's not a bug. It's an undocumented feature.