hi Annisw
On my main form (which is a navigation form) I make a call to the getLogOnName function.
In my DB, I have table (called User_ACL) that has the fields UserID,FormName,CanEdit,CanSee
Here is a sample of my code.
On the main forms 'on load' event
MyUser = GetLogonName()
Me.LogonName = MyUser
Me.UserDisplayName = DLookup("DisplayName", "Qry_LoggedOnUser")
Me.UserEmail = DLookup("EmailAddress", "Qry_LoggedOnUser")
Me.Dept = DLookup("Department", "Qry_LoggedOnUser")
Me.Office = DLookup("Office", "Qry_LoggedOnUser")
Me.UserID = DLookup("ID", "Qry_LoggedOnUser")
MyUserId = Me.UserID
sql = "SELECT dbo_NPY_ACL_User.User_ID, dbo_NPY_ACL_User.NavBtn_ID, dbo_NPY_ACL_User.CanSee, dbo_NPY_ACL_User.CanEdit" & vbCr _
& "FROM dbo_NPY_ACL_User" & vbCr _
& "WHERE (((dbo_NPY_ACL_User.User_ID)=" & MyUserId & ") AND ((dbo_NPY_ACL_User.CanSee)=False)) OR (((dbo_NPY_ACL_User.User_ID)=" & MyUserId & ") AND ((dbo_NPY_ACL_User.CanEdit)=False));"
MyQry = "Qry_NPY_ACL_User"
Set rsDAO = CurrentDb.OpenRecordset(sql, dbOpenDynaset, dbSeeChanges)
'MyMsg = MsgBox("DAO Records = " & MyDAORCount, vbOKOnly, "Record Count")
'If rsDAO.RecordCount > 0 Then
Debug.Print "DAO Records"
While (Not rsDAO.EOF)
' Debug.Print rsDAO.Fields("User_ID"); rsDAO![NavBtn_ID]; rsDAO![CanSee]; rsDAO![CanEdit]
'Debug.Print rsDAO![NavBtn_ID]
MyBtn = rsDAO![NavBtn_ID]
If Not (rsDAO![CanSee]) Then
Select Case MyBtn
'Simply hides the navigation buttons from users if they dont have permission to that option
Case 14
If Not (rsDAO![CanSee]) Then
'[NavigationSubform].Form![FindItinerary].Visible = False
End If
Me.NavigationButton14.Width = 0 'TRAVEL
Case 17
Me.NavigationButton17.Height = 0 'TRAVEL - Itinerary
Case 22
Me.NavigationButton22.Width = 0 'VPM
end select
Perhaps you could modify this to suit your needs?