[VBA] Access Problem - Login form load.

[iXIII]

Hey all!

Basically, I created a login and everything works fine. I am just trying to figure the way around coding the feature to enable the form to pop-up upon opening the database and where to put this code.

I then want the code to disable the access of tables (such as the tables where the username and password are stored) as there would be simply no point in creating a login form if people can just access the things anyway.

But the users have ranks mods and admins, the admins have more commands on the login form than the mods which appear after successfully logging in.

The admins have an admin button which I want them to be able to click to enable the backface of the database (so they are able to edit tables and what not at will) but obviously if the mods were to log in they would only be able to use the buttons on their form to open forms etc.

So simply I would appreciate any help that can help solve these three problems;

1] I want to make sure my login form opens upon loading the database (Please note I want to code this in).
2] I want to disable or minimise the back grid of things, consisting of tables and forms so the user has to log in to use them (once more, coding preferably).
3] And finally, I would like the admin to be able to enable the back face of the database (which was disabled upon opening the database) via the use of a command button (so either it maximises or is enabled).

I've not the faintest how to do this and would appreciate any help.

It's worth noting that I haven't a clue where to put this code either, as it will have to have a higher place than the forms and what not.

Please, could somebody help <3

Many thanks
XIII

P.s. I've been at this for a few days now. I've browsed the web to no success

[Imdabaum]

You shouldn't need any code if you wnat it to open on startup.

For Access 2003
http://www.techonthenet.com/access/forms/startup.php


For Access 2007 and later
Office Button--Access Options-- Current Database| Display Form: choose existing form to start.

[Imdabaum]

Sorry for the double post, but I'll take advantage of the reserved spot to explain the hiding part.

Create a module [name whatever you want]
Then insert the following VBA code into the module.

Option Compare Database
Option Explicit
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Private Declare Function apiGetComputerName Lib "kernel32" Alias _
"GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Public Function DisableSpecialKeys() As Boolean
On Error GoTo Err_DisableSpecialKeys
'This function restricts users from using special keys that open design features.
'Start up properties will always be executed.

Dim db As Database
Dim Prop As Property
Const conPropNotFound = 3270
Set db = CurrentDb()
db.Properties("AllowSpecialKeys") = False
Set db = Nothing
DisableSpecialKeys = True
Exit_DisableSpecialKeys:
Exit Function
Err_DisableSpecialKeys:
If Err = conPropNotFound Then
'If the property doesn't exist, create it
Set Prop = db.CreateProperty("AllowSpecialKeys", dbBoolean, True)
db.Properties.Append Prop
Resume Next
Else
MsgBox "Disable did not Work!!"
DisableSpecialKeys = False
Resume Exit_DisableSpecialKeys
End If
End Function
Public Function EnableSpecialKeys() As Boolean
On Error GoTo Err_DisableSpecialKeys
'This function enables special keys that open design features.
'Start up properties will always be executed.

Dim db As Database
Dim Prop As Property
Const conPropNotFound = 3270
Set db = CurrentDb()
db.Properties("AllowSpecialKeys") = True
Set db = Nothing
EnableSpecialKeys = True
Exit_DisableSpecialKeys:
Exit Function
Err_DisableSpecialKeys:
If Err = conPropNotFound Then
'If the property doesn't exist, create it
Set Prop = db.CreateProperty("AllowSpecialKeys", dbBoolean, True)
db.Properties.Append Prop
Resume Next
Else
MsgBox "Ensable did not Work!!"
EnableSpecialKeys = False
Resume Exit_DisableSpecialKeys
End If
End Function
Function p_DisableShiftBypass()
On Error GoTo errDisableByPass
'This function restricts users from modifying the database
'Start up properties will always be executed.
Dim db As Database
Dim Prop As DAO.Property
Const conPropNotFound = 3270

Set db = CurrentDb()
'Disable the shift key on startup.
db.Properties("AllowByPassKey") = False
DisableSpecialKeys
Exit Function

errDisableByPass:
'The first part of this error routine creates the "AllowByPassKey
'property if it does not exist.
If Err = conPropNotFound Then
Set Prop = db.CreateProperty("AllowByPassKey", dbBoolean, False)
db.Properties.Append Prop
Resume Next
Else
MsgBox "Function 'DisableShiftBypass' did not complete successfully."
Exit Function
End If
End Function
Function p_EnableShiftByPass()
'This function enables the SHIFT key at startup. This action causes
'the Autoexec macro and the Startup properties to be bypassed
'if the user holds down the SHIFT key when the user opens the database.
On Error GoTo errEnableShift
Dim db As DAO.Database
Dim Prop As DAO.Property
Const conPropNotFound = 3270
Set db = CurrentDb()
'This next line of code disables the SHIFT key on startup.
db.Properties("AllowByPassKey") = True
EnableSpecialKeys
'function successful
Exit Function
errEnableShift:
'The first part of this error routine creates the "AllowByPassKey
'property if it does not exist.
If Err = conPropNotFound Then
Set Prop = db.CreateProperty("AllowByPassKey", _
dbBoolean, True)
db.Properties.Append Prop
Resume Next
Else
MsgBox "Function 'p_DisableShiftByPass' did not complete successfully."
Exit Function
End If
End Function
Public Function LockDown()
' Runs if the database extension is .accde instead of .accdb
' Disables shift bypass, and special keys that would otherwise open the navigation window.
Dim sDbExt As String
sDbExt = Right(CurrentProject.Name, 6)
If sDbExt = ".accde" And fOSUserName <> "bbaum" Then
p_DisableShiftBypass
Else
p_EnableShiftByPass
End If
End Function

'******************** Code Start **************************
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function
'******************** Code Start **************************
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Function fOSMachineName() As String
'Returns the computername
Dim lngLen As Long, lngX As Long
Dim strCompName As String
lngLen = 16
strCompName = String$(lngLen, 0)
lngX = apiGetComputerName(strCompName, lngLen)
If lngX <> 0 Then
fOSMachineName = Left$(strCompName, lngLen)
Else
fOSMachineName = ""
End If
End Function


Usually I call the LockDown sub on AutoExec macro as that will fire before anything else happens in the order of operations in Access. The fOSUserName function can be used to allow certain people the ability to view the tables in case you ever need to modify the tables and is more secure than using the Environ variables.