If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Restrict access to recortds
I am trying to find out how I can restrict users to only being able to
read/edit records which they have entered. Apparently I need to "limit the recordset for the form to those where the User ID is equal to the current user". Can anyone tell me exactly how to do this? Regards, Gavin |
#2
|
|||
|
|||
Restrict access to recortds
You will need to implement user level security to do that, Gavin. Get a copy
of the Security FAQ, read it several times, and follow it to the letter. Don't miss a single step. You can find a copy of it on the Security page of my website. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "gavin" wrote in message ... I am trying to find out how I can restrict users to only being able to read/edit records which they have entered. Apparently I need to "limit the recordset for the form to those where the User ID is equal to the current user". Can anyone tell me exactly how to do this? Regards, Gavin |
#3
|
|||
|
|||
Restrict access to recortds
User Level security will restrict users to tables, not row
level. How are you getting the user's username? By using the CurrentUser function, or Windows login, or ??? All you need to do is to create a query that says: Select * from Table where userName= CurrentUser Base the form off that query. Chris Nebinger -----Original Message----- You will need to implement user level security to do that, Gavin. Get a copy of the Security FAQ, read it several times, and follow it to the letter. Don't miss a single step. You can find a copy of it on the Security page of my website. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "gavin" wrote in message ... I am trying to find out how I can restrict users to only being able to read/edit records which they have entered. Apparently I need to "limit the recordset for the form to those where the User ID is equal to the current user". Can anyone tell me exactly how to do this? Regards, Gavin . |
#4
|
|||
|
|||
Restrict access to records
You need a column in the table 'createdby' which holds the person who created the record. Then in your SQL simply code: SELECT .... WHERE createdby = current user
To be more reliable, I always code: WHERE ucase(createdby) = ucase(current user) to make sure you are always dealing with the same case The function to get the current user is available on the internet, do a searcxh for 'fosusername' |
#5
|
|||
|
|||
Restrict access to recortds
Chris,
Without implementing user level security, ALL users who open the database will be logged in as Admin. Thus your query, without user level security, will pull ALL records from the table, assuming there is a userName field that has been populated with the CurrentUser. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Chris Nebinger" wrote in message ... User Level security will restrict users to tables, not row level. How are you getting the user's username? By using the CurrentUser function, or Windows login, or ??? All you need to do is to create a query that says: Select * from Table where userName= CurrentUser Base the form off that query. Chris Nebinger -----Original Message----- You will need to implement user level security to do that, Gavin. Get a copy of the Security FAQ, read it several times, and follow it to the letter. Don't miss a single step. You can find a copy of it on the Security page of my website. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "gavin" wrote in message ... I am trying to find out how I can restrict users to only being able to read/edit records which they have entered. Apparently I need to "limit the recordset for the form to those where the User ID is equal to the current user". Can anyone tell me exactly how to do this? Regards, Gavin . |
#6
|
|||
|
|||
Restrict access to recortds
Gavin,
You can use the network's (or PC's) User Name for each person. Let's say that you have a table called tblCustomers. Create a field in the table called UserName. Create a Main Menu (frmMainMenu), as well as TWO forms for Customers: one for ADDING NEW Customers (frmCustomers_ADD) and a second for EDITING EXISTING records called "frmCustomers_EDIT". (Make the RecordSource for frmCustomers_ADD: "Select * from tblCustomers where false"; make the Record Source for frmCustomers_EDIT: "Select * from tblCustomers where UserName = " & GetUser() Now, create a text control on BOTH of these forms called UserName (just as it is called in tblCustomers). Using the Properties for each of the UserName controls, set Locked to Yes, set the ForeColor to 128, and the TabStop to No. (This will prevent anyone from changing the data in UserName manually via the forms.) Now, in the frmCustomers_ADD form, use this code: Private Sub Form_BeforeUpdate() If IsNull(Me![UserName]) Or Me![UserName] = "" Then Me![UserName] = GetUser() Else 'do nothing End If End Sub Next, in the frmCustomers_EDIT form, SKIP the above code, since the UserName should already be in the table and thus on the form where it cannot be altered. ================================================== == In the form frmCustomers_ADD AND frmCustomers_EDIT "General Declarations" section, type: Option Compare Database Option Explicit Private Declare Function GetUserName Lib "Advapi32.dll" Alias "GetUserNameA" (ByVal sBuffer As String, nSize As Long) As Long Private Function GetUser() As String Dim sUserName As String Dim lgSize As Long Dim lgLength As Long sUserName = String(15, " ") lgSize = Len(sUserName) lgLength = GetUserName(sUserName, lgSize) GetUser = Left(sUserName, lgSize - 1) End Function ================================================== ===== The result: When you add a NEW customer, the Windows user name for the data entry person will automatically be entered. In addition, when you open the other form for editing/viewing, only you will be able to see the records you entered. There are many other ways to do this, but the use of the Windows/Network User Name is rather handy. I use it in many applications, where I save the: DateCreated 'using Form_BeforeUpdate() DateLastModified 'using Form_BeforeUpdate() CreatedBy 'using the GetUser() function with Form_BeforeUpdate() LastEditedBy 'using the GetUser() function with Form_BeforeUpdate() ---Phil Szlyk "gavin" wrote in message ... I am trying to find out how I can restrict users to only being able to read/edit records which they have entered. Apparently I need to "limit the recordset for the form to those where the User ID is equal to the current user". Can anyone tell me exactly how to do this? Regards, Gavin |
Thread Tools | |
Display Modes | |
|
|