Restricting Access to Certain Information

I’m creating a database for budgeting purposes and because I don’t want 
budgeting managers to see the salaries for other departments, I’d like to 
restrict access.

I have essentially created 3 tables:

The first is a table that has the following fields:
Table #1:  Departments
DepartmentID  	(key)
Department Name	(Text)

Table #2:  Salaries
EmployeeID  		(key)
EmployeeName  	(Text)
Salary		   	(number)
DepartmentID    	(lookup from Table #1)    
Budgeting Manager  	(yes/no)

Table #3:  Expenses
ExpenseID          (key)
DepartmentID    (lookup from Table #1)
AnnualBudget    (number)

I’d like to create forms where budgeting managers can add individuals to 
their department (Table #2) or expenses to their department (Table #3).  
However, I’d like to restrict the information that they have to their own 
department.  (i.e. the budgeting manager/s for Department #2 would only be 
able to view the Salaries and Expenses associated with Department #2.)

How do I do this?  

0
Utf
4/26/2007 12:54:01 AM
access.forms 6864 articles. 2 followers. Follow

3 Replies
570 Views

Similar Articles

[PageSpeed] 57

Presumably you have some mechanism whereby the user must sign in when they 
open the database, and you can use that to determine which department they 
are from.

You would then use the Open event procedure of the form to set its 
RecordSource so that the form displays only the data for that department, 
e.g.:
    Dim strSql As String
    strSql = "SELECT Salaries.* FROM Salaries WHERE DepartmentID = 99;"
    Me.RecordSource = strSql

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Alberto" <Alberto@discussions.microsoft.com> wrote in message
news:2A532B65-2776-4F1E-8484-FB5146B9A5E4@microsoft.com...
> I’m creating a database for budgeting purposes and because I don’t want
> budgeting managers to see the salaries for other departments, I’d like to
> restrict access.
>
> I have essentially created 3 tables:
>
> The first is a table that has the following fields:
> Table #1:  Departments
> DepartmentID  (key)
> Department Name (Text)
>
> Table #2:  Salaries
> EmployeeID  (key)
> EmployeeName  (Text)
> Salary    (number)
> DepartmentID    (lookup from Table #1)
> Budgeting Manager  (yes/no)
>
> Table #3:  Expenses
> ExpenseID          (key)
> DepartmentID    (lookup from Table #1)
> AnnualBudget    (number)
>
> I’d like to create forms where budgeting managers can add individuals to
> their department (Table #2) or expenses to their department (Table #3).
> However, I’d like to restrict the information that they have to their own
> department.  (i.e. the budgeting manager/s for Department #2 would only be
> able to view the Salaries and Expenses associated with Department #2.)
>
> How do I do this? 

0
Allen
4/26/2007 3:02:31 AM
Thanks Allen.  Follow-up question:

You state "Presumably you have some mechanism whereby the user must sign in 
when they open the database, and you can use that to determine which 
department they are from."

What is the best way to do this given the scenario I've listed above?



"Allen Browne" wrote:

> Presumably you have some mechanism whereby the user must sign in when they 
> open the database, and you can use that to determine which department they 
> are from.
> 
> You would then use the Open event procedure of the form to set its 
> RecordSource so that the form displays only the data for that department, 
> e.g.:
>     Dim strSql As String
>     strSql = "SELECT Salaries.* FROM Salaries WHERE DepartmentID = 99;"
>     Me.RecordSource = strSql
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "Alberto" <Alberto@discussions.microsoft.com> wrote in message
> news:2A532B65-2776-4F1E-8484-FB5146B9A5E4@microsoft.com...
> > I’m creating a database for budgeting purposes and because I don’t want
> > budgeting managers to see the salaries for other departments, I’d like to
> > restrict access.
> >
> > I have essentially created 3 tables:
> >
> > The first is a table that has the following fields:
> > Table #1:  Departments
> > DepartmentID  (key)
> > Department Name (Text)
> >
> > Table #2:  Salaries
> > EmployeeID  (key)
> > EmployeeName  (Text)
> > Salary    (number)
> > DepartmentID    (lookup from Table #1)
> > Budgeting Manager  (yes/no)
> >
> > Table #3:  Expenses
> > ExpenseID          (key)
> > DepartmentID    (lookup from Table #1)
> > AnnualBudget    (number)
> >
> > I’d like to create forms where budgeting managers can add individuals to
> > their department (Table #2) or expenses to their department (Table #3).
> > However, I’d like to restrict the information that they have to their own
> > department.  (i.e. the budgeting manager/s for Department #2 would only be
> > able to view the Salaries and Expenses associated with Department #2.)
> >
> > How do I do this? 
> 
> 
0
Utf
4/26/2007 12:10:01 PM
Use a macro named AutoExec to OpenForm in dialog mode.

This is a simple little unbound form where the user enters whatever you 
expect of them, e.g. username, password, department, whatever.

You then set the form's Visible property to yes (so it remains open in the 
background), and you can read the department from the form when you need it.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Alberto" <Alberto@discussions.microsoft.com> wrote in message
news:B6D7D429-5FE2-453A-BD57-F72A390B5C50@microsoft.com...
> Thanks Allen.  Follow-up question:
>
> You state "Presumably you have some mechanism whereby the user must sign 
> in
> when they open the database, and you can use that to determine which
> department they are from."
>
> What is the best way to do this given the scenario I've listed above?
>
>
>
> "Allen Browne" wrote:
>
>> Presumably you have some mechanism whereby the user must sign in when 
>> they
>> open the database, and you can use that to determine which department 
>> they
>> are from.
>>
>> You would then use the Open event procedure of the form to set its
>> RecordSource so that the form displays only the data for that department,
>> e.g.:
>>     Dim strSql As String
>>     strSql = "SELECT Salaries.* FROM Salaries WHERE DepartmentID = 99;"
>>     Me.RecordSource = strSql
>>
>> "Alberto" <Alberto@discussions.microsoft.com> wrote in message
>> news:2A532B65-2776-4F1E-8484-FB5146B9A5E4@microsoft.com...
>> > I’m creating a database for budgeting purposes and because I don’t want
>> > budgeting managers to see the salaries for other departments, I’d like 
>> > to
>> > restrict access.
>> >
>> > I have essentially created 3 tables:
>> >
>> > The first is a table that has the following fields:
>> > Table #1:  Departments
>> > DepartmentID  (key)
>> > Department Name (Text)
>> >
>> > Table #2:  Salaries
>> > EmployeeID  (key)
>> > EmployeeName  (Text)
>> > Salary    (number)
>> > DepartmentID    (lookup from Table #1)
>> > Budgeting Manager  (yes/no)
>> >
>> > Table #3:  Expenses
>> > ExpenseID          (key)
>> > DepartmentID    (lookup from Table #1)
>> > AnnualBudget    (number)
>> >
>> > I’d like to create forms where budgeting managers can add individuals 
>> > to
>> > their department (Table #2) or expenses to their department (Table #3).
>> > However, I’d like to restrict the information that they have to their 
>> > own
>> > department.  (i.e. the budgeting manager/s for Department #2 would only 
>> > be
>> > able to view the Salaries and Expenses associated with Department #2.)
>> >
>> > How do I do this? 

0
Allen
4/26/2007 12:33:21 PM
Reply:

Similar Artilces:

Access Subtotal in Query
I have a simple query that uses 3 fields. Location, Person and Value. When I group by location and person and sum value, I get all the results I need but I would love to see subtotals for each location. I'm using Access 2003. How do I add in the subtotals. Something like Maryland, Person 1, 5 Maryland, Person 2, 10 Maryland Subtotal, 15, - this is the line I'm trying to get New York, Person 1, 20 Do this in a report. In report design view, open the Sorting And Grouping box (View menu.) Choose the Location field, and in the lower pane of the dialog, set Group Footer to Yes. You ...

Accessing Portfolio
Using Money v.14 (2004 I think) Up until a few days ago I accessed my investment portfolio by clicking on 'Investing' then 'view my portfolio'. Now 'Investing' brings up a page of msn marketing bumf, and I can't find any route on it to my portfolio details! I just want to access my accounts. How can I bypass this marketing junk? Jonathan Gibbs ...

Restricting Input
Ok, I'm on a roll here. Could someone please tell me how to set up a cell restriction that will only allow data input if another cell is "empty" or has a certain value? Kind of like an IF function, only when "true" exists, I can input any value, and when "false" exists, I must leave it blank. Thanks in advance. Randy, you can accomplish this using data validation. Select: data-validation, then input your restriction. Randy Vieira wrote: > Ok, I'm on a roll here. Could someone please tell me how to set up a cell > restriction that will ...

Access- How to log which user has modified access db?
Hi All, We have one MS Access database, which is being shared/accessed by number of users at the same moment. Few of them are admin users too who can perform update/delete/insert operations too. Now we have a requirement to log which users has perform which update operations. Is there some way, can you please advise. Thank you so much in advance, Best regards, Sunil Somani If you have User Level security enabled, you can add a column to each table and call the CurrentUser() function and add that user in the before update event of the form. Without that, you can use t...

copying information from various excel files
HELP PLEASE!!! I need your help with the following situation: I have multiple excel files that have the same exact structure. Have to search across all of the files for two different strings, which should both be prompted (stra and strb). This information prompted should be compared, (stra) with the values in column c (if this is true), then strb with the values in column d. If both criterias are located in the SAME row, I have to display the information on columns e and f. Thanks to all of you for your help. Marcelo This might work for you if all your workbooks are in the same folder. ...

Restrict calendar?
We are currently looking at moving to Exchange 2003 this year. Our employers are holding off on the purchase due to the fact that there is no way to restrict certain users from even being able to see "Free/Busy" on their calendars. They want it so that a whole Active Directory OU can be set to not have any calendar access to another OU's calendar. Does anyone know if this is possible? This is huge for us so a great big Thank You in advance to those who might have suggestion. sgsundqu wrote: > We are currently looking at moving to Exchange 2003 this year. Our > employe...

Pricing restriction to price lists
Most of our customers do no wish to load all products to MS CRM. Therefore the system restriction to use price lists is too inflexible. In case of opportunities it is even not possible to enter manual prices on product level. ...

Restrictions
All of a sudden I am no longer able to click on hyperlinks embedded within email and get an error message "THIS OPERATION HAS BEEN CANCELLED DUE TO RESTRICTION IN EFFECT ON THIS COMPUTER. PLEASE CONTACT YOUR SYSTEM ADMINISTRATOR." I haven't knowingly changed anything but some other program may have. How do I get back to normal? See if the articles at http://support.microsoft.com/?kbid=3D310049 and http://support.microsoft.com/?kbid=3D307818 help with this.=20 --=20 Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for=20 Administrator...

unauthorised to access page message
I just thought I would share this interesting issue with the group, I have been rolling out CRM without to many issues but then I had one laptop which refused to allow the user access to the CRM server. The error message was reported as "unauthorised to access page" error. after a lot of checking the configuration and playing with security levels etc I found by chance that the laptop had not completed a recent Windows update correctly. This was resolved by booting into safe mode, connecting to the windows update site running an outdate ( this failed as expected ) and then reb...

Access 2007 freezes with everything
Hi, We've currently upgraded our Office 2002 to Office 2007. I know that with new programs comes "bugs" and "issues" to be worked out. However, we seem to be encountering a big problem with Access 2007. When opening an existing database (created in Access 2002), Access 2007 will allow the program to open, but if you try to do anything with that database....bam! it freezes. When I say anything, I mean just that. --If it's an existing database (any), as soon as you click in a record...Access freezes. --If it's an existing database (any), and you want ...

Is this possible in Access?
I have searched and searched for the 'perfect bingo program'. I have a few, but they don't do exactly what I want.I would like to create custom pages for printing, with one or two cards on each page. B4 landscape for single cards, B4 Portrait for doubles. A total of 50 pages for singles, 25 for doubles.On each page, I want to place a 5x5 grid with the numbers 1-25 placed at random. This I will do manually, I don't need access to generate random patterns for each page.Then, I would like to load a series of pictures, saved with the file names 1.jpg-25.jpeg.On each page, based on ...

Restrict DB Access
Is there a way in GP7.5 to restrict a user to only see a single SQL Database/Server when they login to GP? Thanks It's unclear exactly what you want to do. In GP, you can restrict access by user to the company databases via the User Access window. When you set up the ODBC DSN, you are determing which database server the user can access through GP. Additionally, if you happen to have multiple servers, the best way to handle it is to have different user accounts. -- Charles Allen, MVP "JDR" wrote: > Is there a way in GP7.5 to restrict a user to only see a si...

Converting access report to pdf format
hi I wrote a database that, among other things, prints a variety of reports for my clients. Once each report is done, I run a program called pdfWriter to convert each report to a pdf file. (When this program is installed, I select the printer called pdfWriter then print the file. It doesn't actually print a physical copy but saves the file in pdf format.) Then I e-mail these pdf reports to the clients. What I'm looking for are ways that I could automate this process from Access. Can this be done? If so, could someone point me in the right direction. Any clues would be grea...

Preparing for Access 2010
I'm preparing my application's installation packaging and documentation for ultimate release of Access 2010. Checking with MS, I see that two versions are available (and quite a bit different in size). One is Access 2010 32-bit. The other is Access 2010 64-bit. There's also a download link for each ... I guess this dnwloads the beta version. Here's my question... The MS specs don't specifically state this, but will the 32-bit version of Access 2010 Runtime work on 64-bit Windows 7 ? It'll obviously install into the 32-bit "Program Files"...

Restricting no. of recipients
Hello, We are using Exchange 5.5 server now and for some reason, we need to restrict the number of recipients that an user can send at once. I tried to find at most of configuration dialogs, but could not find it. Is it possible to enable this function in Exchange 5.5? Or is there a 3rd party tool regarding this? Appreciate it if you can find it or help me. Thanks. Richard You can set this, but it is for the maximum number of recipients in the message before distribution list expansion, so a distribution list counts as one recipient. It is much more accurate in Exchange 2000/2003....

Exchange Outlook Web Access Quirk
Am in the process of testing Exchange 2003 on our network, and so far all has gone well except for one quirk in Outlook Web Access, which I access through a VPN connection. All goes well until I try and delete an email while in Outlook Web Access, and then the email deletion hangs. The deletion finally occurs but only after an inordinate amount of time. For some reason, this only happens in IE, not in Firefox or other browsers; they work like a champ. Thought it might be a security issue, so loosened the security settings in IE, but still hangs when I delete an email. Go figure. -- E...

Information Store is high even after cleanout
I had my Exchange 5.5 users clean out their email accounts since I was nearing the 16GB limit. They removed approximately 2.6GB of mail. When I check the backup, the Information Store size has not decreased. What causes this? Is the space just allocated? Does it need to be defragged? Thanks, John "AnytimeAnywhere" <gm79ta@yahoo.com> wrote in news:eeUZl3ApFHA.2444@tk2msftngp13.phx.gbl: > I had my Exchange 5.5 users clean out their email accounts since I was > nearing the 16GB limit. They removed approximately 2.6GB of mail. > When I check the backup, the I...

restrict to export records to Excel and restrict to print
Hi, It is a major requirement for us to restrict some users to export Microsoft CRM records to Excel file and to restrict them to print as stated in our implementation policies. Is there a possible workaround for this restriction? Would greatly appreciate for your fedback. Lot of thanks in advance. In v1.2 there is no easy way to do this. v3.0 should enable this via security roles. In thoery, you could do something with javascript that conditionally removed the link, but not sure how reliable it would be as it would need to do a server-based call to determine the security. Matt Parks...

Outlook Web Access and OutlookXP
Is it possible to configure Outlook XP to send and receive mail from an Outlook Web Access account? The Outlook Web Access account has a url as follows: http://webmail.XXXX.XX. After I login through the web page, the url changes to https://webmail.XXXX.XX/exchange. Ideally I'd like to automatically download and send mail through Outlook XP rather than have to go to this website to get mail from this one account. Thanks in advance. locally? If you are a memeber of the domain just go through outlook setup. But remotely you can remote desktop to a computer set up on your domain...

MS Access for Mac?
Bought my 1st Mac this week & purchased Office 2004 Pro for it. I was surprised to find that it doesn't include Access. When I asked the vendor to check, he told me that he doesn't think Access is available for Macs. Can this be true? Is there any viable alternative? TIA, Phil Sharpe "Phil Sharpe" <a@b.com> a �crit dans le message de news:%23DrsFs8IFHA.1948@TK2MSFTNGP14.phx.gbl... > Bought my 1st Mac this week & purchased Office 2004 Pro for it. > > I was surprised to find that it doesn't include Access. > > When I asked the vendor...

Access 2003
I want to use a lookup table to fill a number of boxes on a form. I have created a combo box from where I pick the initial info, and once picked the other text should be filled in. How do I go about this? Hi If they are bound controls (text boxes) use the AfterUpdate event of the combo Private Sub ComboName_AfterUpdate() Me.ControlName1 = Me.ComboName.Column(1) Me.ControlName2 = Me.ComboName.Column(2) Me.ControlName3 = Me.ComboName.Column(3) 'ect' End Sub If they are not bound to a table field you could just use = ComboName.Column(3) as the control source for the text boxes Hope ...

ME Information Store crash
I try to start Microsoft Exchange Information Store (service) and return 2147746063 and the services don't start i execute the command ininteg -pri -fix -test alltests (exec 22 test, repair ok) i have exchange server 5.5 sp 4 any ideas? thanks, Jose Orlando is this relevant? http://support.microsoft.com/?id=265437 "Jose Orlando" <jorlando@ciudadceleste.com> wrote in message news:uT%23YMZ$rEHA.3896@TK2MSFTNGP15.phx.gbl... >I try to start Microsoft Exchange Information Store (service) and return > 2147746063 and the services don't start > > i execute ...

Subforms are slow on an Access Project
I converted a 2003 Access .mdb database to an Access .adp project and one of the forms took a huge performance hit. The form was bound to a table with 3000 + records and contained 6 sub-forms placed on pages in a tabl control. One of the tab pages displays data from the main table and the others display data from the child tables. When the user changes a field on the main table and moves to another field there is a 3 to 4 second delay while the status bar below displays a "Calculating" message. Presumably the Acces project is updating the data on the other sub-forms even thou...

Accessing CDocument from Generic Class
In my Document class I have a variable for the Recordset. How do I access the document object to get the recordset object from a generic class. Ex. CSupplierDoc CSupplierRS m_pSupplierRS; CSupplier From CSupplier I want to get a hold of m_pSupplierRS. William Gower wrote: > In my Document class I have a variable for the Recordset. How do I access > the document object to get the recordset object from a generic class. > > Ex. CSupplierDoc CSupplierRS m_pSupplierRS; > > CSupplier > > From CSupplier I want to ...

Conversion Access 2000 to Access 2003
Remember converting from Access 97 to Access 2000. Access 2000 used a different version of Visual Basic Code. Some of your Access 97 applications did not run correctly in Access 2000 until you modified or debugged some Visual Basic Code. Will I have to debug or modify Visual Basic Code in an Access 2000 to Access 2003 conversion? Hi Berry, There's less change in the VBA department between 2000 and 2003 than between 97 and 2000 and the conversion is usually trouble-free. See http://www.granite.ab.ca/access/conversionproblems.htm and http://support.microsoft.com/?id=319400 for more...