How do I keep a combo box value from being changed back to previous value?

[Seth Schrock]

I have a combo box that keeps track of the status of my jobs. The different options are:

1. New
2. In Progress
3. Completed

When I create a job, the default value is New. What I'm wanting is make it so that once the status has been changed to In Progress or Completed, the ability to change the status back to new is removed. I've gotten as far as to figure out that I need to do this in the Before_Update event of the combo box, but I can't figure out how to test the old value to see if it was greater than 1 (meaning it had a status of either In Progress or Completed) and if the new value is 1 (New) in which case I would cancel the update with a message explaining why the status couldn't be changed to New.

Part of my problem is that I'm not sure what the value of Me.JobStatus (the name of the combo box) is in the Before_Update event. For example, if the status is 2 - In Progress and the user attempts to change it to 3 - Completed, what is the value of Me.JobStatus before the update; 2 or 3? I hope that makes sense.

[TheSmileyCoder]

Actually the combobox (and textbox) has a property called .OldValue

As I recall the .Oldvalue contains the value of the field when the record was loaded, and retains the value until record is saved, or a new record is loaded.

I think that should satisfy your needs just fine.

Something like this should work (untested aircode):

Private Sub JobStatus_BeforeUpdate(Cancel as Integer)
 If Me.NewRecord then
   Exit Sub
 End If
 If me.JobStatus.OldValue>Me.JobStatus.Value then
   Cancel=True
   MsgBox "This change is not allowed"
 End if
 
End Sub 

[NeoPa]

Alternatively, set a WHERE clause in your Control Source SQL such that only values greater than the current value are included in the list. That way only valid selections are even available.

[Seth Schrock]

@NeoPa, I just tried your suggestion, but the problem is that it make the combo box blank when you are just viewing the job.

@TheSmileyCoder, For some reason the value isn't changing back to the previous value. This keeps me from being able to move to another record. I thought that the Cancel = True part would change the value back to the previous value. I tried changing the value back to be the .OldValue, but I got an error. Here is what I've got:

Private Sub JobStatus_BeforeUpdate(Cancel As Integer)
 
If Me.NewRecord Then
    Exit Sub
End If
 
If Me.JobStatus.OldValue > Me.JobStatus.Value Then
    Cancel = True
    'Me.JobStatus.Value = Me.JobStatus.OldValue
    MsgBox "You cannot change the status to a previous level."
End If
 
End Sub
 

I have line 9 commented out so that the code could execute.

[TheSmileyCoder]

Sorry, should have realised that would give trouble. Try replacing line 9 with

Me.Jobstatus.Undo

[Seth Schrock]

Bingo! Thanks for your help. This is only the second time that I have worked with the Cancel ability and I haven't gotten used to all the things that are necessary to have everything back to the way it was.

[NeoPa]

Seth Shrock:
@NeoPa, I just tried your suggestion, but the problem is that it make the combo box blank when you are just viewing the job.

You're absolutely right Seth. I didn't consider that (Ooops).

[Rabbit]

Seth Schrock
@NeoPa, I just tried your suggestion, but the problem is that it make the combo box blank when you are just viewing the job.

A greater than or equal to should solve that problem.

[Seth Schrock]

Well, that worked for existing records, but when creating a new record, it is only showing status 2 and 3 (default value is 1).

[NeoPa]

Rabbit:
A greater than or equal to should solve that problem.

I don't believe that would work reliably in all scenarios. A list of records would only have one control setup to work with and that would require it to be set up specifically for each record.

[Rabbit]

You may have to Nz() the value.

Edit: NeoPa, true, I think you would would have to requery in the on current event.

[Seth Schrock]

That worked. I just put the following in the WHERE clause:

WHERE JobStatusID >= NZ(Forms!frmJob!JobStatus,1)

and then did a requery of the combo box in the form's On_Current event. BINGO! I do like this solution better than my original plan because, as NeoPa said, it makes it so that the wrong values aren't even shown. Thanks Rabbit for the solution and NeoPa for the suggestion.

[TheSmileyCoder]

I must admit I do like that solution better then my own, since as you mention, it does not present the invalid values.

[NeoPa]

Always a pleasure Seth.

PS. I like your avatar. V 8-)