Access 2007 VBA Help: Controlling Combobox Value Selections

GibsonSt19

Hey all, I really thought I'd be able to nail this, but I'm struggling.

I have a Project tracking Database, and on the main Project form, there's a 'Project Status' field, which contains around 15 values (i.e. 01 New, 06 Approved to Build, 10 Approved to Implement, 12 Closed - Cancelled, 14 Closed - Implemented etc).

I want to be able to restrict some Project Status' to users, and allow myself (as admin) to select closed statuses, as below...

It should be pretty simple really. Three variables (Status, Closed Date and User (which can be admin or other), but I'm having difficulties.

1. If status selected has "Closed" in the string, and Closed date is null/blank, and user is 'admin', I want the status to be updated accordingly, and the Closed Date populated with the current date/time. <LI itxtHarvested="0" itxtNodeId="17">If status selected has "Closed" in the string, and Closed date not is null/blank, regardless of user, I want a msgbox to advise "Record already closed", and any changes to be cancelled. <LI itxtHarvested="0" itxtNodeId="16">If status selected doesn't have "Closed" in the string, and Closed Date is not null/blank, if the user is admin, I want a msgbox to question the admin whether they're sure they want to re-open the record. If yes, set status accordingly, and clear Closed Date field. If no, cancel update.
2. If status selected doesn't have "Closed in the string, and Closed Date is not null/blank, if the user is not admin, I want a msgbox to advise "You're not authorised to re-open a project".

The code I have tried thus far is below:

Private Sub change_status_AfterUpdate()
' Allow Admins to set records to Closed, providing that the change_closed_date field is blank or null.
    If InStr(Me.change_status, "Closed") > 0 And IsNull(Me.change_closed_date) Or (Me.change_closed_date = "") And (Usr <> "admin") Then
 
            ' Remind mere mortals (non-admin) they have insufficient privileges to Close a record.
            MsgBox "Please note, only Database Administrators may close a record ", vbOKOnly, "Hmmm..."
            Me.Undo
            Cancel = True
 
        Else
 
            Me.change_closed_date = Now()
 
    End If
 
 
' Allow Admins to re-open Closed records.
    If (Not (InStr(Me.change_status, "Closed") > 0)) Then
 
    If (Not IsNull(Me.change_closed_date) Or (Me.change_closed_date <> "")) And (Usr <> "admin") Then
 
        ' Remind mere mortals that they have insufficient privileges to re-open a record.
        MsgBox "Mere mortal, you have insufficient privileges to raise a record from [URL="http://www.access-programmers.co.uk/forums/showthread.php?p=1108926&posted=1#"][COLOR=darkgreen]data[/COLOR][/URL] death! Please contact a Database Administrator.", vbOKOnly, "Pah!"
        Me.Undo
        Cancel = True
 
            Else
 
               Answer = MsgBox("Privileged user, are you sure you wish to re-open this record, and clear the Closed Date field?", vbYesNo, "Really?")
                    If Answer = vbYes Then
                        Me.change_closed_date = ""
 
                            ' Let record remain Closed, if Admin isnt sure they want to re-open.
                        Else
 
                            Me.Undo
                            Cancel = True
 
                    End If
    End If
    End If
End Sub

The Project status field is actually 'change_status'.

There can be only one status set at any one time.

There are numerous 'Closed' status' a project can be in ('Closed Cancelled', 'Closed Implemented', 'Closed Subsumed: and 'Closed - No Impact Detected').

I understand I could approach it in a different way, by only making certain status' visible to Users, and additional status' to Admins, however the request I've had is that it's set up, as I've presented it.

I guess the reason it's not working, is down to my If Then Else grouping, but can't work it out at all.

Any help would be massively appreciated.

rmg1

If I'm reading it right, these two lines have incorrect bracketing:-
If InStr(Me.change_status, "Closed") > 0 And IsNull(Me.change_closed_date) Or (Me.change_closed_date = "") And (Usr <> "admin") Then

If (Not IsNull(Me.change_closed_date) Or (Me.change_closed_date <> "")) And (Usr <> "admin") Then

The first one should be:-
if Usr<>"admin" and InStr(Me.change_status, "Closed") > 0 and (IsNull(Me.change_closed_date) Or (Me.change_closed_date = "")) Then

The second one should be:-
If Usr <> "admin" and (Not IsNull(Me.change_closed_date) or Me.change_closed_date <> "")

Try that and let me know how you get on.

GibsonSt19

Hey rmg1, thanks for that, that's sorted the problem!

Really appreciate your guidance :T

rmg1

No problem.