Approval/Notification of Record Change

[developing]

Hello

how goes it? I am developing a database which needs approvals from two seperate usetrs before record gets updated. i need ideas for the approval part.

So a record is changed by a user, this event sends a message to admin. Admin A logs in and approves the change. Admin B logs in and approves the change. It is only after the two approvals that the change is made in the main table..

So ideas on how to go about the approval system? any links are appreceated

[ADezii]

Hello

how goes it? I am developing a database which needs approvals from two seperate usetrs before record gets updated. i need ideas for the approval part.

So a record is changed by a user, this event sends a message to admin. Admin A logs in and approves the change. Admin B logs in and approves the change. It is only after the two approvals that the change is made in the main table..

So ideas on how to go about the approval system? any links are appreceated

Two independant Tables which are structurally equal. One Table, let's say tblNonApprovedC hanges, has either none or a single Admin approval, The other Table, let's say tblApprovedChan ges, contains only Records which have been approved by 2 Admins. Once a Record has been saved and approved by 2 Admins in tblNonApprovedC hanges, it is appended to tblApprovedChan ges, then deleted from tblNonApprovedC hanges. I really do not see a simply way to implement what you are trying to accomplish without a parallel/interim Table.

[developing]

yes, ADezzi thats what i was thinking too..two tables would make life easier....how would you keep track of the two admin conformations though? a bool value? something along those lines that i dont know about?

[ADezii]

yes, ADezzi thats what i was thinking too..two tables would make life easier....how would you keep track of the two admin conformations though? a bool value? something along those lines that i dont know about?

In the AfterUpdate() Event of your Primary Form, the one based on tblNonApprovedC hanges, place the following code which will ensure that both Admins approve changes before any action is taken:

If Not IsNull(Me![txtAdmin1]) And Not IsNull(Me![txtAdmin2]) Then
  'Record is already saved, now Append to tblApprovedChanges then,
  'if no Errors, DELETE
End If

[NeoPa]

You'd need a way of identifying each user (Network Logon is a good way) then you would have a table of Admins. When someone from the Admin table approves a record then his ID is added to [Confirmation ID 1] of the record unless that is already populated in which case the record is doubly approved and can be moved to the accepted table.

[developing]

this is what i came up with:

4 tables.

link between first three tables is customer number
[Main] customer info etc including number
[MainTemp] customer number
[MainFinal] customer info etc including number

[UserAccounts] every account is assigned a security rank

user edits record
-email sent to Admin1
-records edited in [Main]

Admin1 logs in and clicks approved
- only customer number is stored in [MainTemp]
-email sent to Admin2

Admin2 logs in aprroves the change
-with customer number from [MainTemp] as the criteria, all the info is copied over from [Main] to [MainFinal]


data is updated after two approvals....ex cept, there will be two huge tables [Main] and [MainFinal] at all times....


so how do i improve this

[developing]

scratch that....another idea based on what NeoPa said,

2 tables.

[Main] customer info with 2 true/false columns for admin approvals
[MainFinal] customer info

when both are true for any given customer, that customer info gets appended to [MainFinal]

one problem that i am sure to run into..read from [MainFinal] and write to [Main]...how do come around this?

[NeoPa]

scratch that....another idea based on what NeoPa said,

2 tables.

[Main] customer info with 2 true/false columns for admin approvals
[MainFinal] customer info

when both are true for any given customer, that customer info gets appended to [MainFinal]

one problem that i am sure to run into..read from [MainFinal] and write to [Main]...how do come around this?

They can't be True/False, as you'd never know which Admin had already approved. It would have to be the admin ID (Name or whatever).
Pseudo-SQL :

INSERT INTO [MainFinal] ([Field1],[Field2],...)
SELECT [Field1],[Field2],...
FROM [Main]
WHERE ([Admin1] Not Is Null)
  AND ([Admin2] Not Is Null)

[thriller16]

Hello

how goes it? I am developing a database which needs approvals from two seperate usetrs before record gets updated. i need ideas for the approval part.

So a record is changed by a user, this event sends a message to admin. Admin A logs in and approves the change. Admin B logs in and approves the change. It is only after the two approvals that the change is made in the main table..

So ideas on how to go about the approval system? any links are appreceated

Hello,

Iam developing nearly same database which contains approval of admin in the case of record changes by users. But i have a linked table and when users change data in this table i want access to send mail to admin for the approval.
What do you think how to build this database?

[NeoPa]

Please do not ask your question in somebody else's thread. This is hijacking and is against site rules.

Also, this forum is designed to help out programmers who run into difficulty with their projects. The members who answer questions here are volunteers. Please do not ask them to design your database for you as they get paid to do that as professionals.

If you wish to have a database designed please post in the Jobs forum and our experts will be happy to submit bids on the project.

MODERATOR.