Datasheet Form - Possible Usage Case?

[JMongi]

I'm starting to plan for a form to use for editing user access levels for various "modules" in my db. So, I'm envisioning a list of rows with userid's and a list of columns with the various modules as headers. The intersections would be combo boxes for the access levels (right now 5). Is this something the datasheet form would be able to handle? It's early, but I'm envisioning the following table/query as the base:

tblUserAccess
ID (PK)
UID (FK to users table)
Module (FK to module list)
AccessLvl (FK to level list)
LastDateMod

I've tried doing a few searches on datasheet without success. I'm going to do some investigating of the basic Access info on datasheet forms to see if I can answer my own question. Thanks!

 

[Gasman]

I'd have a record for each user and module.

 

[JMongi]

That would be the underlying data structure as I outlined in the first post. Each unique record would consist of a user id, module and level. Unless I'm misunderstanding your post.

 

[JMongi]

It might be an absolute nightmare to craft an editable form structured as I described when working with normalized data. That's why I thought I'd throw it out here for comment. A single user form is easy enough to create. This is just another option I didn't even know if it was possible.

 

[Gasman]

You said modules in different columns?, Like a spreadsheet?

 

[JMongi]

This is all preliminary but let me expound on the basic structure:
tblModule
ModuleID (PK)
ModuleName
ModuleAbr

tblLevel
LevelID(PK)
LvlNum
LvlDescrip

tblUsers
UserID (PK)
...

tblUserAccess
AccessID (PK)
UserID (FK to users table)
ModuleID (FK to module list)
LevelID (FK to level list)
LastDateMod

So, an individual user would have a record that was his userID, the module, and the access level associated with that module and the date this access level was set. When logging into the dB, a query will run to pull the latest access levels and then store that in a record set. This recordset will be used to control the UI and authorizations within the dB.

While I can create a parent/child forms for viewing and editing records for each particular user, I am investigating the possibility of a form that is structured as described in the original post. It would show all users on one axis and all the modules on another axis. This would definitely NOT be a list of records. That would be impossible to be structured this way. I didn't know if there was a form within access that was crafted in a way that could actually accomplish this. I'm not even sure you could pull data OUT of a normalized database that could be displayed this way. That's why this question is so open ended.

 

[Gasman]

Well that display would be a crosstab query I believe, but it looks like your structure is good, well to me at least.

 

[MajP]

You can do the crosstab, but it will not be editable. However, then you can doubleclick on a cell and bring up a pop up that allows you to edit the access for that User and that Module. On close of the pop up you would have to requery the crosstab.

 

[JMongi]

You can do the crosstab, but it will not be editable. However, then you can doubleclick on a cell and bring up a pop up that allows you to edit the access for that User and that Module. On close of the pop up you would have to requery the crosstab.

So, in this hypothetical, this would be viewing a crosstab query, not an actual form? That isn't a non-starter as the only person setting such levels would be an administrator anyway. So having this be via a query instead of form wouldn't be a deal breaker.

 

[MajP]

You can bind the crosstab to your form if you know the discrete number of columns (modules). As mentioned the query is not editable, you need a pop up form when clicking on a cell to edit. This could be permission based.

 

[JMongi]

Hmm...I figured it would be tricky. Part of the issue is that I am trying to account for module expansion (so no fixed number of modules) and envisioning adding access levels for all users when a new module is incorporated into the main operations database.

 

[Isaac]

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?

 

[MajP]

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.

 

[MajP]

I am assuming the grid view is helpful as a quick way to see which people are accessed to which module looking from the module view or person view.

 

[Isaac]

Definitely agree that generally speaking, batch update ability and summarizations are good things.

 

[MajP]

However, this is one of the updates that would be great in Access. Never going to happen, though. Having an ubound grid control like a .NET DataGridView would make doing things like this so easy. It would allow you to dynamically build an editable form with rows for users and columns for modules and comboboxes for cells to change access levels. Then you can close the form and batch update. There use to be a FlexGrid Active X control, but was a pain to work with and distribute.

 

[Pat Hartman]

Here's a sample database that might help you. It takes a properly normalized schema and by using queries, creates a DS form that looks like a spreadsheet. I've only used it for forecasting applications so I've only used 12 columns in the real world. I have no idea how many columns could be presented without over taxing Access. The database contains two samples. One is forecasting. The other is expenses which is more along the lines of what you are looking for. There's not a lot of data in the sample. Add more Expense types and add rows for data and see what happens. No code changes would be required. I've also included some rudimentary documentation.

 

[MajP]

As mentioned this does not solve the problem. If the user wants to add a Module it would require modifying the query and modifying the form. No way to do that dynamically. You can add users by code but Modules is a "column" and requires redesign. If the user sets and limits the Modules ahead of time then this could a viable approach and alleviate the need for a pop up to edit.

 

[CJ_London]

One way is to create your cross tab query, use code to assign it to a temporary table.

then have a form with a sub form which uses the table as its source object (I.e sourceobject=table.temptable)

you’ll need code to update the underlying table as values change.

Because it is a table, users could change usernames but these can be ignored. Tables do not have events like forms do so

if you need form type events/formatting, you’ll need to use a form as a source object . You can preplan a maximum number of columns and hide those not required.

 

[JMongi]

There have been some intriguing options presented.
@MajP - Thanks for indulging the brainstorming session. I always get plenty of food for thought from you!
@Isaac - Thanks for the practical viewpoint. This is definitely an exercise in how much work is saved by setting it up VS how much work to set up.
@PatHartman - Many thanks for providing a sample database. Even if it ends up not being directly implemented, I always learn something new.
@CJ_London - That's a great idea on the whole temp table source! It sparked the hazy outline of a plan. I'll have to see how it develops.

I also have never used a crosstab query which seems to be the main source of all of these ideas, so this thread has definitely been useful for pointing me in the right direction. Many thanks! I'll try to remember to post back here what the final implementation ended up being.