Compare Environ Username to a Table

[aron.ridgway]

Hi There,

Im trying to use the Environ Username function, which i have working! But i want to compare the result with a Username Table to determine if they have access etc...

Any ideas?

thanks
Aron

 

[pr2-eugin]

Simple answer would be to use a DLookup.

DLookup("userAccess", "userTableName", "userName = '" & Environ("username") & "'")

The above code will give what user level access the user has.

 

[aron.ridgway]

Hi Paul

Thank you for the quick response! Where would i place the Dlookup code?

Is there a way of having an IF statement, like

If Environ(Username) is in tblUser then
Open Form
msgbox "Welcome" & Environ
else
msgbox Environ "you do not have access! Contact admin to request approval"
end if

Something along them lines, i havent created much VBA in access before so not quite sure how to refer to tables etc...

thanks!

 

[pr2-eugin]

Thank you for the quick response! Where would i place the Dlookup code?

Let me ask you the same question, where do you want to check this information?

Is there a way of having an IF statement, like

You can ! If you do not need to use the information, then you can even use a DCount.

Something along them lines, i havent created much VBA in access before so not quite sure how to refer to tables etc...

Explain the process flow, maybe I could help !

 

[aron.ridgway]

I will try and explain the process,
I want it to run on a From open Event.
When the Form opens i want to Check the Environ(Username) against a Table of listed Users (tblUser).
If the environ matches a result in the table then the form opens and welcomes the user with a message.
This will then filter the tables depending on what permissions are set up for that user.


If they are not on the User list then the form closes and they recieve a message saying they do not have permission.

hopefully this is a but more help?

thanks

 

[pr2-eugin]

Here is the basic.

Private Sub Form_Open(Cancel As Integer)
    If DCount("*", "userTable", "userName = '" & Environ("username") & "'") = 0 Then
        MsgBox "You do not have access to the DataBase, please contact the Admin.", vbCritical, "NO ACCESS !"
        Cancel = True
    Else
        MsgBox "Welcome " & Environ("username") & " !", vbInformation 
    End If
End Sub

 

[aron.ridgway]

Perfect that works a treat thank you!

How should i go about filtering, the company table based on the users permissions. Will i have to create a query to do this bit?

Basically we have 5 companies that have there own list of suppliers. I want to have a form that filters the suppliers based on the result of the User?

Hopefully that makes some sort of sense?

thanks
Aron

 

[pr2-eugin]

Not much sense, but vaguely. Here is something?

Private Sub Form_Open(Cancel As Integer)
    If DCount("*", "userTable", "userName = '" & Environ("username") & "'") = 0 Then
        MsgBox "You do not have access to the DataBase, please contact the Admin.", vbCritical, "NO ACCESS !"
        Cancel = True
    Else
        MsgBox "Welcome " & Environ("username") & " !", vbInformation 
        Me.Filter = "suppliers = '" & Environ("username") & "'"
        Me.FilterOn = True
    End If
End Sub

 

[aron.ridgway]

Thank you i will try it out,

Im trying to get my head around how i should set up a table.

I have a User Table and Company Table.

I want to create a user permission table that lists the Users and in the columns have the 5 different companies with tick boxes so i can check or un check to give them access or not?

Im sure this is simple stuff but cant figure it out?!

thanks

 

[pr2-eugin]

That would not be the best idea, you need the following structure,

 

[aron.ridgway]

That would not be the best idea, you need the following structure,

I dont quite understand you comment? you need the following structure but nothing seems to follow?

thanks

 

[pr2-eugin]

Ummm.. Can you not see an image following that?

 

[aron.ridgway]

that's better i can see the table design now! So if i was to have multiple companies to one user, i would have 5 user id's if they had access to the 5 companies?

Can you link the permission table to show buttons for each company they have access to?

thanks

 

[Bilbo_Baggins_Esq]

that's better i can see the table design now! So if i was to have multiple companies to one user, i would have 5 user id's if they had access to the 5 companies?

Can you link the permission table to show buttons for each company they have access to?

thanks

what is shown in the picture is NOT a table design, it is a set of table RELATIONSHIPS.

The answer to a literal interpretation of your response would be NO.

in tblUsers, each user has a SINGLE record with his/her own unique UserID

in tblCompanies, each company has a SINGLE record with its own unique CompanyID

In tblUsersCompanies, you can create an association between a user's unique UserID and a Companies unique CompanyID.

If a User is associated with multiple companies, then in tblUsersCompanies there would be multiple records with that UserID but EACH OF THE CompanyIDs would be different.
Likewise, if a company has multiple Users associated with it, that company would have multiple records in the tblUsersCompanies, but again, each of the UserIDs would be different.