How to filter list with username? 1

[fdalmoro]

Hi, I am trying to implement a paperless Purchase Order system for our company. To approve a PO the name of the managers that can approve them is in a lookup table, the managers just pick their name from the list and the PO is approved. The approval look-up field is blocked from being edited through the SQL server, anyone that's not supposed to approve PO's can't.

The problem is that the managers can approve the PO under any name that is in the list, not just theirs. How can I fix it to limit the managers so that they can only approve a PO only if they put in their name?

I have setup users and group policies in the database itself but this only limits access to forms tables and so on as a whole, not the items within the forms.

I think the way to do it is to have some code that will look at the username that is logged in to the database and filter the names out of the list that don't correspond.

I hope this is enough information,

BTW, I don't have much experience with VB (noob), that's why I don't even know where to start.

Thanks for the help in advance.

 

[mpastore]

Add manager user name to the manager table then filter your list query to account for this.

mylist.recordsource = "select * from tbManagers where mgr_user_name = getusername()"

Since you have user and group poicies in place you can lock down the manager table so no one can edit it except a person with Admin privs.

 

[fdalmoro]

Ok, I created a query with those spicfications and thanks for the help. I still can't get it to work though because it says that getusername() is not a valid function.

I tried User() and Username() also but it still does not work, gives me the same error.

I can't paste what the query looks like but this is the
SQL equivalent.

SELECT LU_APPROVAL.FIRST_LAST_NAME, LU_APPROVAL.USER_NAME
FROM LU_APPROVAL
WHERE (((LU_APPROVAL.USER_NAME)=User()))
ORDER BY LU_APPROVAL.FIRST_LAST_NAME;
[code/]

I think I just have to find the right username function and it will work but I looked in the expression builder and there is nothing that even comes close. Any ideas??

 

[fdalmoro]

Hey nevermind, I found it. It was CurrentUser()


here is the final SQL statement that works..

SELECT LU_APPROVAL.FIRST_LAST_NAME
FROM LU_APPROVAL
GROUP BY LU_APPROVAL.FIRST_LAST_NAME, LU_APPROVAL.USER_NAME
HAVING (((LU_APPROVAL.USER_NAME)=CurrentUser()))
ORDER BY LU_APPROVAL.FIRST_LAST_NAME;

[code/]

Thanks for the help,

 

[mpastore]

Right, currentuser(), don't know what I was thinking of.