Non-editable field

[Donna Rae]

In a form, can a field be made non-editable AFTER it has been filled in by an
event procedure?

 

[Jeff Boyce]

Are you saying you wish to "lock" the field? You can set this property
(Locked) and/or a closely related property (Enabled) within an event
procedure.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Donna Rae]

Jeff,

What I have is a form with several fields that get automatically populated
through an event procedure. What I want to do is after those fields are
populated to not allow the user to change any of the data in those fields.
If I lock the field with the Locking property, then it will not populate the
field when the user begins to use the form.

Thanks for your quick reply.

 

[Klatuu]

The best combination to use for your controls is Enabled = No and Locked= Yes
That way, they will not be greyed out. They will look normal, but the user
cannot enter anything manually.
Now, setting the properties of a control this way only affects what a user
can do manually. You can still populate the controls programmatically.

 

[Donna Rae]

I changed the settings as you recommended but when the event procedure is
run, I get the following error mesage: "Run-time error '2110': Microsoft
Office Access cannot move the focus to the control (name of field).

Any other suggestions? Thanks!

 

[Klatuu]

Then your code is tryin to set the focus to a control that is not enabled.
Why would you set the focus to a control that can't be edited?

 

[Donna Rae]

Here's the code I'm using to populate several fields on a form. The user
will then enter other data on the same form, but sometimes will change the
prepopulated data.

Private Sub Title_Change()
Dim db
Dim sql
Dim rs
Dim s As String
s = Title.Text

APC.SetFocus
Set db = DBEngine.Workspaces(0).Databases(0)
Set sql = db.CreateQueryDef("", "Select APC, ProtocolID, Species, Breed,
Age, Wt, Sex, Special, Requestor, FundsAvailable, Remain, Housing from
AnimalOrder where Title ='" & s & "'")
Set rs = sql.OpenRecordset()
APC.Text = rs!APC

ProtocolID.SetFocus
ProtocolID.Text = rs!ProtocolID

Species.SetFocus
Species.Text = rs!Species

Breed.SetFocus
Breed.Text = rs!Breed

Age.SetFocus
Age.Text = rs!Age

Wt.SetFocus
Wt.Text = rs!Wt

Sex.SetFocus
Sex.Text = rs!Sex

Special.SetFocus
Special.Text = rs!Special

Requestor.SetFocus
Requestor.Text = rs!Requestor

FundsAvailable.SetFocus
FundsAvailable.Text = rs!FundsAvailable

Remain.SetFocus
Remain.Text = rs!Remain

Housing.SetFocus
Housing.Text = rs!Housing

End Sub

 

[Klatuu]

You don't need any of the SetFocus lines in your code. The only reason to
use a SetFocus is if you want the user to work with a specic control. Also,
are you naming your controls the same as the recordset fields? Although
allowed, it is not a good idea. It can be confusing for a human to know
which object you are addressing and in some cases, it can confuse Access.
Also, it is always best to qualify the names of your objects. When
referencing control on the current form, use can use Me. or Me! All your Dim
statements result in variants. There is no better way to really slow down
your application except for the next observation. The biggest issue here is
you are using the Change event. The change event fires on EVERY change, For
example if you want to enter "Foobar", it fires after you enter the F, then
after the first O, then after the next O, etc. You should be using the After
Update event. Here is a revised version:

Private Sub Title_AfterUpdate()
Dim rs As Recordset

Set rs = Currentdb.OpenRecordset("Select APC, ProtocolID, Species,
Breed, " & _
Age, Wt, Sex, Special, Requestor, FundsAvailable, Remain, Housing "
& _
" from AnimalOrder where Title ='" & Me.Title & "'")
With Me
.APC = rs!APC
.ProtocolID = rs!ProtocolID
.Species = rs!Species
.Breed = rs!Breed
.Age = rs!Age
.Wt = rs!Wt
.Sex = rs!Sex
.Speciall = rs!Special
.Requestor = rs!Requestor
.FundsAvailable = rs!FundsAvailable
.Remain = rs!Remain
.Housing = rs!Housing
End With
End Sub

Part of your problem may be that unlike VB, VBA only recognizes the Text
property when the control has the focus. When necessary, in VBA, you would
use the Value property, but it is usually not necessary. The default
property of a control is the Value property. so
Me.txtSomeControl.Value = "Bozo" and Me.txtSomeControl = "Bozo" are the same.
Me.txtSomeControl.Text only works when txtSomeControl has the focus.

Please understand I am not trying to beat you up, I am only trying to help.
Best of Luck and post back if you have more questions.

 

[Donna Rae]

Thank you very much. The change in code and Enabled/Locked settings did the
trick. I've had no VBA training and had just been trying to adapt an example
piece of code for my purposes. It actually worked quite well until I needed
this additional safeguard. Again, thanks!

 

[Klatuu]

Glad I could help, Donna.
If I may ask you a question. Is there any specific reason you are using an
unbound form?

 

[Dirk Goldgar]

In

Also, are you naming your controls the same as the
recordset fields? Although allowed, it is not a good idea. It can
be confusing for a human to know which object you are addressing and
in some cases, it can confuse Access.

Just in the interest of friendly debate, I'll challenge this statement.
In the context of control names, I don't think it's harmful and I have
never seen Access get confused. Can you provide an example to support
your assertion?

 

[Douglas J. Steele]

In

Just in the interest of friendly debate, I'll challenge this statement. In
the context of control names, I don't think it's harmful and I have never
seen Access get confused. Can you provide an example to support your
assertion?

If your control is named the same as the field in its control source, you'll
run into problems. For example, if you've got a field named CustomerName,
your control is named CustomerName and its ControlSource is =[CustomerName]
& IIf([Active], "", " (Inactive)")

Yeah, I know it's a contrived example, but if you started out with the
recordset field being the ControlSource and then decided to change it, you
can run into that situation.

 

[Richnep]

Jeff,

What I have is a form with several fields that get automatically populated
through an event procedure. What I want to do is after those fields are
populated to not allow the user to change any of the data in those fields.
If I lock the field with the Locking property, then it will not populate the
field when the user begins to use the form.

Thanks for your quick reply.

I just did this for a few fields in one of my new forms. Put this code
in the on change event of the field

If IsNull(Me.FieldName) = True then goto 200 Else Goto 100
100
MsgBox "You can not change the text in this box"
Me.Undo
Me.Refresh
200
EndSub

Just change Fieldname to the Name of the Field
This will pop up a msgbox that let's them know and then when they
click OK it removes the changes and does a refresh so the orig value
appears back in the box.

 

[Dirk Goldgar]

In

If your control is named the same as the field in its control source,
you'll run into problems. For example, if you've got a field named
CustomerName, your control is named CustomerName and its
ControlSource is =[CustomerName] & IIf([Active], "", " (Inactive)")

But that's not at all the same as a control with the same name as its
controlsource. That's a calculated field that creates a circular
definition. The controlsource '=[CustomerName] & IIf([Active], "", "
(Inactive)")' is obviously not the same as 'CustomerName'.

Can anyone provide a case where the simple fact that a control is named
the same as its controlsource has confused Access or cause it to fail?

Yeah, I know it's a contrived example, but if you started out with the
recordset field being the ControlSource and then decided to change
it, you can run into that situation.

Sure, but it's not a case of *Access* being confused -- it's the
*programmer* who was confused. So which is more cumbersome, renaming
all your controls so that they don't share the names of their bound
fields, or remembering to change the names of calculated controls on
those occasions when you create them from bound controls?

 

[Klatuu]

Yes, Dirk, I can. I was asked to work an an app where a field in the
recordset was named Date and the bound control was named Date. It had in the
code something like If Date > Date() Then
It returned unreliable results. I enclosed the reference in brackets so it
became
If [Date] > Date() and still got bad results. To get it to return the
correct results I had to code it as If [Date] > vba.Date.
--
Dave Hargis, Microsoft Access MVP

In

If your control is named the same as the field in its control source,
you'll run into problems. For example, if you've got a field named
CustomerName, your control is named CustomerName and its
ControlSource is =[CustomerName] & IIf([Active], "", " (Inactive)")

But that's not at all the same as a control with the same name as its
controlsource. That's a calculated field that creates a circular
definition. The controlsource '=[CustomerName] & IIf([Active], "", "
(Inactive)")' is obviously not the same as 'CustomerName'.

Can anyone provide a case where the simple fact that a control is named
the same as its controlsource has confused Access or cause it to fail?

Yeah, I know it's a contrived example, but if you started out with the
recordset field being the ControlSource and then decided to change
it, you can run into that situation.

Sure, but it's not a case of *Access* being confused -- it's the
*programmer* who was confused. So which is more cumbersome, renaming
all your controls so that they don't share the names of their bound
fields, or remembering to change the names of calculated controls on
those occasions when you create them from bound controls?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

In

Yes, Dirk, I can. I was asked to work an an app where a field in the
recordset was named Date and the bound control was named Date. It
had in the code something like If Date > Date() Then
It returned unreliable results. I enclosed the reference in brackets
so it became
If [Date] > Date() and still got bad results. To get it to return the
correct results I had to code it as If [Date] > vba.Date.

Neat example! BUT ... the problem there wasn't because the control was
named the same as the field, but because both were named the same as the
function.

 

[Klatuu]

Can you validate that?
You may be correct; but, The point is that the developer using a reserved
word caused the problem. So, my point stands that using reserved words can be
problematic. Good naming conventions make it easy for even us human types to
understand what is being done.

--
Dave Hargis, Microsoft Access MVP

In

Yes, Dirk, I can. I was asked to work an an app where a field in the
recordset was named Date and the bound control was named Date. It
had in the code something like If Date > Date() Then
It returned unreliable results. I enclosed the reference in brackets
so it became
If [Date] > Date() and still got bad results. To get it to return the
correct results I had to code it as If [Date] > vba.Date.

Neat example! BUT ... the problem there wasn't because the control was
named the same as the field, but because both were named the same as the
function.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Klatuu]

Better syntax would be:
If Not IsNull(Me.FieldName) Then
MsgBox "You can not change the text in this box"
Me.Undo
Me.Refresh
End If
EndSub

 

[Dirk Goldgar]

In

Can you validate that?

Not without setting up a demo table and form, which I don't feel like
doing and which wouldn't *prove* what was going on in your specific case
anyway. But it's obvious from the description of the problem and the
solution, isn't it?

You may be correct; but, The point is that the developer using a
reserved word caused the problem. So, my point stands that using
reserved words can be problematic.

Using reserved words is definitely problematic; I agree with that
statement wholeheartedly. But field names are not -- or should not
be -- reserved words. The statement I'm challenging is your original
one, that naming bound controls the same as their controlsources can be
confusing to Access. I don't believe this is true; Access is set up to
handle this with no problem. If it were true, then all those forms
created by the Form Wizard would not work reliably. But they do.

Good naming conventions make it
easy for even us human types to understand what is being done.

I agree with you there, too. However, people can and do disagree over
what constitutes a good naming convention. That could be a long
discussion, but I think we're all agreed that having some sort of naming
convention is helpful. One can certainly argue that it's best to name
all controls in such a way as to distinguish them from the fields to
which they are bound -- you could make a strong argument for that,
though I don't happen to agree. But I wouldn't justify that principle
on the basis that *Access* has a problem with control/field name
identity, because it doesn't.

 

[Dirk Goldgar]

In

For example, had the OP used the very common txtDate, the
problem would not have happened.

Au contraire. If the control were named "txtDate", but the field named
"Date" were still in the form's recordsource -- whether the control were
bound to that field or not -- the problem would still have occurred.
You'd still need to refer to VBA.Date to keep Access from understanding
"Date" as the field. Without any qualification of the name, Access will
find the field and control objects before the VBA function. So reserved
words shouldn't be used for control *or* field names.