title: VBA and Access 2007 data validation, Login trouble
body: <p>Please bear with me I am a complete noob at VBA, I had no intention of writing code but I ended up doing so anyway because I couldn't see how I'd achieve what I was looking for without it. I have been searching on the internet for 3 days 10 hours a day without much luck. I have an Access database that I'd like to secure. Just let me explain what I am trying to achieve first:</p>
  
  <p>1) I have a combobox in a form that looks up values in a table, the item code of an item. This is basically an inventory thing. I have a query that has a calculated field which returns the remaining stock of each item. I have another query which takes the item code as the parameter and returns the remaining stock for that specific item code. </p>
  
  <p>What I want to do is check if the remaining stock is 0, if it is, then on selecting this item code from the combobox list, have a msgbox display a message saying that the item is not in stock and so it cannot be issued, and reset the combobox (i.e. as if no selection was made)</p>
  
  <p>Here's my code; the combobox's afterUpdate event:</p>
  
  <pre><code>Private Sub Item_Code_AfterUpdate()
  
  Dim dbMyDatabase As DAO.Database
  
  Dim rsMyRecords As DAO.Recordset
  
  Dim strQuery As String
  
  strQuery = "SELECT [Store_Items].[Item Code], Store_Items.[Opening Stock], IIf((Nz([Opening Stock],0)+Nz([Quantity Purchased],0)-Nz([Quantity Issued],0)&lt;0),0,(Nz([Opening Stock],0)+Nz([Quantity Purchased],0)-Nz([Quantity Issued],0))) AS [Remaining Stock] FROM (Purchases RIGHT JOIN Store_Items ON Purchases.[Item Code] = Store_Items.[Item Code]) LEFT JOIN Issuances ON Store_Items.[Item Code] = Issuances.[Item Code] WHERE (((Store_Items.[Item Code])=[Forms]![Issuances]![Item Code]));"
  
  Set dbMyDatabase = CurrentDb
  
  Set rsMyRecords = dbMyDatabase.OpenRecordset(strQuery)
  
  Dim Msg, Style, Title, Response
  
  Msg = "This Item is out of Stock! You Cannot Issue this item!"
  Style = vbOK
  Title = "Warning!"
  
  If rsMyRecords![Item Code] &lt;= 0 Then
  Response = MsgBox(Msg, Style, Title)
  If Response = vbOK Then Me![Item Code].Requery
  
  End Sub 
  </code></pre>
  
  <p>2) Ive made 2 versions of a navigation panel. One of them has access to all of the forms, and the other one has limited access. This is to be decided by the login type. Now, the only one that works is the first one, I don't understand why the ElseIfs aren't working. When I try logging in with other types, on clicking the login button, nothing happens.</p>
  
  <p>I also want to hide the navigation pane and access default menus that allow database design editing and viewing of tables and forms in design views depending on the login type but I have no idea how to achieve this.</p>
  
  <pre><code>Private Sub LoginBUtton_Click()
  
  'Check to see if data is entered into the UserName combo box
  
      If IsNull(Me.CBOLogin) Or Me.CBOLogin = "" Then
        MsgBox "You must select a user type.", vbOKOnly, "No User name"
          Me.CBOLogin.SetFocus
          Exit Sub
      End If
  
      'Check to see if data is entered into the password box
  
      If IsNull(Me.TextPass) Or Me.TextPass = "" Then
        MsgBox "No Password Entered, Enter a password.", vbOKOnly, "No Password"
          Me.TextPass.SetFocus
          Exit Sub
      End If
  
      'Check value of password in Users to see if this
      'matches value chosen in combo box
  
      If Me.TextPass.Value = DLookup("Password", "Users", _
              "[UserID]=" &amp; Me.CBOLogin.Value) Then
  
  
  
      ElseIf (Me.CBOLogin.Value = "Developer") Then
      DoCmd.Close acForm, "LoginForm", acSaveNo
      DoCmd.OpenForm "FullAccessNav"
  
  
      ElseIf (Me.CBOLogin.Value = "Office") Then
      DoCmd.Close acForm, "LoginForm", acSaveNo
      DoCmd.OpenForm "LimitedAccessNav"
  
  
      ElseIf (Me.CBOLogin.Value = "Store") Then
      DoCmd.Close acForm, "loginForm", acSaveNo
      DoCmd.OpenForm "LimitedAccessNav"
  
      Else
              MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
              "Invalid Entry!"
              Me.TextPass.SetFocus
  
      End If
  
      'If User Enters incorrect password 3 times database will shutdown
  
      intLogonAttempts = intLogonAttempts + 1
  
      If intLogonAttempts &gt; 3 Then
        MsgBox "You do not have access to this database.Please contact admin.", _
                 vbCritical, "Restricted Access!"
          Application.Quit
      End If
  
  End Sub
  </code></pre>
  
  <p>Help Please? :S</p>