Automation for Assigning in MS access

[john_vin]

I want to make MS access automated to assign files to quality checkers whoever active in the data base. Files should not be assigned to any checkers who is inactive. I uploaded the sample data base where makers enter the details and I want the the checkers name should be selected automatically and this is based on the login status of the checker, so I want a login form where all makers have a same password and other same password for QC ers. The checkers have should not have access to QC working form and same for QC ers should not have any access to checker form. And there should be a admin login where the automatically assigned QC ers name can be changed if required. So my final goal is to remove the assigning form from this data base.

[john_vin]

Anyone can help me??

[orange]

Checkers--active and inactive
Admin
QC ers active/inactive?
Makers
???

all makers have a same password and other same password for QC ers.

Suggest you avoid lookups at table field level.

[john_vin]

Below is my requirement.


I want to make MS access automated to assign files to quality QC ers whoever active in the data base. Files should not be assigned to any Qc ers who is inactive. I want the the QC ers name should be selected automatically and this is based on the login status of the QC ers, so I want a login form where all makers have a same password and other same password for QC ers. The QC ers have should not have access to Maker working form and same for Makers should not have any access to QCer form. And there should be a admin login where the automatically assigned QC ers name can be changed if required. So my final goal is to remove the assigning form from this data base.


I attached a sample data base. It will be really helpful if someone can do this.


Thank you!!

[Micron]

I will give you a clue as to why I think people are not falling all over this one, and I don't say this to be rude or condescending. We help people solve issues and sometimes that takes quite a bit of our free time. However, they are mainly the ones doing the work - we pitch in a guide them, even do some coding. Many of us here would not do a student's homework, nor would they simply take your database and work all your issues out for free and simply hand it back to you. While you might think you've explained it well enough, believe me when I say you would not think so if you knew what could be involved and how many attempts it could take for the final result to resemble what you really mean. I might be wrong about some or even all of that, and will take my lumps if any of the long time helpers here feel like putting me in my place. On the other hand, maybe it needed to be said so that you understand the response rate.

[john_vin]

Option Compare Database


Private Sub btnOK_Click()

Dim UserLevel As Integer
Dim UserName As String
Dim frm As Form
Dim cmb As ComboBox
Dim i As Integer


If IsNull(Me.txtUserLogin) Then
MsgBox "Please enter your user name", vbInformation, "Username required"
Me.txtUserLogin.SetFocus
ElseIf IsNull(Me.txtPassword) Then
MsgBox "Please enter your password", vbInformation, "Password required"
Me.txtPassword.SetFocus
Else
If (IsNull(DLookup("UserName", "tblUsers", "UserName ='" & Me.txtUserLogin.Value & "'"))) Then
MsgBox "Incorrect user name"
ElseIf (DLookup("Password", "tblSecurityLevel", "SecurityID=" & DLookup("SecurityID", "tblUsers", "UserName='" & Me.txtUserLogin.Value & "'")) <> Me.txtPassword.Value) Then
MsgBox "Incorrect user name or password"
Else
UserLevel = DLookup("SecurityID", "tblSecurityLevel", "Password='" & Me.txtPassword.Value & "'")
UserName = Me.txtUserLogin
DoCmd.Close
If UserLevel = 2 Then ' QC level
DisableSetProperties
DoCmd.OpenForm "QC_Working_Form"
Set frm = Forms![QC_Working_Form]
Set cmb = frm![Checker_Name]
For i = 0 To cmb.ListCount
If cmb.Column(1, i) = UserName Then cmb.Value = i + 1
Next i
ElseIf UserLevel = 3 Then ' Maker level
DisableSetProperties
DoCmd.OpenForm "Maker_Working_Form"
Set frm = Forms![Maker_Working_Form]
Set cmb = frm![Maker Name]
For i = 0 To cmb.ListCount
If cmb.Column(1, i) = UserName Then cmb.Value = i + 1
Next i
ElseIf UserLevel = 1 Then ' Admin level
EnableSetProperties
DoCmd.OpenForm "QC_Assigning_Form"
End If
End If
End If


Set frm = Nothing
Set cmb = Nothing


End Sub


Public Function SetProperties(strPropName As String, varPropType As Variant, varPropValue As Variant) As Integer
On Error GoTo Err_SetProperties


Dim db As Database, prop As Property


Set db = CurrentDb
db.Properties(strPropName) = varPropValue
SetProperties = True


Exit_SetProperties:
Set db = Nothing
Exit Function


Err_SetProperties:
If Err = 3270 Then
Set prop = db.CreateProperty(strPropName, varPropType, varPropValue)
db.Properties.Append prop
Resume Next
Else
SetProperties = False
MsgBox "Runtime error # " & Err.Number & vbclrf & vbclrf & Err.Description
Resume Exit_SetProperties
End If


End Function


Public Function EnableBypassKey()
On Error GoTo ThisError
Dim strInput As String
Dim User As String
User = Environ("USERNAME")


If User = "admin" Then
strInput = InputBox("Please enter the password to enable the bypass key:", "Enter Password")

If strInput = "admin" Then
SetProperties "AllowBypassKey", dbBoolean, True
MsgBox "The bypass key has been enabled. Users can now bypass the startup options using the Shift key.", vbInformation, "Bypass Key Enabled"
Else
MsgBox "Invalid password. The bypass key has not been enabled.", vbInformation, "Invalid Password"
End If

Else
MsgBox "You do not have the permissions to perform this operation.", vbCritical
End If


ThisError:
Mgsbox Err.descripiton
Exit Function


End Function


Public Function DisableBypassKey()
On Error GoTo ThisError
Dim strInput As String
Dim User As String
User = Environ("USERNAME")


If User = "admin" Then
strInput = InputBox("Please enter the password to disable the bypass key:", "Enter Password")

If strInput = "admin" Then
SetProperties "AllowBypassKey", dbBoolean, False
MsgBox "The bypass key has been disabled. Users can no longer bypass the startup options using the Shift key.", vbInformation, "Bypass Key Disabled"
Else
MsgBox "Invalid password. The bypass key has not been disabled.", vbInformation, "Invalid Password"
End If

Else
MsgBox "You do not have the permissions to perform this operation.", vbCritical
End If


ThisError:
Mgsbox Err.descripiton
Exit Function


End Function


Public Function EnableSetProperties()
On Error GoTo ThisError
DoCmd.ShowToolbar "Ribbon", acToolbarYes
SetProperties "StartUpShowDBWindow", dbBoolean, True ' Display database window
SetProperties "StartUpShowStatusBar", dbBoolean, True ' Display status bar
SetProperties "AllowFullMenus", dbBoolean, True ' Enable full menus
SetProperties "AllowSpecialKeys", dbBoolean, True ' F11, Alt+F11 etc.
SetProperties "AllowBypassKey", dbBoolean, True ' Shift Key Override on loading
SetProperties "AllowShortcutMenus", dbBoolean, True ' Access Shortcut menus. May be too severe.
SetProperties "AllowToolbarChanges", dbBoolean, True ' Allow changes
SetProperties "AllowBreakIntoCode", dbBoolean, True ' Code access
AllowDbWindow True
Exit Function


ThisError:
MsgBox Err.descripiton
Exit Function


End Function


Public Function DisableSetProperties()
On Error GoTo ThisError
DoCmd.ShowToolbar "Ribbon", acToolbarNo
SetProperties "StartUpShowDBWindow", dbBoolean, False ' Display database window
SetProperties "StartUpShowStatusBar", dbBoolean, False ' Display status bar
SetProperties "AllowFullMenus", dbBoolean, False ' Enable full menus
SetProperties "AllowSpecialKeys", dbBoolean, False ' F11, Alt+F11 etc.
SetProperties "AllowBypassKey", dbBoolean, False ' Shift Key Override on loading
SetProperties "AllowShortcutMenus", dbBoolean, False ' Access Shortcut menus. May be too severe.
SetProperties "AllowToolbarChanges", dbBoolean, False ' Allow changes
SetProperties "AllowBreakIntoCode", dbBoolean, False ' Code access
AllowDbWindow False
Exit Function


ThisError:
MsgBox Err.descripiton
Exit Function


End Function


Public Function AllowDbWindow(bHide As Boolean) As Boolean
On Error GoTo HideDbWindow_Error


Dim bResult As Boolean

If bHide = False Then
DoCmd.NavigateTo "acNavigationCategoryObjectType"
DoCmd.RunCommand acCmdWindowHide
Else
DoCmd.SelectObject acTable, , True
End If


bResult = True


HideDbWindow = bResult


HideDbWindow_Exit:
On Error GoTo 0
Exit Function


HideDbWindow_Error:


MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure HideDbWindow of Module basFormManagement"
HideDbWindow = False


Resume HideDbWindow_Exit


End Function

[orange]

John,

What exactly is post #6? I don't see a question or comment.
When you post code -- use code tags(# at the top of response box) and provide some context.