need to add Privileges..

[Stephanie53]

This is the code I have now to create a Purchase order..

there are 3 steps to creating per the policy here...

1. Create the PO from Sales


2. inventory Clerk confirms all pricing
3. Purchasing Supervisor to approve and submit the purchase

So I created the privileges as follows under Privileges Module

Public Function IsCreatePO() As Boolean    IsCreatePO = EmployeeHas(GetCurrentUserID(), CreatePO_Privilege)
    
End Function

Public Function IsSubmitPOForApproval() As Boolean
    IsSubmitPOForApproval = EmployeeHas(GetCurrentUserID(), SubmitPOForApproval_Privilege)
    
End Function

Public Function CanApprovePurchases() As Boolean
    
    CanApprovePurchases = EmployeeHas(GetCurrentUserID(), PurchaseApprovals_Privilege)
End Function

And under Inventory Module I have this..

Function RestockProduct(ProductID As Long) As Boolean
    Dim SupplierID As Long
    Dim QtyToOrder As Long
    Dim PurchaseOrderID As Long
    Dim UnitCost As Long
    
    QtyToOrder = GetQtyToReorder(ProductID)
    
    If QtyToOrder > 0 Then
        
        SupplierID = FindProductSupplier(ProductID)
    
        If SupplierID > 0 Then
                          
            ' Generate new Purchase Order if necessary
            If PurchaseOrderID = 0 Then
                If Not PurchaseOrders.Create(SupplierID, GetCurrentUserID(), -1, PurchaseOrderID) Then
                    Exit Function
                End If
            End If
            
            ' Set unit cost to standard cost for product
            StandardCost = GetStandardCost(Nz(ProductID, 0))
            
            ' Add product line item to Purchase Order
            If Not PurchaseOrders.CreateLineItem(PurchaseOrderID, ProductID, UnitCost, QtyToOrder) Then
                Exit Function
            End If
        Else
            ' Suggested Enhancement: Handle case where product does not have a supplier
        End If
        
    End If
    RestockProduct = True
End Function

Where can i put these Privileges when they go to submit for the next step?

[Stephanie53]

Here is what the code is for the form if this would help...

Option Compare Database
Option Explicit
Public Enum PurchaseOrderStatusEnum
    New_PurchaseOrder = 0
    Create_PurchaseOrder = 1
    Submitted_PurchaseOrder = 2
    Approved_PurchaseOrder = 3
    Closed_PurchaseOrder = 4
End Enum

Function Generate(SupplierID As Long, ProductID As Long, Quantity As Long, OrderID As Long, PurchaseOrderID As Long) As Boolean
    Dim UnitCost As Long
    UnitCost = GetStandardCost(Nz(ProductID, 0))
    If Create(SupplierID, GetCurrentUserID(), OrderID, PurchaseOrderID) Then
        Generate = CreateLineItem(PurchaseOrderID, ProductID, UnitCost, Quantity)
    End If
End Function

Function Create(SupplierID As Long, EmployeeID As Long, OrderID As Long, PurchaseOrderID As Long) As Boolean
    Dim rsw As New RecordsetWrapper
    If rsw.OpenRecordset("Purchase Orders") Then
        With rsw.Recordset
            .AddNew
            ![Supplier ID] = SupplierID
            If EmployeeID > 0 Then
                ![Created By] = EmployeeID
                ![Creation Date] = Now()
                ![Submitted By] = EmployeeID
                ![Submitted Date] = Now()
                ![Status ID] = Submitted_PurchaseOrder
            End If
            
            If OrderID > 0 Then
                ![Notes] = InsertString(PurchaseGeneratedBasedOnOrder, CStr(OrderID))
            End If
            If rsw.Update Then
                .Bookmark = .LastModified
                PurchaseOrderID = ![Purchase Order ID]
                Create = True
            End If
        End With
    End If
End Function

Function CreateLineItem(PurchaseOrderID As Long, ProductID As Long, UnitCost As Long, Quantity As Long) As Boolean
    Dim rsw As New RecordsetWrapper
    If rsw.OpenRecordset("Purchase Order Details") Then
        With rsw.Recordset
            .AddNew
            ![Purchase Order ID] = PurchaseOrderID
            ![Product ID] = ProductID
            ![Quantity] = Quantity
            ![Standard Cost] = StandardCost
            CreateLineItem = rsw.Update
        End With
    End If
End Function

Sub OpenOrder(Optional PurchaseOrderID As Long)
    If (PurchaseOrderID > 0) Then
        DoCmd.OpenForm "Purchase Order Details", acNormal, , "[Purchase Order ID]=" & PurchaseOrderID, acFormEdit, acDialog
    Else
        DoCmd.OpenForm "Purchase Order Details", acNormal, , , acFormAdd, acDialog
    End If
End Sub

Sub NewOrder()
    OpenOrder
End Sub

Function Delete(PurchaseOrderID As Long) As Boolean
    Dim rsw As New RecordsetWrapper
    If rsw.OpenRecordset("Purchase Orders", "[Purchase Order ID] = " & PurchaseOrderID) Then
        Delete = rsw.Delete
    End If
End Function

Private Function SetStatus(PurchaseOrderID As Long, Status As PurchaseOrderStatusEnum) As Boolean
    Dim rsw As New RecordsetWrapper
    If rsw.OpenRecordset("Purchase Orders", "[Purchase Order ID] = " & PurchaseOrderID) Then
        With rsw.Recordset
            If Not .EOF Then
                .Edit
                ![Status ID] = Status
                Select Case Status
                Case New_PurchaseOrder
                    ![Creation Date] = Now()
                    ![Created By] = GetCurrentUserID
                Case Submitted_PurchaseOrder
                    ![Submitted Date] = Now()
                    ![Submitted By] = GetCurrentUserID
                Case Approved_PurchaseOrder
                    ![Approved Date] = Now()
                    ![Approved By] = GetCurrentUserID
                End Select
                SetStatus = rsw.Update
            End If
        End With
    End If
End Function

Function GetStatus(PurchaseOrderID) As PurchaseOrderStatusEnum
    If IsNull(PurchaseOrderID) Then
        GetStatus = New_PurchaseOrder
    Else
        GetStatus = DLookupNumberWrapper("[Status ID]", "Purchase Orders", "[Purchase Order ID] = " & PurchaseOrderID, New_PurchaseOrder)
    End If
End Function

Function MarkApproved(PurchaseOrderID As Long) As Boolean
    If Not Privileges.CanApprovePurchases() Then
        Exit Function
    End If
    If SetStatus(PurchaseOrderID, Approved_PurchaseOrder) Then
        MarkApproved = True
    End If
End Function

Function MarkSubmitted(PurchaseOrderID As Long) As Boolean
    MarkSubmitted = SetStatus(PurchaseOrderID, Submitted_PurchaseOrder)
End Function

Function Exists(PurchaseOrderID As Long) As Boolean
    Exists = Not IsNull(DLookupWrapper("[Purchase Order ID]", "Purchase Orders", "[Purchase Order ID]=" & PurchaseOrderID))
End Function

Function GetStandardCost(lProductID As Long) As Currency
    GetStandardCost = DLookupNumberWrapper("[Standard Cost]", "Products", "[ID]=" & lProductID)
End Function

Function GetListPrice(lProductID As Long) As Currency
    GetListPrice = DLookupNumberWrapper("
[List Price]", "Products", "[ID] = " & lProductID)
End Function

[Perceptus]

So you need only a certain type of user to be able to submit and approve? Im confused at the request of what you are trying to achieve.

[Stephanie53]

Yes i have set up privileges for each employee and have assigned those privileges on the Privileges Table Above in the first one is under the Module Privileges and part of the Enum of those allowed to do each position

[Perceptus]

Are the privileges table you speak of a custom design or the built in one?

[Stephanie53]

Here is it on table..

Privilege ID	Privilege Name
2	Purchase Approvals
3	Sales by Category
4	Sales by Country
5	Sales by Customers
6	Sales by Employees
7	Sales by Product
8	Employee Phone Book
9	Employee Address Book
10	Monthly Sales
11	Yearly Sales Report
12	Quarterly Sales Report
13	Price Changes
16	QuoteOrderPeachtree
17	Production
18	QualityControl
19	Shipping
20	Accounting
21	Completed
22	CreatePO
23	SubmitPOForApproval
24	Add New Inventory

this is how its assigned to each employee


the Privileges Module looks like this...

Option Compare Database
Option Explicit
Public Enum PrivilegeEnum
Administrator_Privilege = 1
PurchaseApprovals_Privilege = 2
SalesbyCategory_Privilege = 3
SalesbyCountry_Privilege = 4
SalesbyCustomers_Privilege = 5
SalesbyEmployees_Privilege = 6
SalesbyProduct_Privilege = 7
EmployeePhoneBook_Privilege = 8
EmployeeAddressBook_Privilege = 9
MonthlySales_Privilege = 10
YearlySalesReport_Privilege = 11
QuarterlySalesReport_Prvilege = 12
PriceChange_Privilege = 13
QuoteOrderPeachtree_Privilege = 16
Production_Privilege = 17
QualityControl_Privilege = 18
Shipping_Privilege = 19
Acounting_Privilege = 20
Completed_Privilege = 21
CreatePO_Privilege = 22
SubmitPOForApproval_Privilege = 23
AddNewInventory = 24

End Enum
Private Function EmployeeHas(EmployeeID As Long, PrivilegeID As PrivilegeEnum) As Boolean
EmployeeHas = DCountWrapper("*", "Employee Privileges", "[Employee ID]=" & EmployeeID & " AND [Privilege ID]=" & PrivilegeID) > 0
End Function
Public Function CanApprovePurchases() As Boolean

CanApprovePurchases = EmployeeHas(GetCurrentUserID(), PurchaseApprovals_Privilege)
End Function
Public Function IsAdministrator() As Boolean
IsAdministrator = EmployeeHas(GetCurrentUserID(), Administrator_Privilege)

End Function
Public Function IsSalesbyCatagory() As Boolean
IsSalesbyCategory = EmployeeHas(GetCurrentUserID(), SalesbyCategory_Privilege)

End Function
Public Function IsSalesbyCountry() As Boolean
IsSalesbyCountry = EmployeeHas(GetCurrentUserID(), SalesbyCountry_Privilege)

End Function
Public Function IsSalesbyCustomers() As Boolean
IsSalesbyCustomers = EmployeeHas(GetCurrentUserID(), SalesbyCustomers_Privilege)

End Function
Public Function IsSalesbyProduct() As Boolean
IsSalesbyProduct = EmployeeHas(GetCurrentUserID(), SalesbyProduct_Privilege)

End Function
Public Function IsEmployeePhoneBook() As Boolean
IsEmployeePhoneBook = EmployeeHas(GetCurrentUserID(), EmployeePhoneBook_Privilege)

End Function
Public Function IsEmployeeAddressBook() As Boolean
IsEmployeeAddressBook = EmployeeHas(GetCurrentUserID(), EmployeeAddressBook_Privilege)

End Function
Public Function IsMonthlySales() As Boolean
IsMonthlySales = EmployeeHas(GetCurrentUserID(), MonthlySales_Privilege)

End Function
Public Function IsYearlySalesReport() As Boolean
IsYearlySalesReport = EmployeeHas(GetCurrentUserID(), YearlySalesReport_Privilege)

End Function
Public Function IsQuarterlySalesReport() As Boolean
IsQuarterlySalesReport = EmployeeHas(GetCurrentUserID(), QuarterlySalesReport_Privilege)

End Function
Public Function IsPriceChange() As Boolean
IsPriceChange = EmployeeHas(GetCurrentUserID(), PriceChange_Privilege)

End Function
Public Function IsQuoteOrderPeachtree() As Boolean
IsQuoteOrderPeachtree = EmployeeHas(GetCurrentUserID(), QuoteOrderPeachtree_Privilege)

End Function
Public Function IsProduction() As Boolean
IsProduction = EmployeeHas(GetCurrentUserID(), Production_Privilege)

End Function
Public Function IsQualityControl() As Boolean
IsQualityControl = EmployeeHas(GetCurrentUserID(), QualityControl_Privilege)

End Function
Public Function IsShipping() As Boolean
IsShipping = EmployeeHas(GetCurrentUserID(), Shipping_Privilege)

End Function
Public Function IsAcounting() As Boolean
IsAcounting = EmployeeHas(GetCurrentUserID(), Acounting_Privilege)

End Function
Public Function IsCompleted() As Boolean
IsCompleted = EmployeeHas(GetCurrentUserID(), Completed_Privilege)

End Function
Public Function IsCreatePO() As Boolean
IsCreatePO = EmployeeHas(GetCurrentUserID(), CreatePO_Privilege)

End Function
Public Function IsSubmitPOForApproval() As Boolean
IsSubmitPOForApproval = EmployeeHas(GetCurrentUserID(), SubmitPOForApproval_Privilege)

End Function
Public Function IsAddNewInventory() As Boolean
IsAddNewInventory = EmployeeHas(GetCurrentUserID(), AddNewInventory_Privilege)

End Function
Public Function GetCurrentUserID() As Long
GetCurrentUserID = Nz(TempVars![CurrentUserID], 0)
End Function

my question is this...in the purchasing order form how would i assign in the VBA for each employee that has the permission for each step in the code provide in post #1 that has todo with Purchasing?

[Stephanie53]

now i am posting to this one...

if a VBA cant be created with each step i need to have taken can i just create a macro for the limitations for each employee? unfortunately there is alot of steps that this company only want certain personnel viewing and editing and appoving

[Stephanie53]

I guess I stumped everyone on this one...

[Stephanie53]

Paul,

i have one more issue and this DB is DONE - Stick a fork and make it WELL DONE (at least for now until we do beta testing next week..lol)

Can you help me out with this issue?

Let me explain this one here on what i need done...

Let me know if you need a more updated DB since from yesterday too..

here are the steps that need to be taken:

1. Create the PO from Sales – Status is Created PO - Sales can not SUBMIT for approval only inventory clerk has this privilege – they are not to be able to get into the rest of this Purchase Order other then to create one.
2. inventory Clerk confirms all pricing – then submits for approval – Status is Submitted for Approval to Purchasing Supervisor to complete – inventory clerk will have the access to also receive and post it to inventory to fill in back orders 3. Purchasing Supervisor to approve and submit the purchase – and have access to all of Purchasing Orders

As you can see i have created all the privileges and have assigned who has what privilege

and in the Order Form if a Sales Rep (say for instance Stephen Plunkett) was to have a customer call in and want a certain item and we are out of stock of it that it will ask "Do you want to create a Purchase Order"

On the purchase order i need to remove the name where it comes up the second time for Submit for Approval (i am editing that button/link to where only the inventory clerk Ian Fisher is the only one besides Purchasing Supv/Manager are the only ones allowed to process that far)

I also need all this to go on a report which i can create per the status so each person can run their daily reports and see what has been submitted for approval, aprroved and received in inventory

Thanks again for all your support in this and for all the other help you have done for me :-)

Stephanie