Lock certain fields in a form once data has been entered

[Hazel]

Can anyone help please !

I have a form with various fields, problem is that some fields need to be protected after information has been entered and no changes can be made.

Hazel

 

[missinglinq]

Private Sub Form_Current()
If Not IsNull(YourField) Then
  YourField.Locked = True
Else
  YourField.Locked = False
End If
End Sub

 

[David Eagar]

Just a thought - what happens if user enters incorrect data? They will now have no way of correcting it. Might it not be better to lock it down after user has signalled a 'I have reviewed data and it is corect' flag?

 

[missinglinq]

Good point! It's not locked until the user moves off of the new record. The whole point, however, is to not allow the user to make changes for any reason! Otherwise, what have you accomplished? I usually leave a "backdoor" for supervisors, admins, etc to use. I either use the Double-Click event of the textbox or of a label on the form to "unlock" the controls for correction by an authorized person. They should do this, of course, without the user seeing how it is done!

 

[David Eagar]

As a Network admin, in the process of complete mental disintegration from trying to tidy up from users incompetence - don't create extra work for yourself!!

Build it so that you have given them EVERY opportunity to verify the data that they have entered - If it's wrong, after that it is a USER problem NOT an admin problem

They had the responsibilty of entering accurate data, if they don't it is not an admin problem, it is a business decision of whether this user should be given access to the database

Harsh, yes, but in the long run, it is the right decision - if they can't even type in accurate data, what else in your organisation aren't they doing correctly? Selling Prices, discounts, business at any cost so my 'sales' figures look good???

If they don't know how to use a computer, DON'T GIVE THEM ONE - there was lot's pf profitable companies pre 1980's!!

However, as a db designer, your responsibity is to make it as user friendly as possible - create as many 'limit to list' combo boxes as you possibly can

NEVER underestimate the power of human stupidity - after all. the cream of our crop generated several WORLD WARS

 

[MarkK]

You can also write a custom wizard, an unbound standalone form that does validation, has lots of combos so users don't have to type, and allows them to cancel the operation. But I think giving certain people a way to edit the fields is also important. Errors happen and to have no way to recover from them, apart from firing someone, seems like a bad idea.

 

[David Eagar]

Errors happen and to have no way to recover from them, apart from firing someone, seems like a bad idea.

Yeah, I was having a bad day, but the main point I was trying make was that
a - make the database user friendly and construct it in a way to reduce the errors

b - If you have done all you can in a, then users must take responsibility for their actions - the worst are the ones who make the same mistakes over and over, no matter how many times you tell them

 

[Moniker]

Just an FYI, this:

Private Sub Form_Current()
If Not IsNull(YourField) Then
  YourField.Locked = True
Else
  YourField.Locked = False
End If
End Sub

easily becomes this:

Private Sub Form_Current()
    YourField.Locked = Switch(Nz(YourField,"")<>"",True, True, False)
End Sub

One line is easier to maintain than five lines.

 

[wazz]

hi. i'm trying to understand/learn the switch stmt.

i'm reading yours like this:

Switch(Nz(YourField), 'if fld is null then        ""<>"", 'not sure about this (not equal to, i.e. false, i.e. do nothing??)
       True,          'if fld is locked then:     True,   'keep it locked
       False)         'if fld is not locked then:         'do nothing

can you help me out with the first line? how do you put ""<>"" into words? i think ("" = ') ?? or maybe in this case ""<>"" means is null?

 

[Moniker]

YourField.Locked = Switch(Nz(YourField,"")<>"",True, True, False)

Nz is Null Zero. Therefore, if YourField = NULL, change it to an empty string. THEN do the comparison against an empty string. So, if it's not an empty string, there's something there, so lock the field, otherwise there's nothing there, so don't lock the field.

If that's still confusing, look up both the Nz Function and the Switch statement.

 

[missinglinq]

Post # 9 is exactly why I prefer

Private Sub Form_Current()
If Not IsNull(YourField) Then
  YourField.Locked = True
Else
  YourField.Locked = False
End If
End Sub

to this:

Private Sub Form_Current()
    YourField.Locked = Switch(Nz(YourField,"")<>"",True, True, False)
End Sub

It's much easier to understand!

 

[wazz]

tnx moniker, i misread the nz part.

 

[Moniker]

No problem. And, for posterity, Switch is a lot easier to understand when you are nesting. For example, if you are trying to determine a letter grade in a query, this is the IIF statement:

LetterGrade = IIf(NumGrade<60,"F",IIf(NumGrade>=60 And NumGrade<70,"D",IIf(NumGrade>=70 And NumGrade<79,"C",IIf(NumGrade>=80 And NumGrade<90,"B","A"))))

Versus:

LetterGrade = Switch(NumGrade<60,"F",NumGrade>=60 And NumGrade<70,"D",NumGrade>=70 And NumGrade<79,"C",NumGrade>=80 And NumGrade<90,"B",True,"A")

I'm not even sure if I got that massive nested IIf right because tracking down parentheses and such is a major hassle when nesting like that. With Switch, it's this:

Switch(Test1, ValueIfTest1=True, Test2, ValueIfTest2=True,... TestX, ValueIfTestX=True)

By ending a switch with "True, SomeValue" (like I did with True, "A"), that means that all the previous tests were false, and the last one will always enumerate to "True". If anything before that is true (a grade lower than A in this example), then the last "True" will never be evaluated.