Access 2000 - AllowEdits property - setting false disables change of button.
Tester
Being quite new to Access 2000 (although experienced VB6) and developing a
project quickly, I have generally found I do not like the default way in
which Access sets up a form with the ability to edit a record displayed on a
form.
I would like my users to only be able to edit a record on a form if they
explicitly request to do so by pressing a button.
I therefore have introduced a checkbox on each form called "Allow Record
Edits" that when pressed sets the Allowededits property of a form to
true/false.
(If people have other suggestions on alternative methods I would be
interested to hear them)
The problem is, I cannot actually press the check box, once the "Allowed
Record edits" button prevents editing, because pressing the button itself,
is editing the form.
I get round this by making the checkboxes "got focus" event, temporarily set
Allowedits to true and the "lostfocus" event, to whatever the checkbox is
set to.
To me though this does not seem the most elegant method. I also have to use
the same method for the combo box I have on the form that allows one to
select a particular record by the key field. (Therefore, one cannot change a
combo box if allowedits is false).
Is there a way of locking only the controls that are bound to a DB field, or
is it perhaps just safer to lock each individual control and then call a sub
to change this when editing is needed?
I welcome any comments on my dilemma. My code does seem to work, but I am
worried this is not a solid coding method.
Regards,
Fletcher Arnold
news:3e13...@news1.homechoice.co.uk...
A command button would still work, regardless of the AllowEdits setting. In
design view you could set AllowEdits to false and create a command button
with the caption "Unlock". Then in the OnClick event have
If cmdLock.Caption = "Unlock" Then
cmdLock.Caption = "Lock"
Me.AllowEdits = True
Else
cmdLock.Caption = "Unlock"
Me.AllowEdits = False
End If
You could also consider having the AllowEdits always set to true but use the
.Locked property for the bound fields.
HTH
Fletcher
Steve Jorgensen
That will work, or there are other variations you can do. My solution
has been to use a frame and 2 option buttons with a transparent button
over the option to allow editing. Clicking the transparent button
sets AloowEdits to True and changes the value of the frame so the
"Edit Enable" option is selected.
Fletcher Arnold
"Steve Jorgensen" <nos...@nospam.nospam> wrote in message
news:j0481vo483qp5183m...@4ax.com...
>
> That will work, or there are other variations you can do. My solution
> has been to use a frame and 2 option buttons with a transparent button
> over the option to allow editing. Clicking the transparent button
> sets AloowEdits to True and changes the value of the frame so the
> "Edit Enable" option is selected.
Interesting. Presumably users cannot tell that they are, in fact, clicking
a transparent command button, and not selecting an option in the normal
sense (and you feel no sense of guilt for the deceit <g>). Perhaps I might
try that idea some time.
Fletcher
Tester
now, and use the "tag" property to store the current state of the command
button.
However,
I still have to use the event technique for the record selecting combo box I
have on the form.
Therefore this combo box has the key field assocaited with it and list each
record in the table. I have to set the onfocus event to set allowedits to be
true, and then the lost focus, to the current setting of the edit lock
command button.
I can think of no other way of doing this when using the Allowedit form
property.
But if anyone else knows how I would like to know.
Regards,
David W. Fenton
<j0481vo483qp5183m...@4ax.com>:
>My solution
>has been to use a frame and 2 option buttons with a transparent
>button over the option to allow editing. Clicking the transparent
>button sets AloowEdits to True and changes the value of the frame
>so the "Edit Enable" option is selected.
Er, can you elucidate on that? Why do you need a transparent button
over the option button?
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
David W. Fenton
<av151j$42n$1...@knossos.btinternet.com>:
Why the need for the "deceit?"
David W. Fenton
<3e147d90$1...@news1.homechoice.co.uk>:
>Therefore this combo box has the key field assocaited with it and
>list each record in the table. I have to set the onfocus event to
>set allowedits to be true, and then the lost focus, to the current
>setting of the edit lock command button.
>
>I can think of no other way of doing this when using the Allowedit
>form property.
I never use AllowEdits because it means that even unbound combo
boxes (used for finding records) are not enabled.
I always create a custom collection of all the bound controls in
the form's OnOpen event, and then set controls to locked/unlocked
when needed by looping through the custom collection.
It would work something like this:
In the OnLoad event of the form:
Call InitializeCollections
That sub looks like this:
Private Sub InitializeCollection()
Dim ctl As Control
If (mcolFields Is Nothing) Then
Set mcolFields = New Collection
For Each ctl In Me.Controls
If ctl.Tag = "Bound" Then
mcolFields.Add ctl, ctl.Name
End If
Next ctl
End If
Set ctl = Nothing
End Sub
All it does is create a collection of all the editable controls.
Assuming the controls are saved in a locked state, you need to
nothing further in the OnLoad.
For your EDIT button, it would be something like this:
Call SetEdit
That sub would look something like this:
Public Sub SetEdit()
Dim ctl As Control
Dim varCtl As Variant
InitializeCollection
For Each varCtl In mcolFields
Set ctl = varCtl
ctl.Locked = Not ctl.Locked
Next varCtl
If (ctl.Locked) Then
Me.Dirty = False
Me!btnEdit.Caption = "Edit"
Else
Me!btnEdit.Caption = "Save"
End If
Set ctl = Nothing
End Sub
Now, I'm not sure I would use Edit/Save as my captions for the
button, but you get the idea (i.e., "Save" implies that it is only
saved when you click the button, whereas any number of operations
actually might force a save to the underlying data table).
Also, if your form loads more than one record (something I wouldn't
necessarily recommend in this kind of context) you'll have to
determine what should happen if the user departs a record without
hitting the SAVE button. The easiest approach is to have the
OnCurrent set the command button's caption to "Edit" and then call
your SetEdit() sub.
Personally, I've hardly ever implemented anything like this for
editing (I do lots of custom collections, and also locking of
controls on forms) because it really only works in a fully unbound
form. My personal preference is that all fields should be editable
as soon as a record is loaded *if* the user should be allowed to
edit this particular record. I see no point with a bound form of
trying to work inconsistently around the behaviors of bound forms
-- you'll only be implying things that you can't really do (such as
cancelling edits, etc.).
I really can't conceive of a situation where you'd really want to
disable editing of controls with a command button if the form is
bound.
Tester
news:92F79AFAFdf...@24.168.128.74...
<SNIPPED>
> Personally, I've hardly ever implemented anything like this for
> editing (I do lots of custom collections, and also locking of
> controls on forms) because it really only works in a fully unbound
> form. My personal preference is that all fields should be editable
> as soon as a record is loaded *if* the user should be allowed to
> edit this particular record. I see no point with a bound form of
> trying to work inconsistently around the behaviors of bound forms
> -- you'll only be implying things that you can't really do (such as
> cancelling edits, etc.).
Interesting code and interesting comments David. One thing about your code
is that you have in the initialisation
If ctl.Tag = "Bound" Then
Can I assume that you have manually first set the tag property of all the
bound fields to the value "bound" for this to work. I checked in my Access
and bound fields do not automatically get this set.
I also found your comments interesting, particularly because I am
demonstrating my first draft of this Access DB to the client this coming
Monday and can get no real input from them until then. As a VB developer, it
seemed to me, to have the records editable from the start is dangerous. This
DB will be used by generally non-technical people so my intention is to
completely hide the access interface (as much as possible). I found that
when I played around with the Access demo DB's I really did not like how
easy a record could just be edited. I prefer to explicitly say, I want to
edit this record or I want to insert a new record.
But I would welcome other opinions as I may be wasting time this weekend
developing features that are unnecessary (therefore, clever command buttons
that allow/disallow editing of the record).
Thanks for any input on this matter.
Rick Brandt
Just to add a similar way to David's that I use. I created a few custom
functions long ago.
LockAll()
UnLockAll()
EnableAll()
DisableAll()
etc..
All of them take a Form and a Search String argument. Each function loops
through all controls on the form and tests the Tag property to see if the
Search String argument is contained within the tag. If it is found then the
appropriate action is taken on the control.
One could certainly use a single function with more arguments, but I like
the simplicitly of using a function where the name self-documents what it is
doing and once you've written one of them it is very easy to copy and tweak
to build the rest.
Fletcher Arnold
The deceit refers to making the command button transparent, so the user
believes he is clicking an option button, when in fact he is clicking a
command button.
I didn't seriously expect feelings of guilt - hence the <g>. Just being
silly.
Fletcher
David W. Fenton
>"David W. Fenton" <dXXXf...@bway.net> wrote in message
>news:92F796953df...@24.168.128.74...
>> fle...@home.com (Fletcher Arnold) wrote in
>> <av151j$42n$1...@knossos.btinternet.com>:
>>
>> >"Steve Jorgensen" <nos...@nospam.nospam> wrote in message
>> >news:j0481vo483qp5183m...@4ax.com...
>> >>
>> >> That will work, or there are other variations you can do. My
>> >> solution has been to use a frame and 2 option buttons with a
>> >> transparent button over the option to allow editing.
>> >> Clicking the transparent button sets AloowEdits to True and
>> >> changes the value of the frame so the "Edit Enable" option is
>> >> selected.
>> >
>> >Interesting. Presumably users cannot tell that they are, in
>> >fact, clicking a transparent command button, and not selecting
>> >an option in the normal sense (and you feel no sense of guilt
>> >for the deceit
>> ><g>). Perhaps I might try that idea some time.
>>
>> Why the need for the "deceit?"
>
>The deceit refers to making the command button transparent, so the
>user believes he is clicking an option button, when in fact he is
>clicking a command button.
I understand that part -- I just don't understand why you can't
just use the option group itself to trigger the event.
>I didn't seriously expect feelings of guilt - hence the <g>. Just
>being silly.
That really wasn't my question -- I've frequently used tab controls
with no visible tabs driven by toggle button option groups, a form
of deceit, I guess. I've no problem with that. I just don't see why
there's a need for an additional control in the first place.
David W. Fenton
>"David W. Fenton" <dXXXf...@bway.net> wrote in message
>news:92F79AFAFdf...@24.168.128.74...
>
><SNIPPED>
>> Personally, I've hardly ever implemented anything like this for
>> editing (I do lots of custom collections, and also locking of
>> controls on forms) because it really only works in a fully
>> unbound form. My personal preference is that all fields should
>> be editable as soon as a record is loaded *if* the user should
>> be allowed to edit this particular record. I see no point with a
>> bound form of trying to work inconsistently around the behaviors
>> of bound forms -- you'll only be implying things that you can't
>> really do (such as cancelling edits, etc.).
>
>Interesting code and interesting comments David. One thing about
>your code is that you have in the initialisation
> If ctl.Tag = "Bound" Then
>
>Can I assume that you have manually first set the tag property of
>all the bound fields to the value "bound" for this to work. I
>checked in my Access and bound fields do not automatically get
>this set.
Yes, naturally -- forgot to mention that.
>I also found your comments interesting, particularly because I am
>demonstrating my first draft of this Access DB to the client this
>coming Monday and can get no real input from them until then. As a
>VB developer, it seemed to me, to have the records editable from
>the start is dangerous. . .
Reading between the lines, it sounds like your form presents all
records from the table to the user at the very beginning. I don't
do that -- I do one of two things: either 1) load only one record
(or a group of records) at a time OR 2) open the form on a new
record.
I prefer alternative 1), such that the form opens with an empty
recordset -- not an empty record*source*, but a recordsource that
returns an empty record; what I use is a TOP 1 query on the
smallest table in my application, then return no fields from that
table, just aliased Null values, such as:
SELECT TOP 1 Null As PersonID, Null As FirstName,
Null As LastName, Null As Address [etc.]
FROM tblLookup;
That returns an empty, non-editable record as the default
recordsource when you open the form. Then when the user chooses a
record (or group of records) to load, it loads completely editable.
> . . . This DB will be used by generally
>non-technical people so my intention is to completely hide the
>access interface (as much as possible). I found that when I played
>around with the Access demo DB's I really did not like how easy a
>record could just be edited. I prefer to explicitly say, I want to
>edit this record or I want to insert a new record.
I agree about the Access demos, which are really case studies for
what *not* to do in an Access database, but the other issue is that
you shouldn't be loading data until you know what data the user
*wants*.
>But I would welcome other opinions as I may be wasting time this
>weekend developing features that are unnecessary (therefore,
>clever command buttons that allow/disallow editing of the record).
My approach with the default empty recordsource and then loading
records as requested has taken care of the exact problem you have
described.
David W. Fenton
<av2b6e$b7p35$1...@ID-98015.news.dfncis.de>:
>Just to add a similar way to David's that I use. I created a few
>custom functions long ago.
>LockAll()
>UnLockAll()
>EnableAll()
>DisableAll()
>etc..
>
>All of them take a Form and a Search String argument. Each
>function loops through all controls on the form and tests the Tag
>property to see if the Search String argument is contained within
>the tag. If it is found then the appropriate action is taken on
>the control.
One thing that I found long ago is that setting up a custom
collection and using it instead of the Controls collection is
*much* faster, quite noticeably so. It also means that you don't
have to worry about different control types having different
properties/characteristics -- you can create custom collections for
each control type (if warranted) and handle each individually.
To work with the way you use your example functions, you'd need to
make the collection public, and you'd pass the form name and the
collection name. Actually, now that I think about it, all you'd
need to pass is the collection name, as it is a "property" of the
form, e.g., Forms!frmPerson.mcolFields.
>One could certainly use a single function with more arguments, but
>I like the simplicitly of using a function where the name
>self-documents what it is doing and once you've written one of
>them it is very easy to copy and tweak to build the rest.
Personally, I prefer a single function for locking, and telling
whether it's on or off, and a single function for enabling, with a
parameter for on/off. That means less code to maintain, and is
hardly less easy to read:
Call EnableAll("frmPerson") vs. Call Enable("frmPerson", True)
seems pretty equivalent to me in terms of code readability, and my
approach takes half the code.