The difficult I do immediately, the impossible takes a little bit longer.

When creating a new database you sometimes want to implement some kind of Log On to track who's loggged on, give permissions to certain areas and/or create an audit trail of who's changing what record (Form level logging).  But you don't want your Users to have to Log On everytime they open your database.  Here's a method that works silently in the background when attached to your Main Menu/Switchboard.  To see how to *attach* to your Main Menu/Switchboard click here...

New User form...

Forms
Make Controls resize with a form  (Access 2007 and higher)
Switch Windows  (Access 2007 and higher)

Private Sub cmdContinue_Click()
On Error GoTo ErrHandler

     Dim strSQL As String

     If Nz(Me.txtFirstName, "") = "" Then
      MsgBox ("First Name cannot be empty.")
      DoCmd.GoToControl "txtFirstName"
     Exit Sub
     End If

     If Nz(Me.txtLastName, "") = "" Then
      MsgBox ("Last Name cannot be empty.")
      DoCmd.GoToControl "txtLastName"
     Exit Sub
     End If

     If Nz(Me.txteMail, "") = "" Then
      MsgBox ("Email cannot be empty.")
      DoCmd.GoToControl "txteMail"
     Exit Sub
     End If

     DoCmd.SetWarnings False
     strSQL = "INSERT INTO tblUsers ( uNetworkID, uFirstName, uLastName, ueMail, uLastLogon, uLogonCount, uSecurityID, uActive )" & _
     " SELECT Environ("UserName"), '" & Me.txtFirstName & "', '" & Me.txtLastName & "', '" & Me.txteMail & "', Now(), 1, 9, True"
     DoCmd.RunSQL strSQL
     Forms(" frmMainMenu ").Tag = "Continue"   'frmMainMenu change to the name of your Main Menu/Switchboard/Navigation Form
     DoCmd.Close acForm, Me.Name

 Complete:
 Exit Sub

  ErrHandler:
  MsgBox ("Error creating user profile: " & Err.Description)

End Sub


Private Sub cmdExit_Click()
     Application.Quit
End Sub

Step 1...

In your Table where you keep your Users information add whichever fields are missing to your table from the table below. (*uDepartmentID* is *optional* as we will not be using it in this example. You can also remove uLastLogon, uLogonCount, uSpecialPermissions and uDeletePermissions but if you are planning to use this form in conjunction with this code , you will need to adjust accordingly.) You can name your fields whatever you like, however, make sure you follow that field name change throughout the rest of the code on this page and the link posted.

Step 2...

If you don't already have one, set up tblSecurity, as shown below. Again, if you already have one under a different name you will need to change the code accordingly. (Used in this example is 2 (two) for Read/Write, 9 (nine) for Read Only and 13 (thirteen) for ADMIN.)

Step 3...

Create your form and name it *frmNewUser*.  Use *tblUsers* as the RecordSource but only set the Control Source for field *txtSecurityID*.  Place the balance of the fields, showing below, with no Control Source (Unbound) on your form.

Place the two buttons, as show below, on your Form, naming them *cmdContinue* and *cmdExit*.  (Cancel out of any invitation to create the code, we'll be putting that in shortly.)  Disable the *Close* *x* in the upper right hand corner in Form Properties.  Also disable the Min/Max option for the Form and set it as a *Pop Up*.  In effect, you do not want them to *blow* past your Form.

Step 5...
Step 4...

Go to your Main Menu/Switchboard and in Design View open the Form Properties window.  Go to the Tag Property and type...

Continue

Save and close your Main Menu/Switchboard and return back to frmNewUser.

strSQL = "INSERT INTO tblUsers ( uNetworkID, uFirstName, uLastName, ueMail, uLastLogon, uLogonCount, uSecurityID, uActive )" & _
     " Values ('" & Environ("UserName") & "' , '" & Me.txtFirstName & "', '" & Me.txtLastName & "', '" & Me.txteMail & "', Now(), 1, 9, True"

...problem solved!
While the code above works fine in Access 2010, I know I use it, however, it throws up an error in Access 2007:  Compile Error, Expected: End of Statement  and highlights UserName.  Quick thinking Nigel posted...

Go to Design View of the frmNewUser.  Copy/paste the below under...

Option Compare Database
Option Explicit

...in the Forms VB Editor.  Make sure to change the name of the Form shown on the blue line *frmMainMenu* to the name of your Main Menu/Switchboard/Navigation Form.

Save and then test.  Be careful!   This Form will throw you out the database if you select *Exit* and will not let you do anything else until you fill in the *required* information.  You can test by opening the database holding down the Shift Key and then opening the Form.  That way you can hit Design Mode of the Form in the Navigation Pane without selecting *Exit*, so you don't get thrown out.  (Just another reason to hide the Navigation Pane and set the *AllowByPassKey* to False when distributing your database!  You can integrate the IsDeveloper() function with your Main Menu/Switchboard to do that.  IsDeveloper() function found here.)

aaaaaaaaaaaaiii