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

Posted on 2007-08-08
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

    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

    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

    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

    LVL 7

    Accepted Solution

    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
    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

    has this been resolved?

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    731 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now