lock a field after editing

[luciegiles]

Hi all,

Last year I asked the question is it possible to lock a field after editing.

Lysander (thanks Lysander) gave me the following code:

Then in the After_Update event of myName put the following code

myAddress.setfocus
myName.locked=true
myName.enabled=false

I have used this this morning (in a different project to the original question) and it works while I'm still in the record. However when I exit the record and re-enter it the field can be edited again. Is there a way I can lock this field so that it can never be edited. I don't really want to lock the whole record if I can help it.

thanks

LG

[Rabbit]

You can use the on current event of the form or detail section (I can't recall which) to check if there's a value in the field. And if there is, lock it.

[NeoPa]

Essentially, and technically, No. That is neither possible, nor does it make sense when you realise that you are not locking a field that way, but a control.

The code is fine for locking out the control, but locking a field is something you'd have to manage every time the form is opened. Thus, you'd need logic to determine the locked status of the control for whenever the field is displayed. To lock a value in a field in a particular record/records you'd need clear logic as to how to recognise which records should have the locked status for that control.

To go forward sensibly we (Not only us but particularly you) would need to understand that logic.

PS. If your logic matches the logic Rabbit has guessed at, then the Current event of the form itself is indeed how you would go about it.

[luciegiles]

Rabbit, NeoPa,

Thanks for the responses.

The reasoning behind this:

I have a db used to collect questionnaire responses. The questionnaire is to be repeated up to four times with the same individuals over a period of several weeks. Thus the client details are held in a form (frmClient) with the questionnaire responses in a subform control (frmQuestionnai re) within frmClient. Tables holding the data are tblClient and tblQuestionnair e respectively.

The number of the response (i.e. first, second, etc) is selected using an option group ([CompletionNumbe r]). I don't want the client to be able to edit the option group when returning to complete a second (or third etc) questionnaire, as this will overwrite the data in tblQuestionnair e.

I have used the following code in both the AfterUpdate event of [CompletionDate] (the next field on the form after [CompletionNumbe r]) and in the OnCurrent event of frmQuestionnair e.

If Len(Me.[CompletionNumber]) > 0 Then
   Me.[CompletionNumber].Enabled = False
   Me.[CompletionNumber].Locked = True
Else
   Me.[CompletionNumber].Enabled = True
   Me.[CompletionNumber].Locked = False
End If

Not sure if that answers your question re logic NeoPa but hopefully clarifies what I am trying to do. The above seems to work but happy to take advice.

all the best and happy new year

LG

[NeoPa]

It seems to me that what would make better sense would be to determine the next value for [CompletionNumbe r] automatically for new records and never allow the operator anything but view access to this value. That would involve determining and setting the value (of [CompletionNumbe r]) whenever the [ClientID] value is changed and .NewRecord is True. Clearly, [ClientID] should not be changeable unless .NewRecord were True.

Does that make the whole situation clearer and simpler?

[luciegiles]

NeoPa,

I like this suggestion as it takes away the need for the user to input anything and therefore eliminates data entry errors. However despite spending a fair bit of time trying to figure out how, I cannot make this work.

I have tried the following in the subform:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
[CompletionNumber] = [CompletionNumber] + 1
End If
End Sub

this makes [CompletionNumbe r] 1 for every record. I also tried using the DMax function but this simply incremented [CompletionNumbe r] for every record rather than for a new record for that client.

Any ideas how I can achieve this?

thanks
LG

[NeoPa]

Assuming you have the Client ID (I'll assume this is a field in both [tblClient] and [tblQuestionnair e] and the control on the main form for this is called [txtClientID]) available, then we can simply set the .DefaultValue of the subform's control for the [CompletionNumbe r] field (We'll call this [txtCompletionNu mber]) based on the existing values in [tblQuestionnair e] for that client. I'll assume for now that the name of the sub-form control is [sfmQuestionnair e] (even though the form used within this sub-form control is [frmQuestionnair e] of course).

Private Sub Form_Current()
    Me.sfmQuestionnaire!txtCompletionNumber.DefaultValue = _
        Nz(DMax(Expr:="[CompletionNumber]", _
                Domain:="[tblQuestionnaire]", _
                Criteria:="[ClientID] = " & Me.txtClientID), 0) + 1
End Sub

The DMax() call finds the record with the maximum CompletionNumbe r value of all those with the same ClientID value as found on the main form's [ClientID] control. The Nz() call around that translates the Null value for any clients with no existing records to a value of zero (0). One (1) is added to the result and this is set as the .DefaultValue of the [txtCompletionNu mber] control on the sub-form. If any existing records are viewed this has no effect, but whenever a new one is added the (unchangeable) value for [txtCompletionNu mber] will already be set and waiting only for the record to be saved away.

[luciegiles]

Hi NeoPa,

I just can't make this work; I wish I could give you more of an insight as to why! For the timebeing I am giving up on this and going with the option I outlined above - I'll come back to this when I have some time.

in the meantime thanks for the help
LG

[NeoPa]

OK Lucie. Thanks for the heads-up. When you come back to it you may want to consider attaching a (potentially) sanitised version of your database (See Attach Database (or other work)) for me to have a look at for you. I expect I will be able to find the isuue somewhat more quickly and easily than you could. I'm happy to try at least ;-)

[luciegiles]

Hi NeoPa (and others),

I have returned to the problem I was having trouble with a few weeks back. You might remember I was trying to go with your suggestion of making the field [CompletionNumbe r] in frmQuestionnair e automatically increment by one for each new record entered under a single [ClientID]. I have tried the code you suggested above but am getting an error (this code is still in the on current event of frmQuestionnair e).

The db is attached as requested - thanks for any help you can offer.

LG

[NeoPa]

The first problem is that you have not followed the instructions correctly. The database posted doesn't have Option Explicit set, which is a big problem for me, as I could spend large chunks of time chasing down problems that are unnecessary, and could be found easily with that setting. Nor does the project compile - even without that tighter setting. For future reference, please read and follow the instructions (I'll include the link again - Attach Database (or other work)) more carefully.

When I tried to look at the database in more detail, I found that there were, indeed, errors related to items that didn't exist. Your code in the form's Current event procedure is essentially :

Private Sub Form_Current()
    Me.subfrmWellbeing!txtCompletionNumber.DefaultValue = _
        Nz(DMax(Expr:="[CompletionNumber]", _
                Domain:="[tblWellbeing]", _
                Criteria:="[ClientID] = " & Me.ClientCode), 0) + 1
End Sub

I could not find any subform and I couldn't find any TextBox called txtCompletionNu mber. I was also able to determine that the form had no subform controls at all and that nothing had a name of either subfrmWellbeing or txtCompletionNu mber. I guess that explains some of your problems, but doesn't leave me much to work with I'm afraid.

Perhaps if you tell me which form frmQuestionnair e is a subform of (I would guess frmClient?), and which control on frmQuestionnair e it is that you want the code to set the default for, then I could possibly suggest a solution. I don't think this is easy for you. Clearly there is much confusion. We'll see what we can manage anyway.

[NeoPa]

I managed to find out that the [CompletionNumbe r] control is an Option Group. That came out of the back-field. Nevertheless I managed to make some code that might work. Unfortunately, when you created the data base following the very clear instructions I left for you, you managed to leave out the point about being able to reproduce the problem (the database has no client data in it at all so cannot even test the code out) as well as the instructions on how to go about doing so.

Here is the code anyway. You can test it I expect, even if I cannot :

Private Sub Form_Current()
    Me.CompletionNumber.DefaultValue = _
        Nz(DMax(Expr:="[CompletionNumber]", _
                Domain:="[tblWellbeing]", _
                Criteria:="[ClientID] = " & Me.ClientCode), 0) + 1
End Sub

This code is in the module for [frmQuestionnair e] (which is where you had it anyway for some reason), not for [frmClient]. This explains why we have no need for any subform references.

[JackieBolinsky]

Hello...
One way to deal with this is to have a button that toggles a flag_Locked field. Then, create a rec access rule for that priv set that edit is allowed when flag_locked ≠1. Another suggestion. I often "bury" sensitive fields on a record in a popup window with a Done button. This way, the user must click an Edit button to access these fields. This technique is especially useful if you need to run validation when they click Done or only allow certain priv sets to edit these fields.

You have got to realize that 'disabled' doesn't mean or equate to 'readonly'. Both these properties disallow the user from editing the entered text but the real difference comes when you try retrieving the form element values at the server. If the element is marked with the property 'disabled', the form element value won't be submitted to the server while a form elements' value marked 'readonly' will be.

Jackie...

[luciegiles]

Hi NeoPa,

I tried the code you offered and I get the following error when I try to add a new client:

Run time error '3075':

Syntax error (missing operator) in query expression '[ClientID] = '

Or this when trying to add a new record to an existing client:

Run time error '3075':

Syntax error (missing operator) in query expression '[ClientID] = 111LG'

where 111LG is the ClientID.

Not sure if that means anything to you.

[TheSmileyCoder]

I suggest you take a look at:
Quotes and double quotes: Where and when to use them

A modified example of NeoPa's code is shown below:

Private Sub Form_Current() 
    Me.CompletionNumber.DefaultValue = _ 
        Nz(DMax(Expr:="[CompletionNumber]", _ 
                Domain:="[tblWellbeing]", _ 
                Criteria:="[ClientID] = '" & Me.ClientCode & "'"), 0) + 1 
End Sub

Note that ID fields should in 99.9% of case not carry any information.