Recordset is not updateable msg with combo

[blackduck603]

I am getting a "Recordset is not updateable" runtime error when the following line of code tries to execute.

' Set the combo to the 1st record
Me.ComboSubtypes = Me.ComboSubtypes.Column(0)

This ONLY occurs with users in my new Inquiry group (defined in the Security/Permissions). The Inquiry group has Open/Run and Read Design permission for this form (and all others). The Inquiry group's permission for all tables is Read Design & Read Data.

This error did not occur until I implemented the security & permissions.

I appreciate anycoments, suggestions, ideas

 

[Telecom]

In the RowSource of your comboBox is there a query defined? If so, you'll need to give premission to that query too.

 

[blackduck603]

The RowSource query is not defined in a Query object if that is what you mean. The query is defined in the combo's RowSource property as follows.

SELECT DISTINCT [ItemTypes].[ItemTypeID], [ItemTypes].[ItemTypeDesc] AS [DESC] FROM ItemTypes ORDER BY [ItemTypeDesc];

The contents of the combo are based on the value selected in another combo. So.....I build a new query and execute a Requery in the AfterUpdate handler of the related combo. Here is the entire Sub code:

'-----------------------------------------------------------------------------------------------
' Populate the SubTypes combo based on the Type selected
'-----------------------------------------------------------------------------------------------
Private Sub ComboTypes_AfterUpdate()

Dim strSQL As String

' ComboSubTypes Row Source: SELECT [ItemSubTypes].[ItemSubTypeID], [ItemSubTypes].[ItemSubTypeDesc] FROM ItemSubTypes WHERE [ItemSubTypes].[ItemTypeID]=[Forms]![MaintenanceForm]![ComboTypes];
strSQL = "SELECT ItemSubTypes.ItemSubTypeID, ItemSubTypes.ItemSubTypeDesc" & _
" FROM " & _
" ItemSubTypes" & _
" WHERE " & _
" ItemSubTypes.ItemTypeID = " & Me.ComboTypes.Column(0) & _
" ORDER BY ItemSubTypeDesc"

Me.ComboSubtypes.RowSource = strSQL


' Force a re-query (based on current selected Type in comboTypes)
Me.ComboSubtypes.Requery

' Set the combo to the 1st record
Me.ComboSubtypes = Me.ComboSubtypes.Column(0)


End Sub



As I mentioned above, this code works fine unless the user does not have Admins permissions. I don't get it.......I wouldn't think that adding items to a combo via a query would need more than read permission.


I FIGURED OUT PART OF THE PROBLEM.
The Inquiry user is also in the default Users group.

I sort of got off track here.
I do NOT want the user to havce any permission for this form.
BUT
When I remove Open/Run and Read Data permission for the form, I do NOT get the expected "No Permission" msg when an Inquiry user launches the form from my Switchboard form. Instead I get the following message:
"There was an error executing the command."

If the user double clicks on the Form to launch it (istead of from the switchboard), the correct "No Permission" message appears.


Any idea why this might occur?


H E L P

 

[Telecom]

Don't quote me on it but you may need Modify Design on the Form security permissions for that particular Form. When you change the me.combo.rowsource value its a Design Change.

It sounds like you may need to setup custom permission groups. Make one for Viewing and another for Changing.


What I do for my database is make custom groups.

1. First Backup your WorkGroup File and Database in safe spot.
2. Remove all access for the default Users group.
3. Create a Custom permission Group for Viewing and give it Read-like permissions for the Database Objects that it needs.
4. Create a Custom permission Group for Changing and give it Change-like permissions for the Database Objects that it needs.
5. Remove the User Group for all users.
6. Give the just-built permission groups to your users as needed.

In my database I have one form for Viewing a Record and another for changing a record. Both of these forms are popups off my main form; which is a continuous form that displays all my records for that given format.

Using a Command Button Open a popup Form for changing a record; use to Add, Edit, or Add/Edit depending on how you code your form.

Make another Command button that opens a popup Form that allows a normal user to just View the Record. You can grant Read permissions to that read-only popup form. You can just display the data and not make fancy code that would cause them to need greater the Read permissions.

Does that help or are you looking for a simpler fix?

 

[blackduck603]

Yes it helps.......but I am still quite confused.

Are you saying that updating the contents of a combo control on a form via a query requires Modify Design permission?

 

[blackduck603]

I decided to try a slightly different approach (per your suggestion).

I removed all permission to my Maintenance Form for the Inquiry group.
Seems simple enough.
HOWEVER...........
If an Inquiry user trys to launch the Maintenance Form ia my Switchboard form, instead of getting "No Permission", they get a message box that says:
"There was an error executing the command."

Trying to launch the Maintenance Form directly from the access forms object works as expected with the following message:
"You do not have permission to access this object"

Any idea why the Switchboard launch behaves differently?

 

[Telecom]

Yes it helps.......but I am still quite confused.

Are you saying that updating the contents of a combo control on a form via a query requires Modify Design permission?

I'm not 100% positive about the Modify Design permission being related to the Combo Box rowsource change in your code. Try testing that to double check.

Here is the Definition of a Modify Design Permission...

Modify Design: View and change the design of tables, queries, forms, reports, or macros; or delete them.

 

[Telecom]

I decided to try a slightly different approach (per your suggestion).

I removed all permission to my Maintenance Form for the Inquiry group.
Seems simple enough.
HOWEVER...........
If an Inquiry user trys to launch the Maintenance Form ia my Switchboard form, instead of getting "No Permission", they get a message box that says:
"There was an error executing the command."

Trying to launch the Maintenance Form directly from the access forms object works as expected with the following message:
"You do not have permission to access this object"

Any idea why the Switchboard launch behaves differently?

In your switchboard are you launching your form with VB code? Like this...DoCmd.OpenForm "frmName"

 

[blackduck603]

VBA code for Switchboard

I figured it out (and am guessing that this is where you were headed).

The Click event in my Switchboard code was getting an error trying to launch any form with no permission. I ended up adding code to check the error and if it was No Permission to display a more appropriate message.

HandleButtonClick_Err:
If (Err = conErrDoCmdCancelled) Then
Resume Next
ElseIf Err.Number = 2603 Or Err.Number = 3112 Then
MsgBox "You do not have permission for this.", _
vbOKOnly + vbInformation
Endif

Thanks for your assistance