Hi All:
Users log in my database using their username and password. Once they login their username and userlevel is stored in a table temporarily using update query. I have a form which displays records for all the users but a user can edit only his/her record. I do this by checking the user name in that temporary table and username on the form. If it matches they can edit their record. This works fine. The problem that I have is that I can't get it to work for the user with user level 'admin'. A user with user level 'admin' can edit all the records.
Here is my code:
My form is based on table tableBusinessOpporunity_Main and the name of the temporary storage table is N_tblLocalUser.
Private Sub Form_Current()
'Build SQL String from creating recordset to determine if
'the current record is from the current user
SQL = "SELECT Capture_Lead, UserLevel FROM tblBusinessOppurtunity_Main " & _
"INNER JOIN N_tblLocalUser ON tblBusinessOppurtunity_Main.Capture_Lead = N_tblLocalUser.UID " & _
"WHERE (Opportunity_ID = " & Me.Opportunity_ID & " AND Capture_Lead = '" & LocalUser & "')" & _
"OR USERLEVEL = 'Admin'"
objRec.Open SQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic, adCmdText
'If there is a match, allow the user to add, delete and edit records
If objRec.RecordCount = 1 Then
Me.AllowDeletions = True
Me.AllowEdits = True
Me!cboCaptureLead.Locked = True
Me.cmdAdd.Enabled = True
Me.QBFMilestonesubform.Enabled = True
Me.QBFActionItemSubform.Enabled = True
Me.QBFNotessubform.Enabled = True
'Else don't allow the user to add, delete and edit records
Else
Me.cmdAdd.Enabled = False
Me.AllowDeletions = False
Me.AllowEdits = False
Me.QBFMilestonesubform.Enabled = False
Me.QBFActionItemSubform.Enabled = False
Me.QBFNotessubform.Enabled = False
End If
objRec.Close
End Sub
I would really appreciate any help.
Thanks,
Ekta
Users log in my database using their username and password. Once they login their username and userlevel is stored in a table temporarily using update query. I have a form which displays records for all the users but a user can edit only his/her record. I do this by checking the user name in that temporary table and username on the form. If it matches they can edit their record. This works fine. The problem that I have is that I can't get it to work for the user with user level 'admin'. A user with user level 'admin' can edit all the records.
Here is my code:
My form is based on table tableBusinessOpporunity_Main and the name of the temporary storage table is N_tblLocalUser.
Private Sub Form_Current()
'Build SQL String from creating recordset to determine if
'the current record is from the current user
SQL = "SELECT Capture_Lead, UserLevel FROM tblBusinessOppurtunity_Main " & _
"INNER JOIN N_tblLocalUser ON tblBusinessOppurtunity_Main.Capture_Lead = N_tblLocalUser.UID " & _
"WHERE (Opportunity_ID = " & Me.Opportunity_ID & " AND Capture_Lead = '" & LocalUser & "')" & _
"OR USERLEVEL = 'Admin'"
objRec.Open SQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic, adCmdText
'If there is a match, allow the user to add, delete and edit records
If objRec.RecordCount = 1 Then
Me.AllowDeletions = True
Me.AllowEdits = True
Me!cboCaptureLead.Locked = True
Me.cmdAdd.Enabled = True
Me.QBFMilestonesubform.Enabled = True
Me.QBFActionItemSubform.Enabled = True
Me.QBFNotessubform.Enabled = True
'Else don't allow the user to add, delete and edit records
Else
Me.cmdAdd.Enabled = False
Me.AllowDeletions = False
Me.AllowEdits = False
Me.QBFMilestonesubform.Enabled = False
Me.QBFActionItemSubform.Enabled = False
Me.QBFNotessubform.Enabled = False
End If
objRec.Close
End Sub
I would really appreciate any help.
Thanks,
Ekta
