Locking Fields

[Bentleybelle]

I have a form which, once certain data is entered, should not change, I therefore want to be able to lock these fields (one of which is the primary key) in order to prevent the user changing in error. If I set the Locked text box property to Yes, it does not allow me to enter a new record. If I set the Form property Allow Edits to No, it locks the whole form and does not allow the user to go back and complete other fields where the info was unknown at the original time. Any help please?

 

[RichO]

You need to use an event procedure to do this.

In the After Update event you can place the code:

Me.MyTextBox.Locked = True

 

[Bentleybelle]

I'm probably going to make myself sound dumb, but I assume I have to replace myTextBox with the field name; but what is represented by Me?
I would appreciate your help.

You need to use an event procedure to do this.

In the After Update event you can place the code:

Me.MyTextBox.Locked = True

 

[DazedandMuddled]

but what is represented by Me?

Me.something, the Me is the name of the form that your text box is on.

the Me can be deleted and it shouldn't affect your code

HTH

Cam

 

[RichO]

I assume I have to replace myTextBox with the field name

The Control Source (data tab) is where you select the field name. Name (other tab) is where you can name the control anything you like. Don't get these 2 properties confused. For best results, don't name your control with the same name as the control source. It will still work that way but it can cause confusion later on if you try to edit the control source in any way. I did alot of head scratching and hair pulling because of that when I started learning Access.

I like to use Me. because VBA can auto-complete the names of your controls to assure that they are properly typed.

 

[Bentleybelle]

I still don't have this quite right. Attached is screen shot of the code I have put in After Update. It has locked the field as I wanted (ClientID) but when I try to enter a new record, I get a message that Access can't find the macro. The ClientID is entered, but it doesn't automatically insert the Client Name, which it did previously??

The Control Source (data tab) is where you select the field name. Name (other tab) is where you can name the control anything you like. Don't get these 2 properties confused. For best results, don't name your control with the same name as the control source. It will still work that way but it can cause confusion later on if you try to edit the control source in any way. I did alot of head scratching and hair pulling because of that when I started learning Access.

I like to use Me. because VBA can auto-complete the names of your controls to assure that they are properly typed.

 

[Mile-O]

You have been given code; not an expression.

Where you have put the line is where you would put an expression. When that line is selected, a small box appears at the right handside with three dots on it. You click this and select Code Builder and, once the form module opens up, put the code between the two lines presented to you (Private Sub...... End Sub)

Also, you are referring to textboxes and NOT fields - there is a difference.

The Me refers to the instance of the form class.

You shouldn't even need the Autonumber on your form at all. Why not just set it's Visible property to False?

It's best to prefix the name of a textbox control with txt and then the name of the field. i.e. txtDateReceived, txtSurname as this helps you disambiguate and refer to both the underlying field or the control. Naming a control the same as an underlying field reduces your functionality.

Similarly, with other controls, you should use a prefix (i.e. cbo (combobox), cmd (command button) lst (listbox), chk (checkbox)

 

[Bentleybelle]

This isn't working! I have done as you suggested, but this allows me to make an update to one record, but then will not allow me to create a new record (presumably because it is locked after an update). There must surely be a way??

You have been given code; not an expression.

Where you have put the line is where you would put an expression. When that line is selected, a small box appears at the right handside with three dots on it. You click this and select Code Builder and, once the form module opens up, put the code between the two lines presented to you (Private Sub...... End Sub)

Also, you are referring to textboxes and NOT fields - there is a difference.

The Me refers to the instance of the form class.

You shouldn't even need the Autonumber on your form at all. Why not just set it's Visible property to False?

It's best to prefix the name of a textbox control with txt and then the name of the field. i.e. txtDateReceived, txtSurname as this helps you disambiguate and refer to both the underlying field or the control. Naming a control the same as an underlying field reduces your functionality.

Similarly, with other controls, you should use a prefix (i.e. cbo (combobox), cmd (command button) lst (listbox), chk (checkbox)

 

[Mile-O]

You'll still need to review the status of the textbox in the form's Current event:

i.e.

Private Sub Form_Current()
    If IsNull(Me.MyTextbox) Then
        Me.MyTextbox.Locked = False
    End If
End Sub

 

[Pat Hartman]

Except that you need to toggle the property and I would check for a new record rather than nulls.

Private Sub Form_Current()
    If Me.NewRecord Then
        Me.MyTextbox.Locked = False
    Else
        Me.MyTextbox.Locked = True
    End If
End Sub

 

[Mile-O]

Except that you need to toggle the property and I would check for a new record rather than nulls.

I forgot to toggle. Thanks.

However, I still think that testing for a Null rather than a NewRecord is the way to go unless the BeforeUpdate() event of the form won't let you save the record until there's an entry in this textbox.

 

[Bentleybelle]

Well, I've been playing about this for ages, and I think I have finally cracked it. It seems to be working as I want. I've learned quite a bit from this little episode, so thank you all very much

Except that you need to toggle the property and I would check for a new record rather than nulls.

Private Sub Form_Current()
    If Me.NewRecord Then
        Me.MyTextbox.Locked = False
    Else
        Me.MyTextbox.Locked = True
    End If
End Sub

 

[Pat Hartman]

After re-reading your original post I agree with Mile. If locking the field(s) depends on the value in a particular field then using the NewRecord property will not do what you need. You'll need to check the controlling field for nulls.

If IsNull(SomeField) Then
.....