navigating to different records in a read only form

[Paul]

When you have a read-only form, (one whose "Allow Edits" property is set to
"No"), how do you get a record selection combo box to work in that form?

I have a form that uses a record selection combo box to select different
records. For some of my users, I have VBA set Allow Edits to "Yes," and for
others, "No." But when it's set to "No," the record selection combo box
won't work. How do you navigate to different records when you have a read
only form, if the combo box won't work?

Thanks in advance,

Paul

 

[Marshall Barton]

When you have a read-only form, (one whose "Allow Edits" property is set to
"No"), how do you get a record selection combo box to work in that form?

I have a form that uses a record selection combo box to select different
records. For some of my users, I have VBA set Allow Edits to "Yes," and for
others, "No." But when it's set to "No," the record selection combo box
won't work. How do you navigate to different records when you have a read
only form, if the combo box won't work?

Instead of using AllowEdits, set the Locked property of all
controls except the combo box. This can easily be done by
holding down the Shift key and clicking on each of the bound
controls.

 

[Gina Whipp]

Paul,

Put an Unbound Combo Box on the form, this will not lock so all users can
use it as a look-up box but the ones that can edit will have no difficultly.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Bob Larson]

Gina:

An unbound combo box will still lock if AllowEdits is set to False.
Marshall's answer is actually correct.

--

Thanks,

Bob Larson
Access MVP

Free Access Tutorials and Resources: http://www.btabdevelopment.com

Paul,

Put an Unbound Combo Box on the form, this will not lock so all users can
use it as a look-up box but the ones that can edit will have no
difficultly.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Dirk Goldgar]

Instead of using AllowEdits, set the Locked property of all
controls except the combo box. This can easily be done by
holding down the Shift key and clicking on each of the bound
controls.

I've always done it the way Marsh suggests, but recently it occurs to me --
probably suggested by someone else -- that it might be simpler to just set
AllowEdits = True in the combo box's Enter event and set AllowEdits = False
in the combo's Exit event. I haven't tried this in any practical
application, but it works in a quick test, and might be handier if the form
is only going to be read-only for some users, not all.

 

[Gina Whipp]

Bob,

Not on my forms... And I just tested that theory.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Gina:

An unbound combo box will still lock if AllowEdits is set to False.
Marshall's answer is actually correct.

--

Thanks,

Bob Larson
Access MVP

Free Access Tutorials and Resources: http://www.btabdevelopment.com

 

[Gina Whipp]

Bob,

MY MISTAKE... I just checked and the form it worked on has the code behind
the form that keeps my unbound controld from locking no matter what the
Allow Edits says. SORRY! I forogt I put that there, I must have run into
this problem and found a work around.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Gina:

An unbound combo box will still lock if AllowEdits is set to False.
Marshall's answer is actually correct.

--

Thanks,

Bob Larson
Access MVP

Free Access Tutorials and Resources: http://www.btabdevelopment.com

 

[tina]

I must have run into this problem and found a work around.

share? tia, tina

 

[Gina Whipp]

Tina,

I guess that was kind of rude, slapping myself in the back of my head... I
used the Tag property of the bound controls to lock the controls based on
the User's SecurityID.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Paul]

Gina,

Can you tell me what VBA code you're using to keep the unbound control from
locking regardless of the value of the AllowEdits property? My experience
is that even if the Locked property of the Combo box is set to "No," it will
not select a new record in the form if AllowEdits is set to yes.

Thanks

Paul

 

[Paul]

Dirk,

This could be a good solution for me, because I can add code to the combo
box's Exit event to test who the user is. If I want that user to be able to
edit the form, I could just exit the sub. If I don't want them to be able
to edit, then I could set AllowEdits to false.

Thanks

Paul

 

[Paul]

Marsh,

Since I need to have the form change between allowing and not allowing edits
depending on who the user is, I guess I could loop through all the controls,
changing the Locked property as appropriate.

Thanks for the suggestion.

Paul

 

[Gina Whipp]

Paul,

You missed the subject line... I made a mistake. I ran into that problem.
So I created a Security Table 1 = Edit, 2 = View (or Read Only) and 13 =
System Administrator.

Depending on your Security Level and the Tag Property depends on what you
can do but it leaves the unbound combo box in operation. I suppose you can
even do it without worrying about the Tag Property of each field. I put it
in two place because while some might figure out the table they usually
ignore the tag property.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Dirk Goldgar]

Dirk,

This could be a good solution for me, because I can add code to the combo
box's Exit event to test who the user is. If I want that user to be able
to edit the form, I could just exit the sub. If I don't want them to be
able to edit, then I could set AllowEdits to false.

Bear in mind that what I am suggesting is an alternative to Marsh's idea of
locking all the controls. That said, this approach would not need to check
the user's authorization in the Exit event. Instead, it would save the
current state of AllowEdits in the Enter event, and then restore that state
in the Exit event. To do that you'd have to declara a module-level variable
(in the Declarations section of the form's module) to save the state. Here
is how it would work, showing the relevant code from the form's module:

'------ start of example code -----
Dim mblnAllowEdits As Boolean

Private Sub YourComboBox_Enter()

mblnAllowEdits = Me.AllowEdits
Me.AllowEdits = True

End Sub

Private Sub YourComboBox_Exit(Cancel As Integer)

Me.AllowEdits = mblnAllowEdits

End Sub
'------ end of example code -----

 

[Marshall Barton]

Since I need to have the form change between allowing and not allowing edits
depending on who the user is, I guess I could loop through all the controls,
changing the Locked property as appropriate.

Sure. That's where the idea of using the Tag property comes
in.

I use a public procedure in a standard module to take care
of it for any form. Gina's use of different Tag settings
for different types of users is fine if that's your
situation, but a simple edit/no edit procedure, you could
just set the Tag property to LOCK or leave it blank:

Public Sub SetLocks(frm As Form, OnOff As Boolean)
Dim ctl As Control
For Each ctl In frm.Controls
If ctl.Tag = "LOCK" Then ctl.Locked = OnOff
Next ctl
End Sub

Then you can call it from any form to allow changes:
SetLocks Me, False
or to prevent changes:
SetLocks Me, True
Or from a main form to allow changes in a subform:
SetLocks Me.subformcontrol.Form, False
or ...

 

[Paul]

Thanks for the sample code, Marsh. It will also help me in another area.

Two questions.

1. The expression "ctl.Locked = OnOff" - I haven't seen that before. Is
that a toggle switch that turns the Locked property of a control on and off
each time it executes?

2. Using the Tag property to identify which controls should be locked seems
to be a great way to determine which ones are locked if you don't want to
lock all of them. But if you want to lock or unlock all of them at once,
wouldn't it be a valid, and possible simpler, approach to turn the form's
AllowEdits property on or off, as per Dirk's suggestion?

Paul

 

[Paul]

Thanks for the sample code, Dirk. If I understand it correctly, it's a good
way to control who can edit the records in a form if one group of users can
edit all records in a form, and another group can edit no records in a form.
I can use that in some situations, but in this particular one, I need to
check the users' authorization for each record.

This is a Project Management application, and I want the Project Manager and
Team members to edit their own projects, but not other projects. I have
some forms where users can only see their own projects, but in this
particular form I want everyone to be able to see all projects, but only
edit the ones in which they are on the mamagement team. And so I'm thinking
the best way to do that is to check their authorization status in the
following three events:

1. the form's Load event,
2. the form's OnCurrent event and
3. the selection combo box's Exit event

Does that make sense?

Paul

 

[Paul]

Thanks for the comments, Gina.

As I mentioned to Dirk in my message of 1/31 5:34 PM, all users can edit
some records in this form's recordset, but not all. Please see that message
form more detail.

I do have a question regarding your Security Table. Is this something you
use as part of the built-in Access security? I've always avoided getting
into Access Security because I've read that it "ranges from the labyrinthine
to the inscrutable."

If it's not part of the built in Access Security, and if it's simply a table
you're using to establish user security levels, could you say a few more
words about how you're using it? For example, are you using DLookup() in
VBA to get into your security table to check authorization levels?

Paul

 

[Gina Whipp]

Paul,

Got a little busy on a project tonight. I will send the details tomorrow.
However, before I run off. I myself do not use Access security. I either
use my one concoction or if my Client has an SQL server and I'm putting the
tables there I use it.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[John W. Vinson]

I do have a question regarding your Security Table. Is this something you
use as part of the built-in Access security? I've always avoided getting
into Access Security because I've read that it "ranges from the labyrinthine
to the inscrutable."

If you would like to investigate it - and if you don't have A2007 from which
it's been removed - check out the Microsoft Access 2000 Security FAQ:

http://support.microsoft.com/kb/207793/en-us