Challenge...Locking only a particular record!

[amb3r]

Hi Guys,

I posted a message a few days ago regarding how to lock a particular record.
I received couple of replies but unfortunately it didn't really resolve my query.

I have a form linked to a table called "LiveEmployees". There are thousands of records which are updated (automatically and by users) on a regular basis. However, when an employee leaves etc, we need to lock that particular record so that the information stays as it is and no one can edit it!

I have read alot of information regarding this on various websites, MS Access help and on these posts. They all provide info on locking 'fields' or locking all records, which i can do. But what i want is to be able to lock only a particular record (e.g. lock only record '11' and record '35' from 150 records) rather than lock all the records.

Hope this makes sense!

I think the best way would be to create a command button (or a tick box etc) and write some code in VBA to achieve this. I’ve tried myself but am not getting anywhere!

Let me know if you have any queries.

I would really appreciate it if anyone could help/guide/advice me on this plz.

Thanks,
Regards,
Amber

 

[Karma]

I can think of a few ways of doing this.

1. Archive the data for that employee in another set of tables so that it no longer exists in the active tables but is not lost.

2. Add a yes/no check field in the table and then filter out the employees with the field set to no [or yes depending on what you call the field ]

3. Have an employee leave date field and allow edits/viewing where the field is null - same as above but actually shows some meaningful information.

HTH

K.

 

[workmad3]

Well, if your users can only update data through VBA code, then it is fairly easy... you create another table of data which consists of your locked records ID, and have your VBA edit commands only allow the edits through on records that are not in the locked records list.

If you are not doing everything through VBA, then you will probably need to filter the data out and have it viewable through non editable forms only.

 

[amb3r]

Karma/Workmad
Thanks alot for your replies.

The thing is, the database will be used by novice users. the database will contain thousands of records which will be updated using the form. Every now and then some records will need locking as we would no longer need to edit the records, but we will still need to access/view their data. therefore it is important all the records (locked and unlocked) stay on one form, as it will make it easier for all the users (99.9% of whom are very new to access) to view/edit.

I understand where you are coming from but for this purpose i think it would be much easier if all the records stay on the same form. It would be great if there was a way to lock individual records as and when required. I have seen this done before a long time ago - they had a tick box to lock/unlock that record, but the rest of the records were not affected. It would be great if i could have something similiar.

Thanks.
Amber

 

[Karma]

if you are confident that you will not need to edit the records in the future then add a yes/no field to the table and a check box on the form linked to that field.

In the oncurrent event of the form.

if me.chkbox_no_longer_employed = true then

me.AllowEdits = False

else

me.AllowEdits = True

end if

As the user moves between records the form will lock and unlock for editing depending on the status of the check field.
You could/should have some form of password override to unlock the record because once you have used the checkbox the only way to unlock it would be to go to the table and do it by manually as the checkbox itself will be locked as well.

HTH

K.

 

[amb3r]

Excellent!
It kind of works! Thank you very much.
I created a "yes/No" field called 'Locked'. If you tick this box on the form and use the above code then it doesnt allow you to edit that record but it allows you to edit the unticked ones. this is exactly what i wanted.

the only thing is though if you tick the 'Locked' field to lock a record then it doesnt allow you to edit any fields, which means it wont allow you to use the combo box to to move to another record! lol but i think i should be able to sort that out.

Thanks alot mate.

Amber

 

[Karma]

Excellent!
It kind of works! Thank you very much.
I created a "yes/No" field called 'Locked'. If you tick this box on the form and use the above code then it doesnt allow you to edit that record but it allows you to edit the unticked ones. this is exactly what i wanted.

the only thing is though if you tick the 'Locked' field to lock a record then it doesnt allow you to edit any fields, which means it wont allow you to use the combo box to to move to another record! lol but i think i should be able to sort that out.

Thanks alot mate.

Amber

Rather than lock the whole form you could lock individual objects but if you have a lot of objects it can be a bit of a pain.

if me.ch..... = true then

me.txt_username.locked = true
me.txt_... etc

else

me.txt_username.locked = false
me.txt_... etc

end if

 

[KeithG]

Instead of locking the controls you could always set the enabled property to false. So instead of just being locked the fields would be grayed out but you can still view the data. That way it is more apearent to the user that they can't modify the data.

 

[amb3r]

Thanks alot for your help guys.

I had to set each individual fields Enabled property to false. It took me a while as I've got 40+ fields but i got there in the end!!

Thanks for all your help again guys.

Amber