Protecting Specified Fields in Continuous Forms

[ErikRP]

I have a continuous form which users update periodically. Depending on the value of one of the fields, NOTES, other fields (FIELD_1, FIELD_2 and FIELD_3) should not accept data.

I have toyed with the idea of making the field invisible if NOTES is equal to a certain value. On continous forms though this is a lot easier said than done!

Any ideas?

 

[David R]

Two thoughts come to mind

1) You're right, making continuous form fields Visible = False is difficult, to say the least. You can Disable the field, and/or Lock it, to prevent data entry. What will you do if the field already has data in it before you Disable it, however?

2) Why do you have three fields with numeric values at the end? Unless that was just a silly example, you shouldn't have repeating groups in a form; make a one-to-many relationship instead.

 

[ErikRP]

Actually the field names are Investigation, Payment and Communication. Each are combo boxes.

If the Notes field contains 'NE' (for 'No Error') then none of fields (Investigation, Payment or Communication) should allow input.

If Notes contains 'E' (for 'Error') then at least one of the fields should be able to display the type of error (i.e. what type of Investigation error, Payment error or Communication error).

 

[ErikRP]

I think I have answered my own question, and it seems to be working - all the better. If anyone has any other suggestions, please let me know!

What I did was made the control source as follows:

=IIf([Notes]='NE',Null,[Payment])

So if Notes is 'NE', i.e. there was no error, make the field contents Null. Otherwise use the value of Payment. Like I said, it seems to be working, but if this could cause problems, let me know!


UPDATE

I spoke too soon. Now I can't add anything when I do want to use the combo box. I get the message at the bottom of the screen: Field is based on an expression and can't be edited

 

[David R]

That will work..

But only for displaying existing records. When you go to add a new record/change an error status, you'll see the comment "You cannot change the value of this field; it is bound to the expression "=IIF(..."

I would use the BeforeUpdate event of the form to check the values. But I would also structure the data a little differently.

[Notes] could be an [Error] checkbox: Yes/no (-1/0)

Instead of three fields for error descriptions, make two fields: [ErrorType] and [ErrorDetail]. [ErrorType] will be either "Communications", "Payment", or "Investigation". ErrorDetail can list the specific types now split into three fields. The benefit of this is if you ever have to add a type, say, "Administrative", then you don't have to rework your forms, queries, etc, completely. You just add the entries to your comboboxes and away you go.

 

[ErikRP]

I'll give that a try.

Thanks too for the suggestions about modifying the structure, however I can't change the number of fields, the information in the fields or anything like that. I was hoping to come up with a simple protection for these few fields. Unfortunately to make any changes to the structure would involve much more work than necessary.

 

[David R]

"can't" is a big word.

If you "can't" change anything because you're getting the tables from another source and are not allowed to modify them, then yes, you're stuck with an inefficient system.

However if you "can't" change them because you think it would be too much work, consider this: I started with a badly unnormalized database my boss created and I took over maintenance of. At the time it had about 200 records in it. When I learned database normalization here, I had to fix about 500 records. When my boss finally gave me permisison to fix ALL of the normalization problems with the database in order to get the figures and functionality our office wanted, it took me a week and a half to fix the 3000 records we had by then (and that's just the main table!).

I leave it up to you to decide if you still "can't" fix it, but you'll save time in the long run if you "can".

 

[ErikRP]

This is one ugly database - no question. It's been a work in progress and the progress has an end date - today. Personal pride forbids me to post a copy of the database, but hopefully one day I will look back and recognize, and be able to fix, all of the problems that it has. It will take some major re-work to fix some of its issues, but that will have to wait for now.

Anyway, it's getting off topic but thanks for your suggestions!