Here is an example from one of my apps. Maybe you can see how I done it. This example will allow only 3 attemps then it will close the app. The user password is stored in a table, the Dlookup returns this password for comparison. Also notice that I also have set strPW2 as my own password that will work reguardless of what the user's password is. It's my way of opening the database without having to know the user's password.
txtEPW is the password entry textbox in the password form.
Private Sub txtEPW_AfterUpdate()
Dim strMsg As String
Dim strMsg2 As String
Dim strTitle As String
Dim strTitle2 As String
Dim strPW As String
Dim strPW2 As String
Dim strEPW As Variant
Dim intCnt As Integer
strPW = DLookup("PW", "tblPW"

strPW2 = "mybackdoor"
intCnt = Me![txtCount]
strMsg = "Incorrect Password" & vbNewLine & "Please try again."
strMsg2 = "That was the Third Attempt!" & vbNewLine & _
"The Program Will Close!"
strTitle = "Invalid Password"
strTitle2 = "Third Attempt"
strEPW = Me![txtEPW]
Select Case strEPW
Case Null
If intCnt = 3 Then
MsgBox strMsg2, vbCritical + vbOKOnly, strTitle2
DoCmd.Quit
End If
MsgBox strMsg, vbCritical + vbOKOnly, strTitle
Me![cmdCancel].SetFocus
Me![txtEPW].SetFocus
Me![txtCount] = intCnt
Case strPW
DoCmd.OpenForm "frmMain"
DoCmd.Close acForm, Me.Name
Case strPW2
DoCmd.OpenForm "frmMain"
DoCmd.Close acForm, Me.Name
Case Else
If intCnt = 3 Then
MsgBox strMsg2, vbCritical + vbOKOnly, strTitle2
DoCmd.Quit
End If
MsgBox strMsg, vbCritical + vbOKOnly, strTitle
Me![txtEPW] = Null
Me![cmdCancel].SetFocus
Me![txtEPW].SetFocus
intCnt = intCnt + 1
Me![txtCount] = intCnt
End Select
End Sub
HTH
RDH