Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-11-2019, 09:06 AM   #1
Alhakeem1977
Newly Registered User
 
Join Date: Jun 2017
Posts: 213
Thanks: 95
Thanked 0 Times in 0 Posts
Alhakeem1977 is on a distinguished road
Multi Approvals Task Management (Dashboard Control Form)

Hi All,

I am working on a Multi Approvals Task Management database and trying to set up a Dashboard Control Form which will allow me to predefined each task (Combobox) and it's concerned department.

How can I achieve this form of control?

As an example:

1. Task 1 should be assigned to four departments: Dept 1, Dept 3, Dept 4, Dept 2 >> End.
2. Task 2 should be assigned to two departments: Dept 1, Dept 2 >> End.
3. Task 1 should be assigned to three departments: Dept 1, Dept 2, Dept 4 >> End.

Thanks in advance!

Alhakeem1977 is offline   Reply With Quote
Old 10-11-2019, 09:23 AM   #2
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Multi Approvals Task Management (Dashboard Control Form)

you need to setup a Login form (with matching userTable with username, password, dept).
login form can be as simple as having a combobox with usernames and dept.
have a button (close button), but instead of closing the form make it's Visible property to False (hidden).
on the dashboard you can validate (allow/disallow) as per dept (from login form combo).
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 10-11-2019, 03:23 PM   #3
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,316
Thanks: 15
Thanked 1,601 Times in 1,520 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Multi Approvals Task Management (Dashboard Control Form)

I am confused by the question.
1. Dashboard forms are typically for display rather than user entry.
2. I don't know what ">> End" means

Are you asking how to assign more than one value to a field? If so, the answer is DON'T DO IT. Create a second table that will hold one row for each department assigned to the task. I would use a subform to assign departments to a task. Use a combo with a RowSource that lists department and just choose one. To prevent duplicates, I would create a unique index on the TaskDept table comprised of TaksID (the foreign key to the parent table) and DeptID (the foreign key to the Dept table.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 11-04-2019, 08:29 AM   #4
Alhakeem1977
Newly Registered User
 
Join Date: Jun 2017
Posts: 213
Thanks: 95
Thanked 0 Times in 0 Posts
Alhakeem1977 is on a distinguished road
Re: Multi Approvals Task Management (Dashboard Control Form)

I am so sorry for the delay, I am still struggling to overcome this issue, actually to clarify my need is that I want to create a control form that contains the FormName (pdf fillable forms will be uploaded to the database as text path) and it's flow through the departments it can be vary from one form to one another.

How can I structure the control form?
Sorry for my limited knowledge in Access.

Sent from my HUAWEI NXT-L29 using Tapatalk
Alhakeem1977 is offline   Reply With Quote
Old 11-04-2019, 09:26 PM   #5
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,316
Thanks: 15
Thanked 1,601 Times in 1,520 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Multi Approvals Task Management (Dashboard Control Form)

Are the documents categorized into types?
Is the path consistent for each type?

If the answers are both yes, you can create a type table to define categories of documents and you can create a path table with a row for each department in the path including a sequence number so that you can control the sequence.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 11-06-2019, 09:12 AM   #6
Alhakeem1977
Newly Registered User
 
Join Date: Jun 2017
Posts: 213
Thanks: 95
Thanked 0 Times in 0 Posts
Alhakeem1977 is on a distinguished road
Re: Multi Approvals Task Management (Dashboard Control Form)

Quote:
Originally Posted by Pat Hartman View Post
Are the documents categorized into types?
Is the path consistent for each type?

If the answers are both yes, you can create a type table to define categories of documents and you can create a path table with a row for each department in the path including a sequence number so that you can control the sequence.
Thanks for your response.
Actually I was waiting for your response, but I missed the notification.

Yes, the documents are categorized and the path is consistent for each document too.

Still I am confused to structure the transactions table [Cases] that it has a columns for each department first one the Customer service department who create the entry that they chose the document type (predefined from the control form) upload the document to the system then it will be assigned (workflow) to the concern departments and it can be vary to four, three or two departments depends on the document type.

That's my aim, as you stated for sure it will automate the process but it's a bit difficult for me to implement it, do you prefer to upload my database or you can give me more clarifications to structure my tables?

Thanks a lot in advance!





Sent from my HUAWEI NXT-L29 using Tapatalk
Alhakeem1977 is offline   Reply With Quote
Old 11-06-2019, 05:35 PM   #7
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,768
Thanks: 93
Thanked 1,726 Times in 1,597 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Multi Approvals Task Management (Dashboard Control Form)

You are looking for a linear list of approvals. IF the list MUST be approved in a specific order, then the simplest way is an ordered junction table.

You have two main tables: your documents and your departments. If both tables have a usable prime key, then your solution is a third table that we call a junction. You will also need a users table that lets you track who did what to whom at any given time, a persons table perhaps. In that table, you would need a Department ID field showing which department the person represents, and perhaps 0 if this person can use the DB but doesn't have approval authority, only read-only / viewing authority.

DocDepJunction:

DocNum: Long, foreign key to Docs table
DepNum: Long, foreign key to Deps table
OrdNum: Long, shows the order in which the the approvals must occur: 1 must be first, 2 must be 2nd etc.
AppDate: Date, the date/time of the approval
AppPers: Long, foreign key to Persons table (representing who approved it)

You have one record in this table for each combination of document and department, so for a given document, you have N+1 records where N is the number of departments and the +1 is the "END department" entry that says you are done.

Now build a query that only shows the record with the LOWEST ordinal number because that record represents the department that must approve the document next.

Then you can build joins between the junction table and the other two tables. When a person logs in, you can query that list of "lowest ordinals" that have the person's department ID. That person can then review and approve as needed. Obviously, you will have a special value representing the "END" department, which doesn't need approval and would use that END department to make lists of documents that are fully approved.

For statuses, you can query this same junction for documents where the lowest available entry is the "END department" entry - and thus get a list of closed documents. Or you can make a query against the same table for cases where the lowest entry is NOT the "END department" thus seeing what is still pending approval (and implicitly, which department needs to approve it.)

This is just an idea, worth pursuing only if it makes sense to you. So I'll stop here for new and let you think about it.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 11-07-2019, 01:42 PM   #8
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,316
Thanks: 15
Thanked 1,601 Times in 1,520 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Multi Approvals Task Management (Dashboard Control Form)

When working in a relational database environment think DOWN rather than ACROSS. Data repeats as rows. Each department is on a separate row. You would never repeat columns as you do with Excel.

This basic difference in point of view is what enables you do do something ONCE in Access that you would have to do multiple times (across) in Excel and which causes NO code or query changes in Access but which causes function and code changes in Excel. Every time you need to add a new department column to your spreadsheet, you need to modify ALL functions to include the new column. When you add a new department row to a table, nothing has to change.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 11-07-2019, 07:04 PM   #9
Alhakeem1977
Newly Registered User
 
Join Date: Jun 2017
Posts: 213
Thanks: 95
Thanked 0 Times in 0 Posts
Alhakeem1977 is on a distinguished road
Re: Multi Approvals Task Management (Dashboard Control Form)

Quote:
Originally Posted by Pat Hartman View Post
When working in a relational database environment think DOWN rather than ACROSS. Data repeats as rows. Each department is on a separate row. You would never repeat columns as you do with Excel.

This basic difference in point of view is what enables you do do something ONCE in Access that you would have to do multiple times (across) in Excel and which causes NO code or query changes in Access but which causes function and code changes in Excel. Every time you need to add a new department column to your spreadsheet, you need to modify ALL functions to include the new column. When you add a new department row to a table, nothing has to change.
Thanks for your clarifications, that's exactly what I want, unfortunately, I am out of my country now, once I reach I will let you know the progress of my application.

Sent from my HUAWEI NXT-L29 using Tapatalk

Alhakeem1977 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Step-wise task management database query (Indexed data pull) nstroem Queries 3 10-08-2018 01:26 PM
Multi Task Command Button checkit81 General 1 04-09-2014 07:07 PM
10 week multi-task planning jerem Forms 1 03-04-2013 02:03 AM
Ideas for Multiple Approvals of Form ramez75 General 14 10-14-2010 05:00 PM
Question Task management database, many questions, plz help MagicWorld General 0 02-01-2010 08:31 AM




All times are GMT -8. The time now is 02:22 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World