Request for Comment and Help - Resource Management DB

[Datech]

Edit: No outstanding problems ATM. Check back later if you care though!

So I've come a long way since I first asked how to do this a few months ago. I've learned a lot of simple stuff and a lot of advanced stuff, but unfortunately I've skipped over a lot of simple and intermediate stuff in my haste.

I've attached a slightly scaled down portion of the database that me and another member here have developed. For anyone watching, feel free to replicate anything you see and like as I'd be no where without the same process. Here are a few fully or close-to-fully refined features that may serve you:

1. User level security with two levels of granularity (roles and views)
2. Administration console for editing users, roles, and views (modified from a similar console posted here)
3. Bug reporting system (clone of admin console, but it doesn't fully work)
4. Full front end for users with buttons and navigation
5. User tracking system for logins and logouts (mostly working, just not a priority right now)

Purpose:
The point of this database has changed a few times, and the scope has expanded drastically and is still growing. Basic overview:

• Track employee data
• Track project data
• Track resource allocation (what employees are on which projects)
• Allow project managers to log in and edit resource allocation information when necessary
• Automated headcount calculations (in progress, not my responsibility)
• Skills tracking (eventually)

Typical Operation and current problems:

• A user logs in (LogInForm)
  
  • Determine user access level, Restrict DB access accordingly
  
  
• Project Managers or TPMs
  
  • View/Edit Resource Utilization (Copy of Owners form)
    
    • Form auto-filters ResourceAllocation table to only show ResourceAllocation entries for which the user Owns (Owners table -> ResourceAllocation.Owner)
    • User edits necessary information, saves and exits or returns to main menu
  
  
• Managers
  
  • Managers are a level above Project Managers or TPMs
  • View/Edit Resource Utilization
    
    • user should be able to select a TPM's name from the drop down menu to filter the results, or view all results
  
  
• ​Report a Bug (frmBugReport)
  
  • Enter relevant information and submit
  • View Bug Reports in Bug Management Console (frmBugs)
    
    • Form auto-filters to only show bug reports submitted by the current user unless user is Admin
    • Selecting a bug from the list populated the relevant fields below
    • User can modify all fields except Status

So my basic problems are I'm not filtering the Copy of Owners form correctly, and I've screwed up the controls and sources for the frmBugs bug console.

I welcome any and all criticism and tips. Like I said, there is a lot of basic stuff that I unfortunately still don't know or don't have enough experience with to consistently use. There is a good big more to this database (Employee entry and Project entry system) that I cut out for this test DB because it's currently working well. Also, I apologize in advance for the spaces in the Object names. Chalk that up to inexperience from the getgo.

Once I get these two problems solved I'll have some more. I'll also tackle any that y'all bring up. And finally, I'm not asking yall to do my work for me (well, sort of...). I want portions of this DB to be fine tuned so that you can point newbies to a nice, wide open example that works to save people time in the future and provide inspiration.

Thanks!

[June7]

Should be saving HRID instead of name in Owners and ResourceAllocation. Actually, I don't see need for Owners table. ResourceAllocation should have HRID as owner fk instead of full name. Also, should not have first and last name fields. Retrieve that info from EmployeeData table in query joins.

Once user logs in, where is their ID retained? I don't see any event code associated with the LogInForm. If you want subsequent queries to filter on user ID (owner) criteria, that value must be held somewhere for reference. I have code in login form that sets value of a textbox on main menu which always stays open and so info is always available to queries and code.

Here is one way to build custom filter in a form http://datapigtechnologies.com/flash...tomfilter.html
Another method involves code to set the FilterOn and Filter properties of the form.

frmBugReport is not bound and fields are not bound. No control for Status input is available.

[Datech]

Thanks June!

I hate to admit it, but I haven't even read the section on queries in The Missing Manual yet... That's next on my list. All the queries that are in there were either written by my coworker or modified from an existing example. I'll have to get to work on getting rid of the owners table.

The userID and all other user information is retained through the With statement in the LogInForm and the basUserInfo module. It works pretty well throughout the entire code as far as I can tell (try logging in as a user or TPM). The only problem is the Bug form because I absentmindedly made a field named 'User', which messed with some things. I think that's part of my bug reporting problem.

frmBugReport is controlled with a With statement in the code as well. Status only needs to be changed by an Admin. frmBugs was my problem child, so I gave y'all the unfinished form because nothing I tried would work properly.

I'll check out the filter link you sent, but does anyone have any other suggestions on finishing frmBugs (I think the RecordSource is the problem) or a specific way to implement the filter? Going into a meeting right now, so I'll go through the datapig tutorial afterwards.

Thanks again!

[Datech]

Hey June, I'm working through getting rid of the Owners table, but I have a question. Only some users will ever be owners (project managers), so I don't need an entire employee list in my filter on the Copy of Owners form. That was the initial thought behind the Owners table.

In order to trim the list down I was thinking about adding a check box to the EmployeeData table to denote an Owner. Would that be a worthy implementation? Could I modify the filter created from the DataPig tutorial with something like Where Owner = True?

[Datech]

Hey June, I'm working through getting rid of the Owners table, but I have a question. Only some users will ever be owners (project managers), so I don't need an entire employee list in my filter on the Copy of Owners form. That was the initial thought behind the Owners table.

In order to trim the list down I was thinking about adding a check box to the EmployeeData table to denote an Owner. Would that be a worthy implementation? Could I modify the filter created from the DataPig tutorial with something like Where Owner = True?

It's kind of nice when you know enough to implement your own suggestion. I just added the Owner field that I mentioned to the query, and set Criteria: True, and Total: Group By and it works. Anything wrong with this?

[June7]

Yes, RowSource of combo and list boxes can be limited by WHERE criteria. The RowSource is, after all, a query in most cases (fixed lists of values is an option).

Yes, the checkbox in EmployeeData could eliminate the Owners table. All employees are either an owner or not.

You say you went ahead and did this and modified the RowSource? Sounds good to me.

[Datech]

Yes, RowSource of combo and list boxes can be limited by WHERE criteria. The RowSource is, after all, a query in most cases (fixed lists of values is an option).

Yes, the checkbox in EmployeeData could eliminate the Owners table. All employees are either an owner or not.

You say you went ahead and did this and modified the RowSource? Sounds good to me.

Awesome, all the changes I made work great and it's mostly how I want it. I'll update the test DB later.

In regards to the frmBugs, I want to change the query to display only bugs submitted by the current user. I've put in a text box that holds the value of the UserID, which is tied to the UserName of who submitted the bug. I'm having no luck getting this filter to work correctly though. Here's the SQL statement I'm using, and it's giving me nothing:

SELECT DISTINCTROW tblBugReport.BugNumber, tblBugReport.UserName AS [User], tblBugReport.Summary AS Summary, tblBugReport.Priority AS Priority, tblBugReport.Status AS StatusFROM tblBugReport
GROUP BY tblBugReport.BugNumber, tblBugReport.UserName, tblBugReport.Summary, tblBugReport.Priority, tblBugReport.Status
HAVING (((tblBugReport.UserName)=[Forms]![frmBugs].[txtUserID].[value]));

txtUserID is the text box containing the user name info on the frmBugs form. The code that populates the text box work, the query doesn't.

[June7]

Is that posted db the latest version?
frmBugs does not have a textbox txtUserID. There is a label called UserID_Label and it is associated with unbound listbox and has caption 'Select Bug'. There is also another label called Label2 not associated wth anything and it also has caption 'Select Bug' (it is hiding behind listbox).

I don't see any code populating textbox with user name info.

Assuming you get a textbox populated, the listbox RowSource would be:
SELECT DISTINCTROW BugNumber, UserName As [User Name], Summary, Priority, Status FROM tblBugReport WHERE (((tblBugReport.UserName)=[Forms]![frmBugs]![txtUserID]));

Then the listbox click event works.

Suggest code for 'Add New Bug' button':
DoCmd.OpenForm stDocName, , , , , acDialog
Me.Requery

[Datech]

Assuming you get a textbox populated, the listbox RowSource would be:
SELECT DISTINCTROW BugNumber, UserName As [User Name], Summary, Priority, Status FROM tblBugReport WHERE (((tblBugReport.UserName)=[Forms]![frmBugs]![txtUserID]));

Then the listbox click event works.

Your assumption was correct. I'll try to post the new DB after my meeting today. The SQL statement was mostly correct, but the txtUserID needed a '.value' as well, like so:

SELECT DISTINCTROW tblBugReport.BugNumber, tblBugReport.UserName AS [User Name], tblBugReport.Summary AS Summary, tblBugReport.Priority AS Priority, tblBugReport.Status AS StatusFROM tblBugReport
WHERE (((tblBugReport.UserName)=[Forms]![frmBugs]![txtUserID].[value]));

I also needed to add Me.lstBugs.requery to the Form_Current event so that it would populate the correct data when you load the form. Again, I'l get an updated DB up later today. I'm not sure where I got HAVING from, but is there a simple explanation for the difference between HAVING and WHERE? Or should I just look that up?

Suggest code for 'Add New Bug' button':
DoCmd.OpenForm stDocName, , , , , acDialog
Me.Requery

What exactly would this do? The Add New Bug button already includes the following:

If IsLoaded("frmBugs") Then        Forms!frmBugs.Requery
        Forms!frmBugs.lstBugs.Requery
    End If

And IsLoaded is a utility function that just checks if a form is loaded.

[June7]

Save yourself some typing, .Value is not required. It is the default property of data controls and will be assumed if no property specified.

Never gave usage of WHERE vs HAVING much thought.

acDialog causes suspension of code execution until the form is closed. The next line to execute will cause the primary form to requery and this will make the new record display in the listbox and the record will be in the form's RecordSource.

[Datech]

Thanks June7! I uploaded the new DB for your viewing pleasure. I also included the employee and project stuff in their this time. Once my coworker gets the automation code done we'll have a first release on our hands! Now I get to go explore the wonderful world of reports, and make a new form that generates reports based on user selections. I'll be following the Datapig tutorials on that, bug any other suggestions? I'm going to leave this thread open and try to update it as I go so people can use the DB features as needed.

[June7]

I get an 'Invalid use of null' error when I login. The frmBugs does not open with the user textbox populated but I guess you will deal with that when you use code to open it. Good luck with your development effort.

[Datech]

I get an 'Invalid use of null' error when I login. The frmBugs does not open with the user textbox populated but I guess you will deal with that when you use code to open it. Good luck with your development effort.

Please submit a bug report using the console within the application so your problem can be handled appropriately.

Just kidding. I don't get the error when I log in. Did you open it as exclusive mode or some other way? Did you enable content for the VBA to work? The frmBugs won't populate unless there is a successful login with how the code is written. Also, I hid and locked the userID textbox.

Another question if you can log in correctly:
Follow these steps:

1. Log in with admin credentials (dvanbeek/daniel)
2. Then close just the main menu and any other windows, but not the DB
3. Reopen the login screen, and log in as anyone else (rbailey/rene)
4. Repeat step 2
5. Repeat step 1

You'll notice that step 1 gives you all choices at frmMainMenu, step 3 limits your choices, and step 5 still limits your choices even though you logged in as admin. This happens because frmMainMenu is still reading the User.ViewID as the previous user.

I know that this won't matter in a production setting because a user won't have multiple login credentials, but it is still evidence of bad coding practices somewhere.

I have this in the login form for the Log in click event:

With User        .AccessID = rst.Fields("AccessID")
        .ViewID = rst.Fields("ViewID")
        .Name = rst.Fields("Employee")
        .Active = rst.Fields("Active")
        .Password = rst.Fields("PWD")
        .SecurityID = rst.Fields("SecurityID")
        .UserID = rst.Fields("USERID")
    End With
    
    If User.ViewID >= 2 Then
        Forms!frmMainMenu.cmdEmpInfo.Visible = False
        Forms!frmMainMenu.cmdUserMgmt.Visible = False
        Forms!frmMainMenu.cmdEmpInfo.Enabled = False
        Forms!frmMainMenu.cmdUserMgmt.Enabled = False
        Forms!frmMainMenu.cmdResourceAlloc.Enabled = False
        Forms!frmMainMenu.cmdResourceAlloc.Visible = False
    End If

I have this in the main menu:

Private Sub Form_Current()'----------------------
'This code hides buttons based on user type
'Created by: Daniel VanBeek
'Date Created: 4/24/2012
'---------------------




If User.ViewID > 2 Then
    Me.cmdEmpInfo.Visible = False
    Me.cmdUserMgmt.Visible = False
    Me.cmdResourceAlloc.Visible = False
    Me.cmdEmpInfo.Enabled = False
    Me.cmdUserMgmt.Enabled = False
    Me.cmdResourceAlloc.Enabled = False
End If
    
End Sub

I replicated that because the Form_Current event would not trigger when the form is opened, and it wouldn't work with Load or Open. However, I think the real problem stems from the timing of all the code, because apparently the With statement writes the info after the form is opened and after it checks the UserID in both the LogInForm and the Main Menu. It therefore reads the previously stored UserID.

So, how should I change it so that it always reads the current userID right then?

[June7]

In code for the LogIn button click event, move the code setting User properties to the Else just before opening MainMenu.