Protecting data in forms

[esjadee]

I have a very simple database for which I was given some code to lock the fields in a form which have data in to stop users inadvertently or deliberately overwriting or amending existing data.

The only problem is the code also disables the command buttons I placed on the form to search, add a new record & close the application. Now the last two I can manage without but the search button is vital to enable users to check the records before adding new records.

I was going to attach the database so anyone who might be able to help could play with it but I'm not sure how to do it as .mdb file extensions are not valid apparently!

Anyway the code I was given is:
Private Sub Form_Current()
On Error Resume Next
Dim ctl As Control
Dim boo As Boolean
For Each ctl In Me
boo = IsNull(ctl)
ctl.Locked = Not boo
ctl.Enabled = boo
Next
Set ctl = Nothing
End Sub

I would really appreciate some help my original query can be found under http://www.access-programmers.co.uk/forums/showthread.php?t=77009&highlight=lock+data+forms

Cheers guys.

 

[BLeslie88]

Well.

Zip your file to be attached silly...

I will continue looking at the code. and reply if I get an idea.

 

[BLeslie88]

...

Bro... Why not just lock the fieldss you don't want overwritten, based on your explanation, and the fact that this is a simple db... Why such code.... I mean. Lock the fields...? No?

If not explain better when and why you need fields locked....

If certain users need to be able to edit those fields, you could unlock them when they enter the form...

 

[trucktime]

Locked

With the code you use all controls on the form are locked.
You can individually lock the fields on your form, by open going to the Data Tab in properties and set locked to: YES.
That way your command buttons still work, of cours you can disable the ones you do not wish to use.

You can ZIP your mdb for uploading, make sure it is not larger than 100K.
Compact the database if necessary.

 

[esjadee]

Oops silly me. That's what you get for being in a hurry! At least that's my excuse & I'm sticking to it.

The reason I need to lock the fields is that some of the people using the form know very little about access or databases and I have had instances of them overwriting data in a field not realising that this changes the record.

I've used a form as that is the easiest way to input the data. The student number is a primary field & it will be necessary to add data at a later date via the form.

So what I am looking for is a way of locking any fields with data in them (to prevent overwriting or deletion) but allowing empty fields to be completed as & when (& then locked).

If there is a better way of doing this I am open to suggestions. I have to say that I am obviously a very limited user of access & I don't know VB (bit of a draw back I am beginning to realise) but having started this I would really like to get something workable up & running (I sort of told my boss I could do it & then got out of my depth very rapidly). Any help or suggestions you have would be much appreciated.

Cheers

 

[trucktime]

Lock fields

In the OnCurrent Event of the form you could put the following code:

Private Sub Form_Current()
If IsNull(Me.Yourfield) = True Then
Me.Yourfield.Enabled = True
Else
Me.Yourfield.Enabled = False
End If

When the user opens the form or goes to the next record the field is locked in case it has data in it.
Of course you will need another form that will enable you to edit the locked fields, if needed.

Don't forget to replace Yourfield with the actual field name. And you can ad all fields that need to be locked.

I'm not sure if there is a better way to do this, but I think this will work just fine.