Allow changes to a field only once.

[zestygirl]

I have a field for "projected budget" (is "0" prior to entry) and "final completion date" (is null prior to entry and is date format after update) that I would like to have editable only once.

The condition I'm envisioning is that the field is updatable IF the value is Null (or zero, or whatever), and cannot be updated once it's been filled in....

Or, perhaps there's a better way?

Any ideas?

Thanks!!!

 

[PNGBill]

Put this in the After Update event of the text box control

Me![COLOR=SeaGreen]ControlName[/COLOR].Locked = True

And this in the On Current event of the form

 'Lock [COLOR=SeaGreen]ControlName[/COLOR] field - incase of unwanted changes..
    If IsNull(Me![[COLOR=SeaGreen]ControlName[/COLOR]]) = False Then
        Me![[COLOR=SeaGreen]ControlName[/COLOR]].Locked = True    'Keep field locked from updates..
    Else
        Me![[COLOR=SeaGreen]ControlName[/COLOR]].Locked = False   'Allow editing..
    End If

On Current will lock the control if there is a value and After Update will lock the control

 

[John Big Booty]

You could have the following in the Form's On Current event;

If IsNull(Me.FieldName) then
     Me.FieldName.Enabled = True
     Me.FieldName.Locked = False
Else
     Me.FieldName.Enabled = False
     Me.FieldName.Locked = True
End If

 

[John Big Booty]

@ PNGBill

SNAP

 

[PNGBill]

@ PNGBill

SNAP

One in a million your code won't lock the field after data entered - or will it?

 

[John Big Booty]

NO my code will only lock the field when the form is opened and the condition is met or you go to a record where the condition is met, to lock the field after data entry;

     Me.FieldName.Enabled = False
     Me.FieldName.Locked = True

In the field's After Update event (I think will do the trick).

 

[PNGBill]

So my post was spot on except didn't cover enables, only lock. Is enabled also important?

 

[boblarson]

So my post was spot on except didn't cover enables, only lock. Is enabled also important?

Locking and disabling will keep the user from being able to select it at all. So you wouldn't want to do that if you were going to let them copy the data by selecting it and using Ctrl+C.

 

[PNGBill]

Then just to cover the question of preventing data edit, locking is all that is required.

 

[John Big Booty]

Then just to cover the question of preventing data edit, locking is all that is required.

Correct. It all depends how you wish the user to interact with that data latter on. It is simply a habit of mine to Lock and Disable a control in most cases, unless I know that the user may wish/need to copy and paste as Bob suggests.

 

[zestygirl]

Bill - Worked liked a charm!!

(Thank you too, John, I did not enter your solution tho...because Bill's seemed to do the trick. However, for some reason, I found that only entering the second condition (oncurrent) was actually necessary....

Perhaps something I've failed to explain, but, it was the only code I had to input, and I copied it and pasted it allll over my form.

;D

Thank you!!

 

[John Big Booty]

Disabling the control stops the user tabbing or selecting a control and then complaining that they can't change the value

 

[PNGBill]

Disabling the control stops the user tabbing or selecting a control and then complaining that they can't change the value

Good point.

ZestyGirl, Really check your form as the way you did it may allow data to be entered and then edited before the form is closed.

By adding the code to the control as well, as soon as the mouse leaves the control, or something like this, no edits are allowed.

You can build in a Command Button to click and allow edits - not sure if a password can be included but it should be able to be done via InputBox.

 

[John Big Booty]

Notice you mentioned Null Or Zero, try this;

If IsNull(Me.FieldName) Or Me.FieldName = 0 Then
     Me.FieldName.Enabled = True
     Me.FieldName.Locked = False
Else
     Me.FieldName.Enabled = False
     Me.FieldName.Locked = True
End If

 

[Galaxiom]

In this situation I generally set a control to locked and disabled. Locked alone causes the control to be greyed out which doesn't always look good.

I also set its border style to transparent. This makes it quite clear at a glance that the value cannot be edited.

One way to deal with the copying of the value from a disabled control is to use an accompanying button. This provides a convenient one-click operation and is especially useful if the copy operation is frequently required.

Although this obviously could clutter the form if applied to every control it is rare that a user would have a need to copy every control from a record. One way to avoid the clutter problem is to impliment the copy "button" as a single combo with the user selecting the control to be copied from the dropdown. The OnUpDate event is used to trigger the code and completes by setting the value of the combo back to something like "Copy to Clipboard" at the end.

One problem with just locking a combo control is that it will still dropdown when clicked. This is exacerbated if the DropDown Method is used in the OnClick event because it increases the target area of the click. This is especially unpleasant in Datasheet view where a user may be navigating the form by clicking anywhere on the record.

A workaround for this is to include both the combo and a textbox with the control source set to equal the displayed column of the combo. Since the textbox is bound to an expression it is automatically not editable. Place the combo and text box in exactly the same location. When editing a record is intended to be blocked simply display the textbox and set the combo to not visible and vice versa when the edit is allowed.

Another technique when a combo is locked and disabled is to cover the dropdown arrow with a rectangle so it doesn't invite the user to want to change it. Set the backgroundcolor of the rectangle to the same as the form.

 

[Galaxiom]

Notice you mentioned Null Or Zero, try this;

If IsNull(Me.FieldName) Or Me.FieldName = 0

A tidier syntax with the same result as:
If IsNull(Me.FieldName) Or Me.FieldName = 0

If Nz(Me.FieldName) = 0

In a similar vein, for detectecing both Null and the NullString in a text formatted control, the following expressions give the same result:

If IsNull(Me.FieldName) Or Me.FieldName = ""

If (Me.FieldName & "") = ""

If Len(FieldName & "") = 0

 

[John Big Booty]

In this situation I generally set a control to locked and disabled. Locked alone causes the control to be greyed out which doesn't always look good.

.....

Sorry just need to correct you on one point. The behaviour/look you describe is caused if the control's Enabled property is set to False, and the Locked property is set to False.

 

[Galaxiom]

Thanks John. You are quite correct. I got that back to front.

Would that be "Dispropertia"?

 

[John Big Booty]

Thanks John. You are quite correct. I got that back to front.

Would that be "Dispropertia"?

There's a reference in there, that has gone straight through to the keeper; I'm afraid

 

[Galaxiom]

There's a reference in there, that has gone straight through to the keeper; I'm afraid

It was mant to be a play on dyslexia and property.
Inadvertantly spelling it "dis" certainly didn't help.