Ideas for Multiple Approvals of Form (1 Viewer)

R

ramez75

Registered User.
Local time
Today, 12:50
Joined
Dec 23, 2008
Messages
181
I have been working on this database for a while and now I am at a point of figuring out how to approve the form/Record by multiple users. I have something put together but I dont feel it sufficient and hence, any ideas recommendations will be greatly appreciated.......

This what I have so far. I have a Main form (Form1) and 2 subforms (Subform1 and Subform2). The approvals are located on Subform2. The way I have the setup right now is
I have 7 checkboxes (Chk1, chk2,.....chk7) each one is tied up to a frame (Frame1, Frame2,.....Frame7) with property = Invisible
Each frame has 2 checkboxes (Approved-option3, Rejected-option4), A memo field to document reason for rejection, Approved by (Locked) and Date (Locked)

Each form/Record can be approved by upto 7 department depending on the situation. Example RecordID=1 will be approved by 2 departments so chk=1 and lets say chk=5. RecordID=2 by 4 departments, lets say chk=2, chk=3, chk=6, chk=7 and so on so forth.

Below is the code I have so far

Code: 
Private Sub Frame1_AfterUpdate()
If Frame1.Value = 3 Then
    Me.datecapaapproved = Date
    Me.approvedby = Nz(DLookup("Nameofuserid", "tblcloselist", "[UserID]=" & Chr(34) & Me.usernameid & Chr(34)), "")
ElseIf Frame1.Value = 4 Then
    Me.datecapaapproved = Date
    Me.approvedby = Nz(DLookup("Nameofuserid", "tblcloselist", "[UserID]=" & Chr(34) & Me.usernameid & Chr(34)), "")
    Me.rejectcomments.Enabled = True
Else
    Me.datecapaapproved = Null
    Me.approvedby = Null
    Me.rejectcomments.Enabled = False
End If
End Sub
I have the above repeated 7 times.

Ok below is what I am trying to accomplish.

1. I want to lock the checkboxes since they control the frames so that when the check boxes are checked then thats it cannot be changed if u go back into the Record. That way once the approval dept are assigned no one can change them.

2. I want also the frames to be visible for current records. Right now if i close the form and open it back up the frame is invisible and I have to check the boxes again. Hence, I will have to remember what each record had approvals assigned too

3. This one is the tricky one..........Well since i have different approval dept i want for example if department 1 log into the system (tied to chk=1 which is ties to Frame1) can only approve their section and not others

I hope my explanation is clear.....Again if there are other ideas or suggestion I am open to listen to them

Thank you
 
I figured out how to go around resolving 1 and 2 by using Currentevent of the subform and afterupdate event of the chk boxes......

I still have no idea how to go around bullet number 3............Unless my approach is not right
 
Instead of making them visible or not simply make them all disabled and locked. Then on the opn of the form only make the particular check box enabled if it has not already been checked. You will need to apply a select case statement to decide which frame is available to which dept.
 
DCrake said:
Instead of making them visible or not simply make them all disabled and locked. Then on the opn of the form only make the particular check box enabled if it has not already been checked. You will need to apply a select case statement to decide which frame is available to which dept.
Click to expand...

YEa thats what I did to resolve 1 and 2...........Its the 3rd portion whats driving me crazy..........So have cases, the problem though I have usernames not by departments. I might have 100 user logging into the system, so you suggesting that break the users based on departments and if any of dept1 logs in Frame1 will be enabled and so on........Let me try something like that. The database is so big at this point but I will see if I can put the users into buckets
 
Think of it like an organisation chart from the MD downwards. Each user as an immediate supervisor or overall manager. These are your buckets. The only issue is when someone is a multitasker.
 
DCrake said:
Think of it like an organisation chart from the MD downwards. Each user as an immediate supervisor or overall manager. These are your buckets. The only issue is when someone is a multitasker.
Click to expand...

I will not have any multitaskers........Each user will belong to only one department..........I hope I am not misunderstanding the multitasker
 
That's good then. You can get examples when a general manager is using a system and needs to be able to update records belonging to more than one department. Thankfully you do not have that worry.
 
But even if i get the bucket thing to work....I still have to figure out a way if the the approver rejects then I have to find a way to reset so the approve and reject fucntion are active. Right now if the user choses active/reject the frame is disabled so the status cant be changed. But if the status is reject then somehow the admin of the system to know about it or the creator of the form/record know about it address the issue and reset the approve/reject. Something along those lines
 
You need to go back to basics and think about what happens when a ne record is added. Do you know at that point who can approve what and who can't. These are referred to:

Known Knowns
Unknown Knowns
Unknown Unknowns
Known unknowns

If you know who can approve which part then this is a known known.
If dept a can only aprove parts 1 and three of any recrod this is an unknown known

if any depatment can approve any section and has to be decided by the type of record added this is an unknown unknown
If when a record is added the contents determine who can approve what, this is a knon unknown.

What have you got?
 
Let me give some info about the system. Its an investigation system. So there are different kind of Users
"Admin" self explanatory, maintenance, etc, access to everything
"Initiator" - Can initiate the form/record and put in basic info and assign the investigation to someone
"Engineer" - Can also initiate and is the one who assignes who the approvers are and the one who closes the form/record and archive it. (There are 7 approving departments that will approve the same information on the form before the form/record can be closed and archived)
"Investigators" - Cannot initiate or close or assign approvers all they can do is go into the form/record and add info, modify attach, etc
"Approvers" - Cannot initiate or close or assign approvers all they can do is go into the form/record and review the info and decide whether to approve or reject. If approve then all is good. Once the last approver has approved the Engineer can go in and close and archive the record. On the other hand if one of the approver rejects then the initiator and engineer need to know to address the issue

Right now I have the users have access levels based on the above. I am ofcourse trying to work on putting the approvers (UserLevel=approver) into different department bucket so that when they login only their frame is enabled.

This will I believe work something like that in theory atleast didnt try it. On currentEvent of the Subform will have some sort of IF Conditions based on the Userlevel and not users since anyone from the dept can approve the section


So with the info I provided where do u think I stand.
 
Below is the code that I have behind the Subform....The only thing I still cant get to work is only allowing the user from certain department to have their frame enabled.......Its not the best, I am sure it can be optimized. So I am trying to add something to control the enable property of Frame1,2,3,4,5,6,7 based on the user logging onto the system. I created DeptID which range 1 to 7. So if the user logging on had DeptID=1 only Frame1 will be enabled others will be disabled......I believe I will have to add the if condition in the CurrentEvent and OpenEvent of the subform is that true?

The other thing i will need to add is when reject is chosen an email is sent to the originator of the record/form to address the issue and reset the Frame so the the approver can go back in and approve

Code: 
Option Compare Database
Private Sub Check1_AfterUpdate()
If Me.Check1 = True Then
   Me.Frame1.Enabled = True
   Me.Check1.Locked = True
Else
   Me.Frame1.Enabled = False
   Me.Check1.Locked = True
End If
End Sub
Private Sub Check2_AfterUpdate()
If Me.Check2 = True Then
   Me.Frame2.Enabled = True
   Me.Check2.Locked = True
Else
   Me.Frame2.Enabled = False
   Me.Check2.Locked = True
End If
End Sub
Private Sub Check3_AfterUpdate()
If Me.Check3 = True Then
   Me.Frame3.Enabled = True
   Me.Check3.Locked = True
Else
   Me.Frame3.Enabled = False
   Me.Check3.Locked = True
End If
End Sub
Private Sub Check4_AfterUpdate()
If Me.Check4 = True Then
   Me.Frame4.Enabled = True
   Me.Check4.Locked = True
Else
   Me.Frame4.Enabled = False
   Me.Check4.Locked = True
End If
End Sub
Private Sub Check5_AfterUpdate()
If Me.Check5 = True Then
   Me.Frame5.Enabled = True
   Me.Check5.Locked = True
Else
   Me.Frame5.Enabled = False
   Me.Check5.Locked = True
End If
End Sub
Private Sub Check6_AfterUpdate()
If Me.Check6 = True Then
   Me.Frame6.Enabled = True
   Me.Check6.Locked = True
Else
   Me.Frame6.Enabled = False
   Me.Check6.Locked = True
End If
End Sub
Private Sub Check7_AfterUpdate()
If Me.Check7 = True Then
   Me.approversother.Enabled = True
   Me.Frame7.Enabled = True
   Me.Check7.Locked = True
Else
   Me.approversother.Enabled = False
   Me.Frame7.Enabled = False
   Me.Check7.Locked = True
End If
End Sub
Private Sub Form_Current()
    If User.AccessID = 1 Then     ' Admin
        Me.Check1.Enabled = True
        Me.Check2.Enabled = True
        Me.Check3.Enabled = True
        Me.Check4.Enabled = True
        Me.Check5.Enabled = True
        Me.Check6.Enabled = True
        Me.Check7.Enabled = True
        Me.Frame1.Enabled = True
        Me.Frame2.Enabled = True
        Me.Frame3.Enabled = True
        Me.Frame4.Enabled = True
        Me.Frame5.Enabled = True
        Me.Frame6.Enabled = True
        Me.Frame7.Enabled = True
    ElseIf User.AccessID = 8 Then 'Quality
        If (Me.Check1.Enabled = True) Or (Me.Check1.Enabled = False) Then
        Me.Frame1.Enabled = False
        Me.Check35.Enabled = False
        Me.Check36.Enabled = False
        End If
        If (Me.Check2.Enabled = True) Or (Me.Check2.Enabled = False) Then
        Me.Frame2.Enabled = False
        Me.Check37.Enabled = False
        Me.Check38.Enabled = False
        End If
        If (Me.Check3.Enabled = True) Or (Me.Check3.Enabled = False) Then
        Me.Frame3.Enabled = False
        Me.Check39.Enabled = False
        Me.Check40.Enabled = False
        End If
        If (Me.Check4.Enabled = True) Or (Me.Check4.Enabled = False) Then
        Me.Frame4.Enabled = False
        Me.Check41.Enabled = False
        Me.Check42.Enabled = False
        End If
        If (Me.Check5.Enabled = True) Or (Me.Check5.Enabled = False) Then
        Me.Frame5.Enabled = False
        Me.Check43.Enabled = False
        Me.Check44.Enabled = False
        End If
        If (Me.Check6.Enabled = True) Or (Me.Check6.Enabled = False) Then
        Me.Frame6.Enabled = False
        Me.Check45.Enabled = False
        Me.Check46.Enabled = False
        End If
        If (Me.Check7.Enabled = True) Or (Me.Check7.Enabled = False) Then
        Me.Frame7.Enabled = False
        Me.Check47.Enabled = False
        Me.Check48.Enabled = False
        End If
    ElseIf User.AccessID = 7 Then 'Approvers
        Me.Check1.Locked = True
        Me.Check2.Locked = True
        Me.Check3.Locked = True
        Me.Check4.Locked = True
        Me.Check5.Locked = True
        Me.Check6.Locked = True
        Me.Check7.Locked = True
    Else                          'Initiators, and Investigators
        Me.Check1.Enabled = False
        Me.Check2.Enabled = False
        Me.Check3.Enabled = False
        Me.Check4.Enabled = False
        Me.Check5.Enabled = False
        Me.Check6.Enabled = False
        Me.Check7.Enabled = False
        Me.Frame1.Enabled = False
        Me.Frame2.Enabled = False
        Me.Frame3.Enabled = False
        Me.Frame4.Enabled = False
        Me.Frame5.Enabled = False
        Me.Frame6.Enabled = False
        Me.Frame7.Enabled = False
    End If
    If User.AccessID = 7 Then 'Approvers
        If Me.Check1 = True Then
        Me.Frame1.Locked = False
        Me.Check35.Locked = False
        Me.Check36.Locked = False
        Else
        Me.Frame1.Locked = True
        Me.Check35.Locked = True
        Me.Check36.Locked = True
        End If
        
        If Me.Check2 = True Then
        Me.Frame2.Locked = False
        Me.Check37.Locked = False
        Me.Check38.Locked = False
        Else
        Me.Frame2.Locked = True
        Me.Check37.Locked = True
        Me.Check38.Locked = True
        End If
        
        If Me.Check3 = True Then
        Me.Frame3.Locked = False
        Me.Check39.Locked = False
        Me.Check40.Locked = False
        Else
        Me.Frame3.Locked = True
        Me.Check39.Locked = True
        Me.Check40.Locked = True
        End If
        
        If Me.Check4 = True Then
        Me.Frame4.Locked = False
        Me.Check41.Locked = False
        Me.Check42.Locked = False
        Else
        Me.Frame4.Locked = True
        Me.Check41.Locked = True
        Me.Check42.Locked = True
        End If
        
        If Me.Check5 = True Then
        Me.Frame5.Locked = False
        Me.Check43.Locked = False
        Me.Check44.Locked = False
        Else
        Me.Frame5.Locked = True
        Me.Check43.Locked = True
        Me.Check44.Locked = True
        End If
        
        If Me.Check6 = True Then
        Me.Frame6.Locked = False
        Me.Check45.Locked = False
        Me.Check46.Locked = False
        Else
        Me.Frame6.Locked = True
        Me.Check45.Locked = True
        Me.Check46.Locked = True
        End If
        
        If Me.Check7 = True Then
        Me.Frame7.Locked = False
        Me.Check47.Locked = False
        Me.Check48.Locked = False
        Else
        Me.Frame7.Locked = True
        Me.Check47.Locked = True
        Me.Check48.Locked = True
        End If
    End If
If Frame1.Value = 3 Then
Me.rejectcomments.Enabled = False
Me.Frame1.Enabled = False
ElseIf Frame1.Value = 4 Then
Me.rejectcomments.Enabled = True
Me.Frame1.Enabled = False
Else
Me.rejectcomments.Enabled = False
End If
If Me.rejectcomments <> "" Then
Me.rejectcomments.Enabled = False
End If
If Frame2.Value = 5 Then
Me.rejectcommentsRegulatory.Enabled = False
Me.Frame2.Enabled = False
ElseIf Frame2.Value = 6 Then
Me.rejectcommentsRegulatory.Enabled = True
Me.Frame2.Enabled = False
Else
Me.rejectcommentsRegulatory.Enabled = False
End If
If Me.rejectcommentsRegulatory <> "" Then
Me.rejectcommentsRegulatory.Enabled = False
End If
If Frame3.Value = 7 Then
Me.rejectcommentsRD.Enabled = False
Me.Frame3.Enabled = False
ElseIf Frame2.Value = 8 Then
Me.rejectcommentsRD.Enabled = True
Me.Frame3.Enabled = False
Else
Me.rejectcommentsRD.Enabled = False
End If
If Me.rejectcommentsRD <> "" Then
Me.rejectcommentsRD.Enabled = False
End If
If Frame4.Value = 9 Then
Me.rejectcommentsManuf.Enabled = False
Me.Frame4.Enabled = False
ElseIf Frame4.Value = 10 Then
Me.rejectcommentsManuf.Enabled = True
Me.Frame4.Enabled = False
Else
Me.rejectcommentsManuf.Enabled = False
End If
If Me.rejectcommentsManuf <> "" Then
Me.rejectcommentsManuf.Enabled = False
End If
If Frame5.Value = 11 Then
Me.rejectcommentsGMS.Enabled = False
Me.Frame5.Enabled = False
ElseIf Frame5.Value = 12 Then
Me.rejectcommentsGMS.Enabled = True
Me.Frame5.Enabled = False
Else
Me.rejectcommentsGMS.Enabled = False
End If
If Me.rejectcommentsGMS <> "" Then
Me.rejectcommentsGMS.Enabled = False
End If
If Frame6.Value = 13 Then
Me.rejectcommentsPurchasing.Enabled = False
Me.Frame6.Enabled = False
ElseIf Frame6.Value = 14 Then
Me.rejectcommentsPurchasing.Enabled = True
Me.Frame6.Enabled = False
Else
Me.rejectcommentsPurchasing.Enabled = False
End If
If Me.rejectcommentsPurchasing <> "" Then
Me.rejectcommentsPurchasing.Enabled = False
End If
If Frame7.Value = 15 Then
Me.rejectcommentsOther.Enabled = False
Me.Frame7.Enabled = False
ElseIf Frame7.Value = 16 Then
Me.rejectcommentsOther.Enabled = True
Me.Frame7.Enabled = False
Else
Me.rejectcommentsOther.Enabled = False
End If
If Me.rejectcommentsOther <> "" Then
Me.rejectcommentsOther.Enabled = False
End If
End Sub
Private Sub Form_Open(cancel As Integer)
On Error GoTo Err_Form_Open
    
    If User.AccessID = 1 Then ' Admin
        Me.Check1.Enabled = True
        Me.Check2.Enabled = True
        Me.Check3.Enabled = True
        Me.Check4.Enabled = True
        Me.Check5.Enabled = True
        Me.Check6.Enabled = True
        Me.Check7.Enabled = True
        Me.Frame1.Enabled = True
        Me.Frame2.Enabled = True
        Me.Frame3.Enabled = True
        Me.Frame4.Enabled = True
        Me.Frame5.Enabled = True
        Me.Frame6.Enabled = True
        Me.Frame7.Enabled = True
    ElseIf User.AccessID = 8 Then 'Quality
        If (Me.Check1.Enabled = True) Or (Me.Check1.Enabled = False) Then
        Me.Frame1.Enabled = False
        Me.Check35.Enabled = False
        Me.Check36.Enabled = False
        End If
        If (Me.Check2.Enabled = True) Or (Me.Check2.Enabled = False) Then
        Me.Frame2.Enabled = False
        Me.Check37.Enabled = False
        Me.Check38.Enabled = False
        End If
        If (Me.Check3.Enabled = True) Or (Me.Check3.Enabled = False) Then
        Me.Frame3.Enabled = False
        Me.Check39.Enabled = False
        Me.Check40.Enabled = False
        End If
        If (Me.Check4.Enabled = True) Or (Me.Check4.Enabled = False) Then
        Me.Frame4.Enabled = False
        Me.Check41.Enabled = False
        Me.Check42.Enabled = False
        End If
        If (Me.Check5.Enabled = True) Or (Me.Check5.Enabled = False) Then
        Me.Frame5.Enabled = False
        Me.Check43.Enabled = False
        Me.Check44.Enabled = False
        End If
        If (Me.Check6.Enabled = True) Or (Me.Check6.Enabled = False) Then
        Me.Frame6.Enabled = False
        Me.Check45.Enabled = False
        Me.Check46.Enabled = False
        End If
        If (Me.Check7.Enabled = True) Or (Me.Check7.Enabled = False) Then
        Me.Frame7.Enabled = False
        Me.Check47.Enabled = False
        Me.Check48.Enabled = False
        End If
    ElseIf User.AccessID = 7 Then 'Approvers
        Me.Check1.Locked = True
        Me.Check2.Locked = True
        Me.Check3.Locked = True
        Me.Check4.Locked = True
        Me.Check5.Locked = True
        Me.Check6.Locked = True
        Me.Check7.Locked = True
    Else                          'Initiators, and Investigators
        Me.Check1.Enabled = False
        Me.Check2.Enabled = False
        Me.Check3.Enabled = False
        Me.Check4.Enabled = False
        Me.Check5.Enabled = False
        Me.Check6.Enabled = False
        Me.Check7.Enabled = False
        Me.Frame1.Enabled = False
        Me.Frame2.Enabled = False
        Me.Frame3.Enabled = False
        Me.Frame4.Enabled = False
        Me.Frame5.Enabled = False
        Me.Frame6.Enabled = False
        Me.Frame7.Enabled = False
    End If
    If User.AccessID = 7 Then 'Approvers
        If Me.Check1 = True Then
        Me.Frame1.Locked = False
        Me.Check35.Locked = False
        Me.Check36.Locked = False
        Else
        Me.Frame1.Locked = True
        Me.Check35.Locked = True
        Me.Check36.Locked = True
        End If
        
        If Me.Check2 = True Then
        Me.Frame2.Locked = False
        Me.Check37.Locked = False
        Me.Check38.Locked = False
        Else
        Me.Frame2.Locked = True
        Me.Check37.Locked = True
        Me.Check38.Locked = True
        End If
        
        If Me.Check3 = True Then
        Me.Frame3.Locked = False
        Me.Check39.Locked = False
        Me.Check40.Locked = False
        ElseIf Me.Check3 = False Then
        Me.Frame3.Locked = True
        Me.Check39.Locked = True
        Me.Check40.Locked = True
        End If
        
        If Me.Check4 = True Then
        Me.Frame4.Locked = False
        Me.Check41.Locked = False
        Me.Check42.Locked = False
        Else
        Me.Frame4.Locked = True
        Me.Check41.Locked = True
        Me.Check42.Locked = True
        End If
        
        If Me.Check5 = True Then
        Me.Frame5.Locked = False
        Me.Check43.Locked = False
        Me.Check44.Locked = False
        Else
        Me.Frame5.Locked = True
        Me.Check43.Locked = True
        Me.Check44.Locked = True
        End If
        
        If Me.Check6 = True Then
        Me.Frame6.Locked = False
        Me.Check45.Locked = False
        Me.Check46.Locked = False
        Else
        Me.Frame6.Locked = True
        Me.Check45.Locked = True
        Me.Check46.Locked = True
        End If
        
        If Me.Check7 = True Then
        Me.Frame7.Locked = False
        Me.Check47.Locked = False
        Me.Check48.Locked = False
        Else
        Me.Frame7.Locked = True
        Me.Check47.Locked = True
        Me.Check48.Locked = True
        End If
    End If
                  
Exit_Form_Open:
    Exit Sub
Err_Form_Open:
    MsgBox Err.Description
    Me.Visible = True
    Resume Exit_Form_Open
    
End Sub
Private Sub Frame1_AfterUpdate()
Me.usernameid = fOSUserName
If Frame1.Value = 3 Then
    Me.datecapaapproved = Date
    Me.approvedby = Nz(DLookup("Nameofuserid", "tblcloselist", "[UserID]=" & Chr(34) & Me.usernameid & Chr(34)), "")
    Me.rejectcomments = Null
    Me.rejectcomments.Enabled = False
ElseIf Frame1.Value = 4 Then
    Me.datecapaapproved = Date
    Me.approvedby = Nz(DLookup("Nameofuserid", "tblcloselist", "[UserID]=" & Chr(34) & Me.usernameid & Chr(34)), "")
    Me.rejectcomments.Enabled = True
Else
    Me.datecapaapproved = Null
    Me.approvedby = Null
    Me.rejectcomments.Enabled = False
End If
End Sub
Private Sub Frame2_AfterUpdate()
Me.usernameid = fOSUserName
If Frame2.Value = 5 Then
    Me.dateapprovedRegulatory = Date
    Me.usernameid = fOSUserName
    Me.approvedbyRegulatory = Nz(DLookup("Nameofuserid", "tblcloselist", "[UserID]=" & Chr(34) & Me.usernameid & Chr(34)), "")
    Me.rejectcommentsRegulatory = Null
    Me.rejectcommentsRegulatory.Enabled = False
ElseIf Frame2.Value = 6 Then
    Me.dateapprovedRegulatory = Date
    Me.approvedbyRegulatory = Nz(DLookup("Nameofuserid", "tblcloselist", "[UserID]=" & Chr(34) & Me.usernameid & Chr(34)), "")
    Me.rejectcommentsRegulatory.Enabled = True
Else
    Me.dateapprovedRegulatory = Null
    Me.approvedbyRegulatory = Null
    Me.rejectcommentsRegulatory.Enabled = False
End If
End Sub
Private Sub Frame3_AfterUpdate()
Me.usernameid = fOSUserName
If Frame3.Value = 7 Then
    Me.dateapprovedRD = Date
    Me.approvedbyRD = Nz(DLookup("Nameofuserid", "tblcloselist", "[UserID]=" & Chr(34) & Me.usernameid & Chr(34)), "")
    Me.rejectcommentsRD = Null
    Me.rejectcommentsRD.Enabled = False
ElseIf Frame3.Value = 8 Then
    Me.dateapprovedRD = Date
    Me.approvedbyRD = Nz(DLookup("Nameofuserid", "tblcloselist", "[UserID]=" & Chr(34) & Me.usernameid & Chr(34)), "")
    Me.rejectcommentsRD.Enabled = True
Else
    Me.dateapprovedRD = Null
    Me.approvedbyRD = Null
    Me.rejectcommentsRD.Enabled = False
End If
End Sub
Private Sub Frame4_AfterUpdate()
Me.usernameid = fOSUserName
If Frame4.Value = 9 Then
    Me.dateapprovedManuf = Date
    Me.approvedbyManuf = Nz(DLookup("Nameofuserid", "tblcloselist", "[UserID]=" & Chr(34) & Me.usernameid & Chr(34)), "")
    Me.rejectcommentsManuf = Null
    Me.rejectcommentsManuf.Enabled = False
ElseIf Frame4.Value = 10 Then
    Me.dateapprovedManuf = Date
    Me.approvedbyManuf = Nz(DLookup("Nameofuserid", "tblcloselist", "[UserID]=" & Chr(34) & Me.usernameid & Chr(34)), "")
    Me.rejectcommentsManuf.Enabled = True
Else
    Me.dateapprovedManuf = Null
    Me.approvedbyManuf = Null
    Me.rejectcommentsManuf.Enabled = False
End If
End Sub
Private Sub Frame5_AfterUpdate()
Me.usernameid = fOSUserName
If Frame5.Value = 11 Then
    Me.dateapprovedGMS = Date
    Me.approvedbyGMS = Nz(DLookup("Nameofuserid", "tblcloselist", "[UserID]=" & Chr(34) & Me.usernameid & Chr(34)), "")
    Me.rejectcommentsGMS = Null
    Me.rejectcommentsGMS.Enabled = False
ElseIf Frame5.Value = 12 Then
    Me.dateapprovedGMS = Date
    Me.approvedbyGMS = Nz(DLookup("Nameofuserid", "tblcloselist", "[UserID]=" & Chr(34) & Me.usernameid & Chr(34)), "")
    Me.rejectcommentsGMS.Enabled = True
Else
    Me.dateapprovedGMS = Null
    Me.approvedbyGMS = Null
    Me.rejectcommentsGMS.Enabled = False
End If
End Sub
Private Sub Frame6_AfterUpdate()
Me.usernameid = fOSUserName
If Frame6.Value = 13 Then
    Me.dateapprovedPurchasing = Date
    Me.approvedbyPurchasing = Nz(DLookup("Nameofuserid", "tblcloselist", "[UserID]=" & Chr(34) & Me.usernameid & Chr(34)), "")
    Me.rejectcommentsPurchasing = Null
    Me.rejectcommentsPurchasing.Enabled = False
ElseIf Frame6.Value = 14 Then
    Me.dateapprovedPurchasing = Date
    Me.approvedbyPurchasing = Nz(DLookup("Nameofuserid", "tblcloselist", "[UserID]=" & Chr(34) & Me.usernameid & Chr(34)), "")
    Me.rejectcommentsPurchasing.Enabled = True
Else
    Me.dateapprovedPurchasing = Null
    Me.approvedbyPurchasing = Null
    Me.rejectcommentsPurchasing.Enabled = False
End If
End Sub
Private Sub Frame7_AfterUpdate()
Me.usernameid = fOSUserName
If Frame7.Value = 15 Then
    Me.dateapprovedOther = Date
    Me.approvedbyOther = Nz(DLookup("Nameofuserid", "tblcloselist", "[UserID]=" & Chr(34) & Me.usernameid & Chr(34)), "")
    Me.rejectcommentsOther = Null
    Me.rejectcommentsOther.Enabled = False
ElseIf Frame7.Value = 16 Then
    Me.dateapprovedOther = Date
    Me.approvedbyOther = Nz(DLookup("Nameofuserid", "tblcloselist", "[UserID]=" & Chr(34) & Me.usernameid & Chr(34)), "")
    Me.rejectcommentsOther.Enabled = True
Else
    Me.dateapprovedOther = Null
    Me.approvedbyOther = Null
    Me.rejectcommentsOther.Enabled = False
End If
End Sub
 
Below what I ended up adding to the Current and Open event of the subform. Not the best but it does the trick. I still have to figure out a way to email to the initiator when the approver reject.

Code: 
Dim deptvalue As Integer
deptvalue = Nz(DLookup("DeptID", "tblSecurity", "[UserID]=" & Chr(34) & Me.usernameid & Chr(34)), "")
 
 If User.AccessID = 7 Then
    If deptvalue = 1 Then 'DeptID
    Me.Frame1.Visible = True
    Me.Frame2.Visible = False
    Me.Frame3.Visible = False
    Me.Frame4.Visible = False
    Me.Frame5.Visible = False
    Me.Frame6.Visible = False
    Me.Frame7.Visible = False
    ElseIf deptvalue = 2 Then
    Me.Frame1.Visible = False
    Me.Frame2.Visible = True
    Me.Frame3.Visible = False
    Me.Frame4.Visible = False
    Me.Frame5.Visible = False
    Me.Frame6.Visible = False
    Me.Frame7.Visible = False
    ElseIf deptvalue = 3 Then
    Me.Frame1.Visible = False
    Me.Frame2.Visible = False
    Me.Frame3.Visible = True
    Me.Frame4.Visible = False
    Me.Frame5.Visible = False
    Me.Frame6.Visible = False
    Me.Frame7.Visible = False
    ElseIf deptvalue = 4 Then
    Me.Frame1.Visible = False
    Me.Frame2.Visible = False
    Me.Frame3.Visible = False
    Me.Frame4.Visible = True
    Me.Frame5.Visible = False
    Me.Frame6.Visible = False
    Me.Frame7.Visible = False
    ElseIf deptvalue = 5 Then
    Me.Frame1.Visible = False
    Me.Frame2.Visible = False
    Me.Frame3.Visible = False
    Me.Frame4.Visible = False
    Me.Frame5.Visible = True
    Me.Frame6.Visible = False
    Me.Frame7.Visible = False
    ElseIf deptvalue = 6 Then
    Me.Frame1.Visible = False
    Me.Frame2.Visible = False
    Me.Frame3.Visible = False
    Me.Frame4.Visible = False
    Me.Frame5.Visible = False
    Me.Frame6.Visible = True
    Me.Frame7.Visible = False
    ElseIf deptvalue = 7 Then
    Me.Frame1.Visible = False
    Me.Frame2.Visible = False
    Me.Frame3.Visible = False
    Me.Frame4.Visible = False
    Me.Frame5.Visible = False
    Me.Frame6.Visible = False
    Me.Frame7.Visible = True
    End If
End If
 
Code: 
If deptvalue = 1 Then 'DeptID
    Me.Frame1.Visible = True
    Me.Frame2.Visible = False
    Me.Frame3.Visible = False
    Me.Frame4.Visible = False
    Me.Frame5.Visible = False
    Me.Frame6.Visible = False
    Me.Frame7.Visible = False
ElseIf deptvalue = 2 Then
    Me.Frame1.Visible = False
    Me.Frame2.Visible = True
    Me.Frame3.Visible = False
    Me.Frame4.Visible = False
    Me.Frame5.Visible = False
    Me.Frame6.Visible = False
    Me.Frame7.Visible = False
    ElseIf deptvalue = 3 Then
Me.Frame1.Visible = False
    Me.Frame2.Visible = False
    Me.Frame3.Visible = True
    Me.Frame4.Visible = False
    Me.Frame5.Visible = False
    Me.Frame6.Visible = False
    Me.Frame7.Visible = False
ElseIf deptvalue = 4 Then
    Me.Frame1.Visible = False
    Me.Frame2.Visible = False
    Me.Frame3.Visible = False
    Me.Frame4.Visible = True
    Me.Frame5.Visible = False
    Me.Frame6.Visible = False
    Me.Frame7.Visible = False
ElseIf deptvalue = 5 Then
    Me.Frame1.Visible = False
    Me.Frame2.Visible = False
    Me.Frame3.Visible = False
    Me.Frame4.Visible = False
    Me.Frame5.Visible = True
    Me.Frame6.Visible = False
    Me.Frame7.Visible = False
ElseIf deptvalue = 6 Then
    Me.Frame1.Visible = False
    Me.Frame2.Visible = False
    Me.Frame3.Visible = False
    Me.Frame4.Visible = False
    Me.Frame5.Visible = False
    Me.Frame6.Visible = True
    Me.Frame7.Visible = False
ElseIf deptvalue = 7 Then
    Me.Frame1.Visible = False
    Me.Frame2.Visible = False
    Me.Frame3.Visible = False
    Me.Frame4.Visible = False
    Me.Frame5.Visible = False
    Me.Frame6.Visible = False
    Me.Frame7.Visible = True
End If

Reads better using a select case statement


Code: 
'/First make them all invisible by default
    Me.Frame1.Visible = False
    Me.Frame2.Visible = False
    Me.Frame3.Visible = False
    Me.Frame4.Visible = False
    Me.Frame5.Visible = False
    Me.Frame6.Visible = False
    Me.Frame7.Visible = False
'/Next use the Select Case to switch on the desired frame

Code: 
Select Case deptvalue
    Case1:Me.Frame1.Visible = True
    Case2:Me.Frame2.Visible = True
    Case3:Me.Frame3.Visible = True
    Case4:Me.Frame4.Visible = True
    Case5:Me.Frame5.Visible = True
    Case6:Me.Frame6.Visible = True
    Case7:Me.Frame7.Visible = True
End Select

Code: 
'/Or even less code
Dim Ctrl as String
Ctrl = "Frame" & deptvalue
Me(Ctrl).Visible = True
 
Thanks DCrake, I like your 3 line code at the end..........I know what I have right now is not the best from optimized coding but I was just trying to see how everything will fall and if I can get the fucntions to work as I need them.

I will play with the case and the small code you provided and see what happens

Thanks for your input
 
I keep getting the error "Type Mismatch" when I open the form cause of the "deptvalue"......

I have it defined as Dim deptvalue as Integer and in the table it is defined as a Number

So what do i need to do to get around it or fix it
 
You must log in or register to reply here.

Similar threads

I
Solved Select All, Deselect All or multiple record selection in continuous form in MS Access
2
Replies
24
Views
4,742
moke123
moke123
M
Get the Count of a multiple null Fields based on other Field Criteria with AND operator
Replies
4
Views
520
plog
P
S
Running Totals Based on Multiple Criteria
Replies
5
Views
449
spudracer
S
kengooch
Combine a Filter between vStDate and vEndDate (unbound txt boxes) on a form -w- a multi-select list box that filters by tEvType (lookup field Auto Ke)
Replies
6
Views
952
Pat Hartman
P
joshuar
Filling Adobe forms from Access using VBA
2
Replies
30
Views
9,837
theDBguy
theDBguy

Users who are viewing this thread

Total: 2 (members: 0, guests: 2)
Back
Top Bottom