By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,979 Members | 943 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,979 IT Pros & Developers. It's quick & easy.

User Login / Verify Permissions / Open Permissions Based Form / Access 2007

cobra35y
P: 5
Good Afternoon, I am new to the world of programming. after reviewing this site for info pertaining to my situation, i have declared a loss and posting for help. maybe i am just overlooking the situation, maybe just need a second set of eyes on this. i am in the military and have designed a unit database that allows for all kinds of tracking and reports generation for training meetings to licensing of soldiers on equipment and other administrative functions. However this database contains personal information that needs to be secured to allow only authorized users that work in the main office. this database will be placed on a network share so that all authorized users will be able to access it as needed for daily operations. my baseline was SD2 a few years ago but after a few years of using it and continuing upgrades and new units, i am required to increase the security.

Situation: All users have a smart card that they use to login to their Desktop. I have code that checks for the Windows login ID and matching that users permissions based on security level authorized and i have the baseline login screen using the code found in lots of locations on the web. now i need to make it so that only certain options are available to basic users.

Question:
I want to get rid of the password login, since the user will be authenticated by their smartcard login to windows. however how do i make it possible for the login button to check that username against the security level authorized per their ID name and then open their form and only their forms?

Possibilities:
i would love to share this database with anyone interested if they dont mind helping me with better solutions on the login funcionality in access 2007. it is hard for me to get the reference materials i need as i am stationed in korea. i have the database preped for dissemination to other programmers, i have removed all the soldier info from tables so you can play with it however you want. i have worked on this since 2007 at my last unit, it is just time to upgrade this baby for transferability to other units and still be able to maintain security.

i would post code here but dont know where to begin.

the database is in ACCDB format using Access 07, i am also learning VB 8 express, downloaded it yesterday, really trying to get a better grip on VBA Programming. but the database has sort of a deadline before they scrap the idea all together.


Any help would be great, i will send this out to anyone requesting it in a .zip file. size right now is 27MB.

contact me directly at
cobra35y@yahoo.com

or

robert.towler@us.army.mil

Thank you in advance for your time and help.
Apr 17 '10 #1
Share this Question
Share on Google+
4 Replies


cobra35y
P: 5
when opening the database this is the first code that runs opening my splash screen, A form with timer event to open the login screen.



Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. '------------------------------------------------------------
  4. ' Form_Timer
  5. '
  6. '------------------------------------------------------------
  7. Private Sub Form_Timer()
  8. On Error GoTo Form_Timer_Err
  9.  
  10.     DoCmd.OpenForm "loginscreen", acNormal, "", "", , acNormal
  11.     Exit Sub
  12.  
  13.  
  14. Form_Timer_Exit:
  15.     Exit Sub
  16.  
  17. Form_Timer_Err:
  18.     MsgBox Error$
  19.     Resume Form_Timer_Exit
  20.  
  21. End Sub
Apr 17 '10 #2

cobra35y
P: 5
Using an unbound table "Account Table"consiting of "ID" AutoNumber column "Username" Column and "Password" column. the form.loginscreen retrieves data from the table.Account Table.

the form consists of 1 combobox bound to the username column on account table and 1 unbound textbox named Password used for password input. also on the form is a command button titled exit database and 1 command button titled login.

Obviously the Login Button with command on_click is bound to an event in the below code initiating the process to verify the username to the password located in the Account Table. which works right now just fine with one exception

when initially getting the loginscreen, i get to select my username, however, when i type in the username it is overwriting the other fields in the account table. however if the username has is in the drop down and unique by means in which it has not been over written then the username and password works fine and continues to the switchboard. otherwise 'Else happens.



Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub Combo11_AfterUpdate()
  4. 'After selecting user name set focus to password field
  5.     Forms!LoginScreen!Password.SetFocus
  6. End Sub
  7.  
  8. Private Sub Command5_Click()
  9. 'Check to see if data is entered into the UserName combo box
  10.  
  11.     If IsNull(Forms!LoginScreen!Combo11) Or Forms!LoginScreen!Combo11 = "" Then
  12.       MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
  13.         Forms!LoginScreen!Combo11.SetFocus
  14.         Exit Sub
  15.     End If
  16.  
  17.     'Check to see if data is entered into the password box
  18.  
  19.     If IsNull(Forms!LoginScreen!Password) Or Forms!LoginScreen!Password = "" Then
  20.       MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
  21.         Forms!LoginScreen!Password.SetFocus
  22.         Exit Sub
  23.     End If
  24.  
  25.     'Check value of password in AccountTable to see if this
  26.     'matches value chosen in combo box
  27.  
  28.     If Forms!LoginScreen!Password.Value = DLookup("Password", "AccountTable", "[UserName]= '" & Forms!LoginScreen!Combo11.Value & "'") Then
  29.  
  30.         'Close logon form and open Switchboard
  31.  
  32.         DoCmd.Close acForm, "LoginScreen", acSaveNo
  33.         DoCmd.OpenForm "Switchboard"
  34.  
  35.     Else
  36.       MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
  37.             "Invalid Entry!"
  38.         Forms!LoginScreen!Password.SetFocus
  39.     End If
  40.  
  41.     'If User Enters incorrect password 3 times database will shutdown
  42.  
  43.     intLogonAttempts = intLogonAttempts + 1
  44.     If intLogonAttempts > 3 Then
  45.       MsgBox "You do not have access to this database.Please contact admin.", _
  46.                vbCritical, "Restricted Access!"
  47.         Application.Quit
  48.     End If
  49.  
  50. End Sub
Apr 17 '10 #3

cobra35y
P: 5
This is the code for the Switchboard however line 319 below is where once the password has been verified it opens the switchboard causing on Load the form closes the Splash Screen preventing further looping of the loginscreen. if the loginscreen is attempted to be bypassed then it continues to cycle and loop until closed by the switchboard being loaded. so this is where i want to enable only the authorized forms or buttons based on security level. should i add a security level table and link to the username in the account table, and code the switchboard buttons so they are available to only those specified users, or would it be best to get rid of the switchboard and have user based forms with their allowed buttons.

the office only consists of approx. 15 soldiers, however this might be ok if it would be the same 15 forever, but simply not the case the soldiers depart after 1 year of assignment to korea. so each user having individual form i cant see feasable, unless maybe a general user form for certain departments and assigning a permission level to each of the department forms. that may be an idea.

let me know what yall think, i am in the wind right now, and i know there must be something i am overlooking for a solution to my dilema. thanks for any and all help.






Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub Form_Open(Cancel As Integer)
  4. ' Minimize the database window and initialize the form.
  5.  
  6.     ' Move to the switchboard page that is marked as the default.
  7.     Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
  8.     Me.FilterOn = True
  9.  
  10. End Sub
  11.  
  12. Private Sub Form_Current()
  13. ' Update the caption and fill in the list of options.
  14.  
  15.     Me.Caption = Nz(Me![ItemText], "")
  16.     FillOptions
  17.  
  18. End Sub
  19.  
  20. Private Sub FillOptions()
  21. ' Fill in the options for this switchboard page.
  22.  
  23.     ' The number of buttons on the form.
  24.     Const conNumButtons = 8
  25.  
  26.     Dim con As Object
  27.     Dim RS As Object
  28.     Dim stSql As String
  29.     Dim intOption As Integer
  30.  
  31.     ' Set the focus to the first button on the form,
  32.     ' and then hide all of the buttons on the form
  33.     ' but the first.  You can't hide the field with the focus.
  34.     Me![Option1].SetFocus
  35.     For intOption = 2 To conNumButtons
  36.         Me("Option" & intOption).Visible = False
  37.         Me("OptionLabel" & intOption).Visible = False
  38.     Next intOption
  39.  
  40.     ' Open the table of Switchboard Items, and find
  41.     ' the first item for this Switchboard Page.
  42.     Set con = Application.CurrentProject.Connection
  43.     stSql = "SELECT * FROM [Switchboard Items]"
  44.     stSql = stSql & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" & Me![SwitchboardID]
  45.     stSql = stSql & " ORDER BY [ItemNumber];"
  46.     Set RS = CreateObject("ADODB.Recordset")
  47.     RS.Open stSql, con, 1   ' 1 = adOpenKeyset
  48.  
  49.     ' If there are no options for this Switchboard Page,
  50.     ' display a message.  Otherwise, fill the page with the items.
  51.     If (RS.EOF) Then
  52.         Me![OptionLabel1].Caption = "There are no items for this switchboard page"
  53.     Else
  54.         While (Not (RS.EOF))
  55.             Me("Option" & RS![ItemNumber]).Visible = True
  56.             Me("OptionLabel" & RS![ItemNumber]).Visible = True
  57.             Me("OptionLabel" & RS![ItemNumber]).Caption = RS![ItemText]
  58.             RS.MoveNext
  59.         Wend
  60.     End If
  61.  
  62.     ' Close the recordset and the database.
  63.     RS.Close
  64.     Set RS = Nothing
  65.     Set con = Nothing
  66.  
  67. End Sub
  68.  
  69. Private Function HandleButtonClick(intBtn As Integer)
  70. ' This function is called when a button is clicked.
  71. ' intBtn indicates which button was clicked.
  72.  
  73.     ' Constants for the commands that can be executed.
  74.     Const conCmdGotoSwitchboard = 1
  75.     Const conCmdOpenFormAdd = 2
  76.     Const conCmdOpenFormBrowse = 3
  77.     Const conCmdOpenReport = 4
  78.     Const conCmdCustomizeSwitchboard = 5
  79.     Const conCmdExitApplication = 6
  80.     Const conCmdRunMacro = 7
  81.     Const conCmdRunCode = 8
  82.     Const conCmdOpenPage = 9
  83.  
  84.     ' An error that is special cased.
  85.     Const conErrDoCmdCancelled = 2501
  86.  
  87.     Dim con As Object
  88.     Dim RS As Object
  89.     Dim stSql As String
  90.  
  91. On Error GoTo HandleButtonClick_Err
  92.  
  93.     ' Find the item in the Switchboard Items table
  94.     ' that corresponds to the button that was clicked.
  95.     Set con = Application.CurrentProject.Connection
  96.     Set RS = CreateObject("ADODB.Recordset")
  97.     stSql = "SELECT * FROM [Switchboard Items] "
  98.     stSql = stSql & "WHERE [SwitchboardID]=" & Me![SwitchboardID] & " AND [ItemNumber]=" & intBtn
  99.     RS.Open stSql, con, 1    ' 1 = adOpenKeyset
  100.  
  101.     ' If no item matches, report the error and exit the function.
  102.     If (RS.EOF) Then
  103.         MsgBox "There was an error reading the Switchboard Items table."
  104.         RS.Close
  105.         Set RS = Nothing
  106.         Set con = Nothing
  107.         Exit Function
  108.     End If
  109.  
  110.     Select Case RS![Command]
  111.  
  112.         ' Go to another switchboard.
  113.         Case conCmdGotoSwitchboard
  114.             Me.Filter = "[ItemNumber] = 0 AND [SwitchboardID]=" & RS![Argument]
  115.  
  116.         ' Open a form in Add mode.
  117.         Case conCmdOpenFormAdd
  118.             DoCmd.OpenForm RS![Argument], , , , acAdd
  119.  
  120.         ' Open a form.
  121.         Case conCmdOpenFormBrowse
  122.             DoCmd.OpenForm RS![Argument]
  123.  
  124.         ' Open a report.
  125.         Case conCmdOpenReport
  126.             DoCmd.OpenReport RS![Argument], acPreview
  127.  
  128.         ' Customize the Switchboard.
  129.         Case conCmdCustomizeSwitchboard
  130.             ' Handle the case where the Switchboard Manager
  131.             ' is not installed (e.g. Minimal Install).
  132.             On Error Resume Next
  133.             Application.Run "ACWZMAIN.sbm_Entry"
  134.             If (Err <> 0) Then MsgBox "Command not available."
  135.             On Error GoTo 0
  136.             ' Update the form.
  137.             Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
  138.             Me.Caption = Nz(Me![ItemText], "")
  139.             FillOptions
  140.  
  141.         ' Exit the application.
  142.         Case conCmdExitApplication
  143.             CloseCurrentDatabase
  144.  
  145.         ' Run a macro.
  146.         Case conCmdRunMacro
  147.             DoCmd.RunMacro RS![Argument]
  148.  
  149.         ' Run code.
  150.         Case conCmdRunCode
  151.             Application.Run RS![Argument]
  152.  
  153.         ' Open a Data Access Page
  154.         Case conCmdOpenPage
  155.             DoCmd.OpenDataAccessPage RS![Argument]
  156.  
  157.         ' Any other command is unrecognized.
  158.         Case Else
  159.             MsgBox "Unknown option."
  160.  
  161.     End Select
  162.  
  163.     ' Close the recordset and the database.
  164.     RS.Close
  165.  
  166. HandleButtonClick_Exit:
  167. On Error Resume Next
  168.     Set RS = Nothing
  169.     Set con = Nothing
  170.     Exit Function
  171.  
  172. HandleButtonClick_Err:
  173.     ' If the action was cancelled by the user for
  174.     ' some reason, don't display an error message.
  175.     ' Instead, resume on the next line.
  176.     If (Err = conErrDoCmdCancelled) Then
  177.         Resume Next
  178.     Else
  179.         MsgBox "There was an error executing the command.", vbCritical
  180.         Resume HandleButtonClick_Exit
  181.     End If
  182.  
  183. End Function
  184.  
  185. Private Sub Command35_Click()
  186. On Error GoTo Err_Command35_Click
  187.  
  188.     Dim stDocName As String
  189.     Dim stLinkCriteria As String
  190.  
  191.     stDocName = "SPECIAL QUERIES"
  192.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  193.  
  194. Exit_Command35_Click:
  195.     Exit Sub
  196.  
  197. Err_Command35_Click:
  198.     MsgBox Err.Description
  199.     Resume Exit_Command35_Click
  200.  
  201. End Sub
  202. Private Sub Command36_Click()
  203. On Error GoTo Err_Command36_Click
  204.  
  205.     Dim stDocName As String
  206.  
  207.     stDocName = "PT Avg"
  208.     DoCmd.OpenQuery stDocName, acNormal, acEdit
  209.  
  210. Exit_Command36_Click:
  211.     Exit Sub
  212.  
  213. Err_Command36_Click:
  214.     MsgBox Err.Description
  215.     Resume Exit_Command36_Click
  216.  
  217. End Sub
  218. Private Sub Command37_Click()
  219. On Error GoTo Err_Command37_Click
  220.  
  221.     Dim stDocName As String
  222.  
  223.     stDocName = "B CO APFT FAILURES"
  224.     DoCmd.OpenQuery stDocName, acNormal, acEdit
  225.  
  226. Exit_Command37_Click:
  227.     Exit Sub
  228.  
  229. Err_Command37_Click:
  230.     MsgBox Err.Description
  231.     Resume Exit_Command37_Click
  232.  
  233. End Sub
  234. Private Sub Command38_Click()
  235. On Error GoTo Err_Command38_Click
  236.  
  237.     Dim stDocName As String
  238.  
  239.     stDocName = "New soldier Query"
  240.     DoCmd.OpenQuery stDocName, acNormal, acEdit
  241.  
  242. Exit_Command38_Click:
  243.     Exit Sub
  244.  
  245. Err_Command38_Click:
  246.     MsgBox Err.Description
  247.     Resume Exit_Command38_Click
  248.  
  249. End Sub
  250. Private Sub Mass_Delete_Click()
  251. On Error GoTo Err_Mass_Delete_Click
  252.  
  253.     Dim stDocName As String
  254.  
  255.     stDocName = "Mass Delete Complete Record"
  256.     DoCmd.RunMacro stDocName
  257.  
  258. Exit_Mass_Delete_Click:
  259.     Exit Sub
  260.  
  261. Err_Mass_Delete_Click:
  262.     MsgBox Err.Description
  263.     Resume Exit_Mass_Delete_Click
  264.  
  265. End Sub
  266. Private Sub Command49_Click()
  267. On Error GoTo Err_Command49_Click
  268.  
  269.     Dim stDocName As String
  270.  
  271.     stDocName = "OPS New soldier Query"
  272.     DoCmd.OpenQuery stDocName, acNormal, acEdit
  273.  
  274. Exit_Command49_Click:
  275.     Exit Sub
  276.  
  277. Err_Command49_Click:
  278.     MsgBox Err.Description
  279.     Resume Exit_Command49_Click
  280.  
  281. End Sub
  282. Private Sub Command50_Click()
  283. On Error GoTo Err_Command50_Click
  284.  
  285.     Dim stDocName As String
  286.  
  287.     stDocName = "TRAINING APFT Scores"
  288.     DoCmd.OpenQuery stDocName, acNormal, acEdit
  289.  
  290. Exit_Command50_Click:
  291.     Exit Sub
  292.  
  293. Err_Command50_Click:
  294.     MsgBox Err.Description
  295.     Resume Exit_Command50_Click
  296.  
  297. End Sub
  298. Private Sub OPS_MENU_Click()
  299. On Error GoTo Err_OPS_MENU_Click
  300.  
  301.     Dim stDocName As String
  302.     Dim stLinkCriteria As String
  303.  
  304.     stDocName = "OPERATIONS MENU"
  305.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  306.  
  307. Exit_OPS_MENU_Click:
  308.     Exit Sub
  309.  
  310. Err_OPS_MENU_Click:
  311.     MsgBox Err.Description
  312.     Resume Exit_OPS_MENU_Click
  313.  
  314. End Sub
  315. '------------------------------------------------------------
  316. ' Form_Load
  317. '
  318. '------------------------------------------------------------
  319. Private Sub Form_Load()
  320. On Error GoTo Form_Load_Err
  321.  
  322.     DoCmd.Close acForm, "radioactive sergeant productions"
  323.  
  324.  
  325. Form_Load_Exit:
  326.     Exit Sub
  327.  
  328. Form_Load_Err:
  329.     MsgBox Error$
  330.     Resume Form_Load_Exit
  331.  
  332. End Sub
Apr 17 '10 #4

cobra35y
P: 5
too much code and too many questions to truly post. lol, you really have to see the database in its current state to understand what i am going for.
Apr 17 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.