Help with For...Next Loop and Other Questions

[JodiPhillips]

Hello All,

I'm trying to limit the number of attempts a user has to log into an MS Access 2003 database, but am having very little success.

My current code for log in is as follows (and thanks to all previous posters whose ideas and code I have taken from these forums to get me this far!!)

UserId comes in from a combo box also on the same form, the focus is then shifted to the password textbox for password entry and then the user clicks on the command button below.

Private Sub cmdLogOn_Click()
Dim sPswd As String
        
 
        If IsNull(Me!cboUserID) = True Or IsNull(Me!txtPass) = True Then
            MsgBox "please enter a valid userid and password"
            Exit Sub
        End If
 
        sPswd = Nz(DLookup("txtpword", "tblLogIn", "txtUserID='" & Me!cboUserID & " ' "), "")
        Debug.Print sPswd
        
        If Me!txtPass <> sPswd Then
            MsgBox "Invalid UserID/Password", vbOKOnly, "Try Again"
            Exit Sub
        End If
        
       
DoCmd.Close acForm, "frmLogOn", acSaveNo
DoCmd.OpenForm ("Start Form CL&D")
 
End Sub

I'm not sure where to put the For statement. I have tried at various points but end up with a lost Next statement, or the users have no limits to the number of attempts.

My tentative For code is as follows:

Dim intAttempts as integer

For intAttempts = 0 to 3 Step 1
Next intAttempts

If intAttempts > 3 Then
MsgBox "You do not have authority to access this database. Contact your support team",vbcritical,"Restricted Access!"
Application.Quit
End If

Can anyone point me in the right direction on where the various For...Nexts should go, or if I am missing something from the code or using the wrong type of loop? Any help with this will be much appreciated. I've read through the help files within access on the loops and its still not clear to me.

Also, I want to AllowBypasskey = False in a procedure(? if that is the correct thing to call it) so that users cannot just Shift-Click into the database - I have a splash set up that is in pop up mode on start up and I want users to move through pages and forms in a specific direction. To disable the bypass do I have to do a create procedure before I can change it? And do I do this as a new module?

Another thing I would like to do is if the database is opened in anyway by anyone or attempt to open I would like to send an email to myself as soon as it is accessed. I can't set a password on the database for a number of reasons, but would like an audit trail of when the database is opened not just logged into. Is this doable? If so, could anyone point me in the how to direction so that I can research it. I don't know where to start looking for info on this one.

Last question - yes I know I'm being greedy, but this should be enough to keep me to browsing for the next six months I promise *wink*,

I want to run a query off the value in txtpass (as above) but if I put [Forms!].[frmLogOn!].[txtpass].[Afterupdate] into the criteria line for the query it gives me a parameter box to complete. Is there a way to specify the value thats entered at login (during the execution of the code above) directly into the query without the user having to reenter their userid? The query basically just runs off and grabs records from a table for their team members.

Many thanks =)

Jodi

[nico5038]

You don't need various for next loops.

Just define before all sub's in the form's code your login counter like:

dim intLogins as Integer

' Now in the login code use

sub Login()

intLogins = intLogins + 1

IF intLogins > 3 then 
    msgbox "No more logins"
    docmd.quit
endif

' herethe other code for testing

end sub

Getting the idea ?

Nic;o)

[JodiPhillips]

Nic,

That did the trick, just copied the sub that you provided and called it from within the logon sub.

Many many thanks!

Jodi

[nico5038]

Glad I could help, success with your application !

Nic;o)