"locking" and "protecting" old data

[jeffm3434]

Please help!!! I am a newbie to Access and VB... but got bribed into deciphering and fixing this old database.

Old database from my wife's work. We have recreated an access database and pulled in all of the old data. The old design only allowed a client company to have one employee. The new design allows them to enter in a client and then pick which company they work for. (Was a one-to-one relationship between company and client and now is a one to many relationship.) The issue is that now all of the old projects for this company are assigned to the company and whoever the current employee is set to for the company (they would change the employee name every time a new project came in from that company).

However, there are some records where they do only deal with one employee for a company. So those records are ok and should be kept "active". But in the other cases we need to be able to "freeze" these records and not allow the addition of any new projects (done as a subform on the client form) to this client record. This will need to be a one-time deal. So once these records are locked they should never have to unlock them... they do need them there for historical data and for reporting purposes. The plan is to change the client name for these records to something like "original company record" or something like that anyway. Then as a new project comes in... a new record will be added for the person and they will be linked to the company but all of the new projects will be added to this client... not to the original client/company record.

How do I make this happen? Again it will be done on a record by record basis and there will probably only be around 600 records or so that will need to be locked down. And no new records should come up that will be locked down.

Thanks for any info!!!

 

[Kraj]

There are a variety of ways you can do this according to what your needs are and how you access the information. For example, you said you only need the old records for historical purposes and will never update them. If you only access the records through reports, you could move all the old records into their own table and add that table into your queries. That way the database will still search that table but your data entry/editing forms will only access new records.

Another way you could do it is to add a Yes/No field to the table that indicates 'locked or unlocked'. Then, in your data entry/edit/view form you can add a bit of code that checks this field and if it is locked sets the form's mode to read-only.

Those are two suggestions. I'm sure there are more possibilities.

 

[jeffm3434]

Another way you could do it is to add a Yes/No field to the table that indicates 'locked or unlocked'. Then, in your data entry/edit/view form you can add a bit of code that checks this field and if it is locked sets the form's mode to read-only.

This sounds kinda like what I need to do. Will setting a form's mode to read-only apply to the subforms too? Each form has 4 subforms and those are the main places that I need to prevent data entry (on the 4 subforms).

If so... then what is the basic syntax of the code that I need to enter?

I am really new to Access and VB (although I have managed to hunt and "google search" my way through most of this project!!).

 

[Kraj]

This sounds kinda like what I need to do. Will setting a form's mode to read-only apply to the subforms too? Each form has 4 subforms and those are the main places that I need to prevent data entry (on the 4 subforms).

I'm not positive but I'd say 'yes' with 80% certainty. If not, you can always add the code on the subforms as well.

If so... then what is the basic syntax of the code that I need to enter?

I'm not your man for syntax; I'm terrible at it. A search of the VB forum should help and even the VB editor's help file is actually useful in situations like this. My suggestion is this: create a button on a form (doesn't matter where because you're not going to keep it). Use the wizard to set the button to open your form; when it prompts you for a mode, select "read-only". Now go into the button properties and look at the 'Event' tab. Where is says "[Event Procedure]", click on the '...' next to it to open the VB editor. There you will see the code Access wrote. Now you can copy that code and paste it into you own procedure. Add an IF statement that references the Yes/No field and assign it to the proper event (on the form you'd want it to trigger every time you move to a different record).

That's basically what you need to do. I probably won't be much help on the nuts and bolts part, but you should be able to do something along the lines of:

If Me.Yes_No_Field = True Then
Me.Mode = ReadOnly

End If

Add this to the "OnCurrent" event. Good luck!

 

[jeffm3434]

Thanks for your help...

This mostly worked. So far I am unable to get the actual sub-forms locked... but I did get the main forms locked and then made a label visible (with BIG red letters) only when locked that warns the user not to enter new data. That is probably all I really have to do. Not a perfect solution... but will work since there are less than 5 people using this system so I think the "training the user" method is easier here than the programming method (at least for this newbie!!!)

here's the code I used....

If Me.Lock = True Then
Me.AllowEdits = False
Me.AllowAdditions = False
Me.AllowDeletions = False
Me.WarningLabel.Visible = True
Me.WarningLabel2.Visible = True
Me.WarningLabel3.Visible = True
Me.WarningLabel4.Visible = True

Else
Me.AllowEdits = True
Me.AllowAdditions = True
Me.AllowDeletions = True
Me.WarningLabel.Visible = False
Me.WarningLabel2.Visible = False
Me.WarningLabel3.Visible = False
Me.WarningLabel4.Visible = False

End If

Again... Thanks for the help.

 

[Kraj]

Glad you got it working. To lock the subform, it should work if you use the same code but instead of "Me." you use "Me.subformname." That should work, although the syntax might need tweaking.