lockone field until another field is completed (1 Viewer)

rainbows · May 29, 2018

hi,

I have a form called " issue details" and in that form a have a field called " action taken " and another called " date closed "

what I would like to be able to do is not allow anyone to put a date in the "date closed" field until the have completed the "action taken field "

is it possible to close the date field until the data is put into the other field

thanks

steve

bob fitz · May 29, 2018

Set and save the enabled property of the Date Closed text box to no.
In the forms On Current event and in the After Update event of the Action Taken text box put some code to test for an entry and then set the Enabled property of the date text box appropriately.

bob fitz · May 29, 2018

Better still: Put the validation code in a sub procedure and call that sub procedure from the two events mentioned in my earlier post

sonic8 · May 29, 2018

rainbows said:
what I would like to be able to do is not allow anyone to put a date in the "date closed" field until the have completed the "action taken field "

What about using a table level validation rule?
No VBA required, just a single line of declarative SQL Code in the table property Validation Rule:

Code:

[date closed] IS NULL OR [action taken] IS NOT NULL

rainbows · May 29, 2018

thanks

I put this is the date closed field in the table " [action taken] Is Not Null" and got a error saying

Invalid SQL syntax
cannot use multiple columns in a column level check constraints

thanks steve

isladogs · May 29, 2018

sonic8 said:
What about using a table level validation rule?
No VBA required, just a single line of declarative SQL Code in the table property Validation Rule:

Code:

[date closed] IS NULL OR [action taken] IS NOT NULL

The problem with that is you can't edit the date closed value after first entry.

bob fitz · May 29, 2018

sonic8 said:
What about using a table level validation rule?
No VBA required, just a single line of declarative SQL Code in the table property Validation Rule:

Code:

[date closed] IS NULL OR [action taken] IS NOT NULL

I don't think that kind of validation can be done at the table level which is why I feel it should be done within the form

sonic8 · May 29, 2018

rainbows said:
Invalid SQL syntax
cannot use multiple columns in a column level check constraints

I was talking about a table level constraint. You use the Property Sheet on the right side of the table designer to enter that.

ridders said:
The problem with that is you can't edit the date closed value after first entry.

Of course you can! Why shouldn't you?

isladogs · May 29, 2018

Hi sonic

I also hadn't realised what you meant by a table level constraint, used the field validation & got the same error as the OP.
So I tried it in the form instead & it wouldn't let me edit it.

Must have done something wrong as it works correctly now. Apologies

rainbows · May 29, 2018

HI

putting that in the properties in the table did work thank you all

Pat Hartman · May 30, 2018

Is Not Null

Is SQL syntax NOT VBA syntax. In VBA, you would use IsNull().

I'm confused about what you ended up doing so I'm going to make another suggestion.

Locking/unlocking fields requires code in multiple events and the code is similar or identical. Rather than spreading code all over the place, you can simply trap the situation in a single place - the form's BeforeUpdate event. You don't actually care if someone tries to change something they shouldn't change, what you care about is that they do not succeed. So in the BeforeUpdate event, you would check the contents of the various fields and if the user is not allowed to update a field at this time, you display a message, cancel the update, undo the change and exit the sub.

Code:

If .....
    Then
        Msgbox "You are not allowed to enter a value in ... at this time.  The value has been removed.  Save again to continue.",vbONOnly
        Me.somefield.Undo
        Cancel = True
        Exit Sub
End If

lockone field until another field is completed (1 Viewer)

rainbows

Registered User.

bob fitz

AWF VIP

bob fitz

AWF VIP

sonic8

AWF VIP

rainbows

Registered User.

isladogs

MVP / VIP

bob fitz

AWF VIP

sonic8

AWF VIP

isladogs

MVP / VIP

rainbows

Registered User.

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread