New User form

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"