New User form...

When creating a new database you sometimes want to implement some kind of Log On to track who's logged 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 every time 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.
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 if you are not planning to use this form in conjunction with this code.
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.)
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.
Go to your Main Menu (or Switchboard) and in Design View open the Form Properties window.  Go to the  
Tag Property and type...

Continue

Save and close your Main Menu (or Switchboard) and return back to frmNewUser.
...problem solved!
While the code above works fine in Access 2010, I know I use it, 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 (or Switchboard) to do that.  IsDeveloper() function found  
here.)
The difficult I do immediately, the impossible takes a little bit longer.
Forms
Tips (Main)
Home
Let your Client customize the form labels
Linking Subforms to an Unbound Control(s) on the Main (Parent) Form
Customize your Main Menu or Switchboard
Auto-Add new child records when adding a new record on the Main (Parent) Form
Simple Bar Chart using Shapes
Simulate a Drop Down box (not a Combo Box) as seen on Web Pages
Implement a Log-On form to control what the User sees
Make Controls resize with a Form
Switch Windows
Assign *Permissions* based on Users Levels
Add *Favorites* to a Continuous Form
Custom Record Selectors
Searching Records
Drill Down (for Continuous Forms)
Highlight Required Controls (End-User Defined)
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
 
     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"
     CurrentDb.Execute strSQL, dbFailOnError
 
     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
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"
Click to send feedback...
This site uses cookies to collect data on usage. By continuing to browse this site you consent to this policy. Find out more here.