field level control

[Dave]

Hi

I'm using Access 2003 on Windows XP. I want the program to enable/disable
fields in a record on a subform dependent on the user rights. I am writing
the code in the subform code module. Can't seem to find the correct syntax
to disable some fields in the record and not others. Each record has the
opportunity to either be enabled or not based on the user. I can set the
AllowAdditions or AllowEdits for each record on the subform when appropriate,
but there are cases when a user is required to edit a value in a single
field, but not the remaining fields.

I have tried variants of the following syntax:
Me.Recordset.Form.Fields("Quantity").Enabled = False
Me.Recordset.Fields("UnitMeas").Enabled = False
Neither seems to be working. Your help is much appreciated.

Dave

 

[Allen Browne]

You can disable only controls, not fields.

Try:
Me.Controls("Quantity").Enabled = False

Add error handling in case Quantity has focus with the code runs, or test
me.ActiveControl.Name to see if it is Quantity, and SetFocus to something
else.

 

[Dale Fye]

Dave,

When I'm implementing this kind of thing, I generally put the code in the
current event of the SUBFORM. That way, as soon as a new record is selected
in the subform, it will enable/disable or lock/unlock specific controls,
based on user permissions.

Private Sub Form_Current

me.txt_Quantity.Enabled = Me.NewRecord OR fnCanEdit()
me.txt_UnitMeas.Enabled = Me.NewRecord OR fnCanEdit()

Exit Sub

You need to be careful what logic you use to enable or disable controls. I
usually enable the controls if it is a new record, and then disable them on
subsequent returns to the record if the user does not have edit permissions.

I generally create functions to store and retrieve the value of specific
user permisions. There are a variety of reasons for doing this instead of
using a global variable, but the most important two are:
1. the function tends not to lose its value if the application encounters
an unhandled error, and
2. you can use functions in queries, but cannot use variables.

I put these in a class module. I define an optional variant variable that I
can pass to the function to set the value, but if I don't pass it a value, it
will return the value stored in bCanEdit (because this is a boolean value,
default (in this case, the default boolean value is False). As you can see,
I've declared bCanEdit as Static, which ensures that it retains its value
from one call to the next.

Public Function fnCanEdit(Optional SomeValue as Variant = NULL) as Boolean

Static bCanEdit as boolean

if NOT ISNULL(SomeValue) then bCanEdit = SomeValue
fnCanEdit = bCanEdit

End Function

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Dave]

Allen: Silly me. Monitor is at backend of desk - thus can't hit head
against it!

Me.Controls("field name").Enabled = False ("as you know") ...
works quite well. Able to turn fields off/on as the case requires. Thank
you for reminding me to set focus on a field that is enabled.

Dale: Yes my attempt to disable/enable fields was done in the subform so it
could react to the current record. Each record (depending on the contents)
has the opportunity for editing if the user is authorized for the particular
category. Someone long ago showed me the trick of using a static variable in
a function to hold a value instead of using a global variable. Use that when
storing the information in the database isn't necessary. I don't normally
put that type of coding in a class module because I normally use the function
with static variable to indicate some status for the entire program. Thank
you for sharing your experince.

 

[Dale Fye]

Silly me. I meant code module.
--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Dave]

Hi Dale

Yes I did rate it (and Allen's) and the comments were helpful. This project
I have many class modules to handle the various components within this db.
Almost too much to keep track of. The network this db is on is slow so I pay
particular attention to how many requests to the back-end I do. One class
module contains all the information I need about the user - which you could
say is static since it doesn't change until the user logs in again. That
alone is saving many trips to the be-db. Thank you again for your help.

Dave

 

[a a r o n . k e m p f]

the network isn't slow-- JET is slow

sounds to me like it would be best to upsize to SQL Server

Linked tables run like crap, too much of a pain for me