Full Version: Set Form To "view Only", Without Affecting Combo Boxes ?
UtterAccess Forums > Microsoft® Access > Access Forms
I'm always looking for ways to have fewer saved objects (queries, forms) and achieve smaller size & hopefully more elegance by doing things somewhat dynamically in code/tables instead. It will make sense when you read the rest of the post. This may not seem very elegant at all yet (work in progress), but just to give you some background on 'why' I'm doing it this way: I'd rather have one form and dynamically set the 3 types of user access levels I want (add only, add + view, view only) as opposed to for example, 3 different copies of the form..
I have a tbl_USERS where access levels are set for various things. my database basic interface structure is one main unbound form (menu) with a tab control. on each tab control there are different things, on page index 7, there is a BOUND form (so I guess technically a Subform...but really there is no parent/child stuff going on, it's just a bound form I drag/dropped). when you click on this page, I want to set the form's data entry, allow additions, allow edits etc., by code.
This seems to work fantastic except for one little glitch. If the user is set to VIEW ONLY access, then when the page loads, they can no longer even SELECT something in 2 comboboxes I have near the top of the form which are meant so that you can select a record and the form GOES to it. These, of course, I want EVERYONE to be able to 'select' an item, so they can GO TO a record (even if they are View Only).
However, this code is making it to where if they are View Only they can't even do that.
How do I adjust what I'm doing to allow for this?? THANKS in advance!!
Code in point:
Case 7
'evaluate what user's access is with regard to AGENTS. there are 4 possibilities:
'1. null value. (cancel). 2. ADD only. 3. ADD AND EDIT. 4. VIEW ONLY.

If IsNull(DLookup("ADD_EDIT_AGENT", "tbl_USERS", "USERNAME='" & GetUserID & "'")) Then
MsgBox "No access", vbCritical, " "
Me.TabCtl7.Value = 0
Exit Sub
strvalue = DLookup("ADD_EDIT_AGENT", "tbl_USERS", "USERNAME='" & GetUserID & "'")
Select Case strvalue
Case "ADD"
Form_FRM_AGENTS.DataEntry = True
Form_FRM_AGENTS.AllowEdits = False
Form_FRM_AGENTS.AllowAdditions = True
Me.lblInvisibleAgents.Caption = "Your Access Level: ADD NEW"
Me.lblInvisibleAgents.Visible = True
Form_FRM_AGENTS.DataEntry = False
Form_FRM_AGENTS.AllowAdditions = True
Form_FRM_AGENTS.AllowEdits = True
Me.lblInvisibleAgents.Caption = "Your Access Level: ADD or EDIT"
Me.lblInvisibleAgents.Visible = True
Form_FRM_AGENTS.DataEntry = False
Form_FRM_AGENTS.AllowAdditions = False
Form_FRM_AGENTS.AllowEdits = False
Me.lblInvisibleAgents.Caption = "Your Access Level: VIEW ONLY"
Me.lblInvisibleAgents.Visible = True
Case Else
MsgBox "A value was found in your user access for this module, " & vbNewLine _
& "But it was not one of the allowed values. The value found was: " & vbNewLine & vbNewLine _
& strvalue & vbNewLine & vbNewLine & "Please see manager to correct", vbCritical, " "
Me.TabCtl7.Value = 0
Exit Sub
End Select
End If
You could move your 2 unbound combo boxes that are being used to filter the records out of the sub form and on to the main form (same tab/page). It will complicate the filtering process a bit as that will need re-written but it would allow your code to work as is.
Just a suggestion.
Hi Isaac,
nother option is to leave AllowEdits to True and just disable/lock specific controls.
Just my 2 cents... 2cents.gif
As Shrek suggested, it's the AllowEdits set to False that is causing the Comboboxes not to function. The Access Gnomes consider the use of a Combobox' to be 'editing!' And his suggested approach of Locking or Locking and Disabling all data-displaying Controls except the Comboboxes is a pretty standard approach. You can actually loop thru all Controls and apply the Locking/Locking Disabling to the appropriate ones.
.D.'s approach will work, too, but as he said, it will complicate the filtering process a bit!
Linq ;0)>
FWIW, I use J. D.'s approach. I think the other way is more work.
Thanks everyone. I wasn't sure if it would require a workaround like this or if maybe I was misunderstanding something more fundamental about the form settings. Oh well - I will loop thru controls to lock/unlock I guess. Thanks again.
Frank, this did get me thinking more about it...So basically the only way that JD's way is more work is that it requires a more complete reference to the form name but that's about it right? example right now I have this in after update event of cbo:
Me.RecordSource = "SELECT * FROM TBL_AGENTS WHERE INTERNAL_ID=" & "'" & Me.cmbNAME.Value & "'"

All that would be required is to adjust the Me. stuff to proper form references right?
Yes, J. D. has you putting the combo box in the MAIN form, with the data appearing in the subform control.
o in the AfterUpdate of the combo box, you could use something like this:
Me.NameOfSubformControl.Form.RecordSource = "SELECT statement goes here"
Has about to respond and say yes, you need to properly reference the controls in their new location (main form) but when I hit reply, I see Frank has you on the right track.<
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.