Datasheet Form - Possible Usage Case?

[JMongi]

Stepping back a bit, I don't see why there is a need for this - I usually just have a Manage Users link that opens a normal form. Each record is a user, and all details can be edited...Why do you "need" this "Excel-type-look" in the first place?

Just to elaborate on the answer a tiny bit. Imagine there are 40 users and I add a new "module" to the main DB. To control access would require individually opening each user record and editing them. Then, how do you know you got them all? This type of form/object would just simplify the process and eliminate errors....assuming it's not more trouble then it's worth!

 

[JMongi]

If this was me I would have a main form with the grid view crosstab as a subform for quick visibility. Then I would have two more subforms. 1 to select a module and add/edit/delete user access. The other would be the flipped view. I can select a user and add/edit/delete modules.

This gives me the quick macro view and two ways to update.

I just reread the thread an hit on this post. I didn't think about the two subform idea, one for users and all their access and one for modules and all their user levels. That might work out great. The crosstab for easy visibility, and the module level form to easily setup all the access when a new module is rolled out which was the main impetus for this thread.

 

[MajP]

Here is the problem with almost any of these ideas, a temp table, crosstab, or complex query. If you want it to be editable with pull down choices you are still stuck with binding to a form. This means you cannot add additional modules without modifying the form or in certain cases the query. Not saying it cannot be done, but all would require significant amount of code to make the number of Modules (columns) dynamic.
You could create a datasheet with a best guess at the number of modules and create a control for each. Then on open dynamically bind the controls and show/hide the used controls. I can see this being doable, but not easy to code. If the number of modules (Columns) was set then any of these ideas would be workable. In access you never want to dynamically add or delete controls. You can show or hide, but that requires an assumed max limit of hidden controls.

I didn't think about the two subform idea, one for users and all their access and one for modules and all their user levels. That might work out great. The crosstab for easy visibility, and the module level form to easily setup all the access when a new module is rolled out which was the main impetus for this thread.

Although the above may not be as eloquent as an editable grid, it requires no complex code, and still provide a reasonable UI.

 

[Minty]

I have done something similar for timetable entries for various stages of a process.
The main form is a continuous datasheet form based on a temp table that is created at form open, each record within can be uniquely identified so on the current event of the datasheet I display the linked data in a subform for editing.

It's relatively simple to code it all.

 

[Isaac]

Just to elaborate on the answer a tiny bit. Imagine there are 40 users and I add a new "module" to the main DB. To control access would require individually opening each user record and editing them. Then, how do you know you got them all? This type of form/object would just simplify the process and eliminate errors....assuming it's not more trouble then it's worth!

I see what you mean, but it still seems like Excel thinking - as in, "I've got to figure out a way for the user to see all the data at once" - rather than for example, to answer your question directly, if I were worried about everyone having the proper access, I'd just be sure to include Features on the form that allowed a person to see "everyone with ___ access", or "everyone in ___ group", or "everyone without ___ access", etc.

But yeah - I understand what you're saying now .. thanks 4 explanation.

 

[Pat Hartman]

As mentioned this does not solve the problem.

You can't have everything. At least the form gives the user the look he wants and is editable. As I said, I developed it for a forecasting app. It's pretty safe that we won't be adding any months to the year anytime soon. It is obviously not a good solution for an app where the addition of "columns" is regular.

If you base a form on a crosstab and use popups to edit, you still need to change the form if you add more columns to the crosstab.

I was rushed when I made my original post and I forget to add that that I think the pivot layout is wrong for this application because unless you wait until the very end to make security form, you will end up changing it constantly and it could easily get too wide to be held in a table/query. Queries have the same 255 column limit in Access that tables do.

When I implement security, I assign users to groups and then groups to "modules" and I use a subform to represent "modules" Since this is a many-many relationship, i also create forms with the opposite view. i.e. modules with subforms showing groups. And finally, the system is less complex if you allow users to be members of more than one group.

 

[JMongi]

If you base a form on a crosstab and use popups to edit, you still need to change the form if you add more columns to the crosstab.

Good to know since I've never used a cross tab query before. That does leave the same issues with expandability. The pace of module expansion will surely decrease over time. But, modding the form to accommodate such expansion is more work than what it's trying to simplify.

When I implement security, I assign users to groups and then groups to "modules" and I use a subform to represent "modules" Since this is a many-many relationship, i also create forms with the opposite view. i.e. modules with subforms showing groups. And finally, the system is less complex if you allow users to be members of more than one group.

Maybe viewing the levels as groups will illuminate to my mind the interplays going on a little better. More thought is required.

I think we are broadly on the same page. As our company is small but growing many people wear many hats but I don't want someone who needs access to critical info in one area to have critical info access in another area without need. So, thus my idea of access levels per "module". Our shop foreman can have deep access to our production information/tracking functions but doesn't need similarly deep access to our parts/inventory functions, etc.

Wouldn't you know that as soon as I think I have time to sink my teeth into this, the fires come a calling...looks like this is quickly becoming a next week brainstorming session.

Thanks again to all who have commented. Great insights!

 

[JMongi]

I have done something similar for timetable entries for various stages of a process.
The main form is a continuous datasheet form based on a temp table that is created at form open, each record within can be uniquely identified so on the current event of the datasheet I display the linked data in a subform for editing.

It's relatively simple to code it all.

For the sake of clarity, which comment were you referring to as "done something similar"....

 

[Minty]

Apologies - I meant using a temporary table to hold the data to display, with a subform to allow edits of whichever "cell" in the datasheet view had focus.

By using a temp table you can have completely dynamic headings, as you create it on the fly.

In this particular use there can be up to 7 stages, but you could have as many as required.

The secret to syncing it with the subform is to be able to identify which record and "field" you are in.
With this data set, it is simple and I can easily look up the relevant source data record for the sub form.

 

[MajP]

If you base a form on a crosstab and use popups to edit, you still need to change the form if you add more columns to the crosstab.

To be clear I already said that twice. You are only repeating what I already said.

Here is the problem with almost any of these ideas, a temp table, crosstab, or complex query. If you want it to be editable with pull down choices you are still stuck with binding to a form. This means you cannot add additional modules without modifying the form or in certain cases the query.

So can this be done dynamically? I believe so, but not easily. You can dynamically build the query def. Then for each Module column of the query you could set the display control to a combobox. Then set the rowsource of the combobox. These are extended properties of the query def. If you did it with a temp table you could do the same thing. Only way I think you can have dynamic columns and editable cells.

And again, all of these methods are "relatively" simple if the columns are not dynamic.

 

[Pat Hartman]

@JMongi,
I think you are really not going to like the modules across the top view. It looks usable if you have a dozen or so modules but once you have to start scrolling, you are going to loose the "goodness" of the grid, especially when the modules you group together in a "level" are 1, 12, 30, and 45.
I'll leave you to MajP since I seem to have annoyed him yet again.

 

[MajP]

I think you are really not going to like the modules across the top view. It looks usable if you have a dozen or so modules but once you have to start scrolling, you are going to loose the "goodness" of the grid, especially when the modules you group together in a "level" are 1, 12, 30, and 45.

That presents a possibility for the best workable solution IMO. It will allow the number of modules to be dynamic and still allow you to bind to a form.
Lets assume you have up to 40 modules, but in a grid view you can view 10 reasonably. Then you could build a subform with names and 10 textboxes for modules. When the subform loads it presents the first 10 modules as columns. You then would have a set of controls to move to next 10 or previous 10 allowing you to see any group of ten at a time. You do this by changing the control source of the textboxes. You then could click on a cell and edit its value in a pop up form if using a xtab as your source or do it directly in the cell if using a temp table. You then write to the normalized table after update of a cell.
The code to do this is a lot but not unsurmountable. In fact I almost did exactly that in a room booking form. This allowed me to book many different rooms during many available time blocks. This in fact used a single subform although to the user it looked like many subforms for each group of rooms. This used a temp table and changed the rowsource and the captions for the columns. The only difference would be the control to load the next set of rooms (in your case modules). I had tabs because the number of rooms was known, you would need a next/previous control
So this would be doable and if the number of modules is a lot this would be a better UI.