By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,041 Members | 1,793 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,041 IT Pros & Developers. It's quick & easy.

lock a field after editing

P: 56
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
Expand|Select|Wrap|Line Numbers
  1. myAddress.setfocus
  2. myName.locked=true
  3. 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
Dec 22 '11 #1
Share this Question
Share on Google+
17 Replies


Rabbit
Expert Mod 10K+
P: 12,366
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.
Dec 22 '11 #2

NeoPa
Expert Mod 15k+
P: 31,494
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.
Dec 22 '11 #3

P: 56
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 (frmQuestionnaire) within frmClient. Tables holding the data are tblClient and tblQuestionnaire respectively.

The number of the response (i.e. first, second, etc) is selected using an option group ([CompletionNumber]). 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 tblQuestionnaire.

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

Expand|Select|Wrap|Line Numbers
  1. If Len(Me.[CompletionNumber]) > 0 Then
  2.    Me.[CompletionNumber].Enabled = False
  3.    Me.[CompletionNumber].Locked = True
  4. Else
  5.    Me.[CompletionNumber].Enabled = True
  6.    Me.[CompletionNumber].Locked = False
  7. 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
Dec 29 '11 #4

NeoPa
Expert Mod 15k+
P: 31,494
It seems to me that what would make better sense would be to determine the next value for [CompletionNumber] 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 [CompletionNumber]) 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?
Dec 29 '11 #5

P: 56
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. If Me.NewRecord Then
  3. [CompletionNumber] = [CompletionNumber] + 1
  4. End If
  5. End Sub
this makes [CompletionNumber] 1 for every record. I also tried using the DMax function but this simply incremented [CompletionNumber] for every record rather than for a new record for that client.

Any ideas how I can achieve this?

thanks
LG
Jan 4 '12 #6

NeoPa
Expert Mod 15k+
P: 31,494
Assuming you have the Client ID (I'll assume this is a field in both [tblClient] and [tblQuestionnaire] 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 [CompletionNumber] field (We'll call this [txtCompletionNumber]) based on the existing values in [tblQuestionnaire] for that client. I'll assume for now that the name of the sub-form control is [sfmQuestionnaire] (even though the form used within this sub-form control is [frmQuestionnaire] of course).
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.     Me.sfmQuestionnaire!txtCompletionNumber.DefaultValue = _
  3.         Nz(DMax(Expr:="[CompletionNumber]", _
  4.                 Domain:="[tblQuestionnaire]", _
  5.                 Criteria:="[ClientID] = " & Me.txtClientID), 0) + 1
  6. End Sub
The DMax() call finds the record with the maximum CompletionNumber 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 [txtCompletionNumber] 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 [txtCompletionNumber] will already be set and waiting only for the record to be saved away.
Jan 4 '12 #7

P: 56
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
Jan 9 '12 #8

NeoPa
Expert Mod 15k+
P: 31,494
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 ;-)
Jan 9 '12 #9

P: 56
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 [CompletionNumber] in frmQuestionnaire 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 frmQuestionnaire).

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

LG
Attached Files
File Type: zip YCS_WellbeingIndicator_08Feb2012.zip (121.7 KB, 56 views)
Feb 8 '12 #10

NeoPa
Expert Mod 15k+
P: 31,494
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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.     Me.subfrmWellbeing!txtCompletionNumber.DefaultValue = _
  3.         Nz(DMax(Expr:="[CompletionNumber]", _
  4.                 Domain:="[tblWellbeing]", _
  5.                 Criteria:="[ClientID] = " & Me.ClientCode), 0) + 1
  6. End Sub
I could not find any subform and I couldn't find any TextBox called txtCompletionNumber. 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 txtCompletionNumber. 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 frmQuestionnaire is a subform of (I would guess frmClient?), and which control on frmQuestionnaire 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.
Feb 9 '12 #11

NeoPa
Expert Mod 15k+
P: 31,494
I managed to find out that the [CompletionNumber] 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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.     Me.CompletionNumber.DefaultValue = _
  3.         Nz(DMax(Expr:="[CompletionNumber]", _
  4.                 Domain:="[tblWellbeing]", _
  5.                 Criteria:="[ClientID] = " & Me.ClientCode), 0) + 1
  6. End Sub
This code is in the module for [frmQuestionnaire] (which is where you had it anyway for some reason), not for [frmClient]. This explains why we have no need for any subform references.
Feb 9 '12 #12

P: 6
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...
Feb 9 '12 #13

P: 56
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.
Feb 29 '12 #14

TheSmileyCoder
Expert Mod 100+
P: 2,321
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current() 
  2.     Me.CompletionNumber.DefaultValue = _ 
  3.         Nz(DMax(Expr:="[CompletionNumber]", _ 
  4.                 Domain:="[tblWellbeing]", _ 
  5.                 Criteria:="[ClientID] = '" & Me.ClientCode & "'"), 0) + 1 
  6. End Sub
Note that ID fields should in 99.9% of case not carry any information.
Feb 29 '12 #15

P: 56
NeoPa and Smileycoder,

thanks very much - problem solved!!

NeoPa, re the earlier problems with the db I posted, there was no blatant disregard of the instructions intended - simply a lack of understanding which I'm sure is frustrating for you, but for me also.

As always thanks for teh help offered.

LG
Feb 29 '12 #16

P: 56
Smiley,

re your comment:

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

I'm not sure what you mean?? Can you explain?
Feb 29 '12 #17

NeoPa
Expert Mod 15k+
P: 31,494
LucieGiles:
NeoPa, re the earlier problems with the db I posted, there was no blatant disregard of the instructions intended - simply a lack of understanding which I'm sure is frustrating for you, but for me also.
Indeed Lucie. To be fair, I wasn't suggesting you were wilfully disregarding them, simply that you managed to miss a point that turned out to be important (They're all important one way or another of course). I appreciate that things can be confusing for people though, even though I have tried to make them as clear as possible. I should do by now, you're by no means the only poster to have slipped up.

As for Smiley's comment, I may be wrong, but I think he's referring to the fact that the ID is other than a simple numeric field with randomly assigned numbers identifying each record. In the absence of any information explaining what type of data you had in that field I had assumed the latter, as is the case for most ID fields. As it turned out that your ID field had a meaningful reference component, and was therefore textual, he suggested the change to handle the different situation.
Feb 29 '12 #18

Post your reply

Sign in to post your reply or Sign up for a free account.