Learn how to a build a cloud-first strategyRegister Now


Not allow a value change in a combo box if you are not authorized.

Posted on 2007-08-08
Medium Priority
Last Modified: 2010-03-19
I have a combo filed in Access Project (backend Sql Server) that only specific set of users are allowed to change the value.   This code works somewhat in that it will give you an error but will still allow the value to change and the undo command does not work to undo the value and gives an error  and the "not authorized person" is able to change the value.   I have tried to use Beforeupdate, afterupdate and even mouse down.   Any help .....please.

Dim pername As String
Dim adminname As String
Dim executive As String

pername = "TerryS"
adminname = "PeggyW"
executive = "GeorgeL"

If pername = fOSUserName() Then
 If adminname = fOSUserName() Then
    If executive = fOSUserName() Then
 DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
MsgBox fOSUserName() & ", You are not permitted to change the Rating."
Exit Sub
End If
End If
End If
End Sub

Question by:peggyweber
LVL 35

Expert Comment

by:David Todd
ID: 19659088

One way is to limit the users rights to the table in question, rather than trying to get the combo box to do it your way.

How are you connecting to SQL, via Windows Authentication?


Author Comment

ID: 19659097
The all users have rights to all fields in this table except one particular field in the same table.  I have a DBA that won't give me Sql Server Access through SQL Enterprise and her understanding of Sql is less then is desired.  So  I have to do all my coding through VBA.....and why my hair is going grey.

Yes it is by windows Authenticaiton.
LVL 19

Expert Comment

ID: 19659140
i generally avoid the undo command and handle using the following
1) declare a form level variable say sOriginal
2) create a function say fnOriginalValue that sets this  variable to the "original" value when the user enters
3) call this function in the combo box enter event (might also try the click event)
4) in the after event of the combox, if the new value is not valid, then set the combo box value back to the original
me.cboWhatever.value = sOriginal

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.


Accepted Solution

NathanIrwin earned 2000 total points
ID: 19659255
I have done a bit of this reciently, as I have a front end which requires limited access to certain functionality. Basically I created my own AppPermissions table in SQL Server so I am able to have multiple user have access to specific functions.
I will not add the full code here at this point unless you want me to, but the following code will allow you to determine who the current user is. If you want me to post the rest of the code I will.

SQL Stored Procedure to determine the current user:

-- ======================================================================
-- Author:            Nathan Johnston
-- Create date: 1/8/2007
-- Description:      Determines and returns the current user
-- ======================================================================
CREATE PROCEDURE [dbo].[spCurrentUserID]
@CurrentUserID varchar(10) OUTPUT

SELECT @CurrentUserID = user_name()


The Function I use to call this procedure and then use it, is in a modual called ApplicationAccess in Access and is as follows:

Function CurrentUser() As String
Dim cmdCurrentUser As ADODB.Command
'Calls the spCurrentUserID sproc to retrieve the ID of the current user. The value
'is stored in the prmUserID parameter and is returned when the function is called

'Declare Command Object parameters
Dim prmUserID As ADODB.Parameter

'Instantiate and set the Object properties
    Set cmdCurrentUser = New ADODB.Command
    With cmdCurrentUser
        .ActiveConnection = CurrentProject.Connection
        .CommandType = adCmdStoredProc
        .CommandText = "dbo.spCurrentUserID"
    'Create and attach command parameter
        Set prmUserID = .CreateParameter("@CurrentUserID", adVarChar, adParamOutput, 10)
        .Parameters.Append prmUserID
    End With

'Execute the Command Object

'Assign OUTPUT values to Function
    CurrentUser = prmUserID
'Clear objects
    Set cmdCurrentUser = Nothing
End Function

Hope this gives you some ideas.

LVL 35

Expert Comment

by:David Todd
ID: 19659278
Hi peggyweber

The others have some good suggestions, but it would have been easier if you or DBA could have simply gotten the permissions right.

Best of luch

PS On behalf of good DBAs everywhere, sorry about your grey hair.
LVL 19

Expert Comment

ID: 19704462
has this been resolved?

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question