Disallow/allow editing of fields in a “single” record?

[MikeFL]

How can I disallow/allow editing of fields in a “single” record of a many records database?

Situation:

I’ve designed a database in MS Access 2003 that has many records, and each individual record “number” has many data entry fields. I want to be able to“disable (not allow)” users from entering or editing any data in certain fields when they have finished entering all the required data.

What I need:

I would like to use a Yes/No ComboBox field (password protected) located in each individual record that will allow me to change it to “Yes” to allow editing of certain fields, or “No” that will disallow editing of certain fields.

Is this possible, and if so can someone explain to me how to do this or what code I need to use?

 

[PHV]

Use the Current event procedure and the NewRecord property of the Form object to set the Enabled or Locked property of the relevant controls.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CaptainD]

Welcome the the forum.

You need to provide more information on what you have and what you have tried.

What I get from your post I assume you have a form bound to the database and you want to disable some of the text boxes from being edited once the person save what they have entered.

With that, do you want some fields to remain "Editable" or do you want the form to only allow "New Records"?

Do you want the user to see existing records at all?

There are different mthods you can use to set properties, you need to decide what you want them to be able to do and not to do.

Then you need to decide when you want to empliment the changes following some event. (When the form opens, On Save, when someone exits a textbox or clicks a button)

Same applies to setting "Access Rights". You need some means to set an access level. An example might be a keystroke combination to pop up an inputbox where you whould enter a password that sets your access level and each event that sets a property would first look at your access level. If you level is (We'll use 9) 9 then exit the routine that would prevent the editing of records, otherwise, it would prevent editing.

It helps here to show what you have tried and why you were trying do do what you did. Then you will get help fixing it.

If you are just trying to have the people here do your work for you, you will not get much help.

 

[TheAceMan1]

How are ya MikeFL . . .

For locking, use the forms [blue]On Current[/blue] event along with detection of wether the textbox(s) contain data. Example:

[blue]Private Sub Form_Current()
   If Nz(Me![purple][b]TextboxName[/b][/purple], "") = "" Then
      Me![purple][b]TextboxName[/b][/purple].Locked = False
   Else
      Me![purple][b]TextboxName[/b][/purple].Locked = True
   End If

   If Nz(Me![purple][b]AnotherTextboxName[/b][/purple], "") = "" Then
   
   '
   '
   '

   If Nz(Me![purple][b]AnotherTextboxName[/b][/purple], "") = "" Then
End Sub[/blue]

User has full editing control until the record is saved.

See Ya! . . . . . .

Be sure to see thread181-473997

 

[MikeFL]

CaptianD

First thanks for your quick response.

To answer some of your questions:

1. You need to provide more information on what you have and what you have tried.

I have not tried anything yet since I just don’t know where to start.

2. What I get from your post I assume you have a form bound to the database and you want to disable some of the text boxes from being edited once the person save what they have entered.

The form is bound to the database via a query.

3. With that, do you want some fields to remain "Editable" or do you want the form to only allow "New Records"?

Once someone has entered all the necessary data into a single record forms fields and field CompPrec = 100%, and field DateComp = Is Not Null all the fields in this form become none-editable for security purpose so they can’t be altered unless I the admin un-lock them.

I would like to use a Yes/No ComboBox field located in the password protected forms tab that is now there which will allow me to change it to “Yes” to allow editing of all fields, or “No” that will disallow editing of all fields.

If editing/updating has to be performed later I the admin change the Yes/No ComboBox to “Yes” to allow editing of all fields, and back to “No” that will disallow editing of all fields.

4. Do you want the user to see existing records at all?

Yes, they can at any time query/filter any record to look at or print.

5. There are different mthods you can use to set properties, you need to decide what you want them to be able to do and not to do.

Please view #3 answers.

6. Then you need to decide when you want to empliment the changes following some event. (When the form opens, On Save, when someone exits a textbox or clicks a button).

Please view #3 answers.

7. Same applies to setting "Access Rights". You need some means to set an access level. An example might be a keystroke combination to pop up an inputbox where you whould enter a password that sets your access level and each event that sets a property would first look at your access level. If you level is (We'll use 9) 9 then exit the routine that would prevent the editing of records, otherwise, it would prevent editing.

I don’t understand.

Hope this helps!

 

[MikeFL]

TheAceMan1

First thanks for your quick response.

I’ve not had a chance to try what you suggested yet, but I will this week when I get home from work.

Can you look at my response to CaptianD today and let me know if what you suggested will work ok from what I responded to him. At this point in time I don’t know if yours or his suggestion will work best for me in my situation.

Again thanks!

 

[MajP]

Maybe

Public Sub lockUnlock()
  If Me.precComp <> 1 Or IsNull(Me.dateComp) Or glblAdmin Then
    Me.AllowEdits = True
    Me.AllowDeletions = True
  Else
    Me.AllowEdits = False
    Me.AllowDeletions = False
  End If
End Sub

Private Sub dateComp_AfterUpdate()
  Me.Dirty = False
  Call lockUnlock
End Sub
Private Sub Form_Current()
  Call lockUnlock
End Sub
Private Sub precComp_AfterUpdate()
  Me.Dirty = False
  Call lockUnlock
End Sub

In a standard module I declare the public variable "glblAdmin", and set this to true if the administrator is logged in.

 

[TheAceMan1]

MikeFL . . .

No my post origination won't do.

To make things easier for yourself, why not [blue]allow full rights for admins[/blue] and adding new records only to all others (for now were talking this form only). This would relieve you of those buttons and any form for prompting. It could be as simple as:

[blue]Private Sub Form_Load()
   If CurrentUser = "Admin" Then
      Me.AllowEdits = True
      Me.AllowDeletions = True
   Else
      Me.AllowEdits = False
      Me.AllowDeletions = False
   End If
End Sub[/blue]

For validating a new record, put a question mark in the [blue]Tag[/blue] property of all controls that must have date (no quotations please). The copy/paste the following to the forms [blue]BeforeUpdate[/blue] event:

[blue]   Dim Msg As String, Style As Integer, Title As String
   Dim dl As String, ctl As Control
   
   dl = vbNewLine & vbNewLine
   
   For Each ctl In Me.Controls
      If ctl.Tag = "?" Then
         If Nz(ctl, "") = "" Then
            Msg = "No Data Entry for " & ctl.Name & "!" & dl & _
                  "You can't save a record until all " & _
                  "required fields have data . . ."
            Style = vbInformation + vbOKOnly
            Title = "Missing Data Error! . . ."
            MsgBox Msg, Style, Title
            Me(ctl.Name).SetFocus
            Cancel = True
            Exit For
         End If
      End If
   Next[/blue]

User will not be able to save until all required data entry is met.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997

 

[TheAceMan1]

MikeFL . . .

Hit submitt too soon.

The form is [blue]Locked[/blue] by default!

See Ya! . . . . . .

Be sure to see thread181-473997

 

[MikeFL]

To: TheAceMan1, CaptianD & MajP:

I think MajP is the closest to what I trying to accomplish.

Let me try to simplify what I’m asking help on again since maybe I didn’t do such a good job and I’m confusing you all:

This is a Technical Assistance Request database program. All its users are admin’s entering necessary data to complete a Technical Assistance Request that was assigned to them.

The main reason for not allowing field data editing after a Technical Assistance Request has been completed/resolved is to prevent accidental deletions and/or changes to data in files during the filtering/searching of records that continues to cause data integrity problems that are not always found and corrected.

I would like the forms No-fields editing action to be triggered by the [CompPrec] field being = 100% (Is 100% complete).

Then I would please like a way to turn back on the forms fields editing should it become necessary by (me) by changing the [CompPrec] field data to 0% completed or some other way if possible. Then when I re-enter 100% in the [CompPrec] field the froms No-fields editing action is turned back on.

Is this possible?

 

[CaptainD]

Did you see the part about "glblAdmin" in MajP's post?
If glblAdmin is "True" then the forms fields are Un-Locked and you can edit or delete the record.

There are various methods you can use to set "glblAdmin" to true or false. The fact that at work each person is an administrator for general purposes does not mean they are an administrator for your program.

If you don't want to use the persons login rights, use a command button that prompts with a password. You can use that to unlock individual records.

 

[MikeFL]

CaptianD:

I’m using a very simple program login password code for all administrators assigned to handle Technical Assistance Requests to keep things simple (View code below), and for filtering/viewing request records later if necessary.

Option Compare Database
Option Explicit
Sub NoEntry()
MsgBox "You entered the wrong password!"
End Sub

Private Sub cmdOK_Click()
If Me.txtPassword = "tcadmin1" Then
    SS_OpenForm "FO-QUsq-TA-000"
    DoCmd.Close acForm, "FO-LogonRequiredScreen"
    DoCmd.Close acForm, "FO-MainPOS"
    DoCmd.OpenReport "Rpt: Requesters QP", acViewPreview, "", "", acNormal
    DoCmd.OpenForm "FO-QUSel-(OCCDates)2", acNormal, "", "", , acNormal
Else
    NoEntry
End If
End Sub

Maybe your right, I’ve made this issue to complicated and a command button that prompts me with a password to un-lock and to re-lock individual records data after they have been completed/resolved and reviewed by me would be the best and easiest way to go.

Could you please help me in writing such a code that I can use under the command button, I'm not very good at doing it?

 

[CaptainD]

This is not tested but it should point you in the right direction

Create an *.ini file in Notepad and enter just the password and save it to some location
For simplicity we'll put it in the root folder and call it MyPassword.ini"

I assume you did not create a module and if you are doing this all from one form you can place the glblAdmin in the form. (I would also make it private)

At the top of the form (General Declarations) enter something like this:

 Private bAdmin as Boolean

(I changed glblAdmin to bAdmin and you would need to do that in the LockUnlock Sub)



Place a command button on the form and we can call it "cmdUnlock" or something along those lines

In the cmdUnlock_Click() event try something like this:

Private Sub cmdUnlock_Click()

Dim sPathAndName As String
Dim sPassword As String
Dim iNum As Integer

On Error GoTo ErrHandler

'Read the file
sPathAndName = "C:\MyPassword.ini"
iNum = FreeFile
Open sPathAndName For Input As iNum
Input #iNum, sPassword
'sPassword now holds the password from the text file
Close iNum
'Now ask for a password

If InputBox("Password required! Please enter a password.", "Attention") = sPassword Then
    bAdmin = True
Else
    bAdmin = False
End If

Call LockUnlock

Exit Sub
ErrHandler:
    MsgBox "Error Unlocking the form. Error # " & Err.Number & ", " & Err.Description, vbOKOnly, "Error"
End Sub

Note: Doing it this way, the password is not case sensitive

Hope that helps