Users and Passwords on 97 DB

[mattpearcey]

I have a database that has grown to a state where i need more people to use it, but to be able to view different records.

I initially conquered this, by having 3 seperate frontends for each of my branches. With tabs showing the difference records for each branch. Therefore, branch A could not see the records of branch B, and so on. However, i now need to set this up so that within the branches, Salesperson A can only see those records assigned to him within that branch. Now i have made this work fine with the below code

Private Sub Command8_Click()
On Error GoTo Err_Command8_Click
Dim SQL$, rs
Dim stDocName As String
Dim stLinkCriteria As String
logon = SalesPersonAfrica

SQL$ = "select * from tblSalesPerson where SalesPersonAfrica = '" & logon & "' and password = '" & Password & "'"
Set rs = CurrentDb().OpenRecordset(SQL$)

If Not rs.EOF Then


stDocName = "BankDetails" '"fsubBankDetails" '"BankDetails"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Else
MsgBox ("Logon failed&quot

End If

Exit_Command8_Click:
Exit Sub

Err_Command8_Click:
MsgBox Err.Description
Resume Exit_Command8_Click

End Sub

This works by having a opening form that the users sees to begin with, and they select their username and is mirrored in the main table. IF they enter that, and their correct password, which i set in a salesperson table, then they open the database to view all the records with their name assigned to them.

However, my problem is that i need this to work also for salesmanagers. I need them to see all the records, so i need to set a query to make them select their username (which is not assigned to anything) to view all records. But i cannot get this to work. It just comes up with no records at all. The code i have is below, that doesnt work.

Please help.


Option Compare Database

Public logon

Function strLogon()
If logon = "Orla Mahon" Then
strLogon = "Rob Koch and Orla Mahon and Neil Herbert"
Else
strLogon = logon
End If
MsgBox (strLogon)
End Function Thank you for your help already.

Matt Pearcey

 

[JeremyNYC]

Using multiple criteria, you can't do this:

WHERE field1 = 'Tom' And 'Dick' And 'Harry'

you have to do this:

WHERE field1 = 'Tom' Or Field1 = 'Dick' Or Field1 = 'Harry'

But much easier than this would be to add another field to your table that stores the person's rank as an integer. Then you could just get all the rows where the rank of the related person is equal to or lesser than this person's rank. You would do this only for people of a certain rank or higher. Others would use the sql above.

The ranks could have a lookup table that translate to actual titles, so that they make sense, and so you can choose them from a combo box.

But, as I think I mentioned in another thread, I would junk the home-made security and use Access security. Then you could have different user groups and determine which records to view based on membership in different groups. Code to do all that is in the Access FAQ.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[tpowers]

Hello JeremyNYC, Now I understand that you are more for people using the access security but I was wondering if you could help me out with this question of mine.

I have created a form that when you log on it will grab your network user logon. I then created a employee table that holds the following fields:

Employee Name, Email Address, EmployeeNo, and Logon lev.

I have a thread open called checking data that talks about this, however after being helped I was still losted. What I am tring to do is when a user opens the database my main menu pops up. In this main menu I have placed the following code:


Dim varUser As Variant
Dim userName As String
Dim intusererror As Integer
userName = CreateObject("Wscript.Network&quot.userName
varUser = DLookup "[LogonLev]", "EmployeeInformation", "[EmployeeNo]" = userName)
SecurLev.Value = varUser
If SecurLev.Value = 1 Then
intusererror = MsgBox("You Autherized to few this screen.", vbOKOnly, "Invalid user&quot
Else:
If SecurLev.Value = 2 Then
intusererror = MsgBox("You Autherized to few this screen, but your access is limited.", vbOKOnly, "Invalid user&quot
Else:
If SecurLev.Value = Null Then
'Code to handle un-authorized person
intusererror = MsgBox("You are not Autherized to few this screen. If you feel you have reached this message in error please contact your Administrator.", vbOKOnly, "Invalid user&quot
'Else
' PersonTakingCAll.Value = CStr(varUser)
'lblUserName.Caption = userName
End If
End If
End If

Can you please tell me what I am doing wrong here?

Thank you in advance

 

[JeremyNYC]

TPowers,

Post the thread number for your other thread and I'll check it out in that thread. I don't want Matt's thread to not get any attention because we get sidetracked on another topic.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[tpowers]

JeremyNYC, here is the tread number

thread702-412834

 

[mattpearcey]

Ok - i have this working, kind of, but i cannot get the users that i need to be able to see all the records to work.

Basically i need to alter this code to work, but cannot make it happen at present.

Option Compare Database

Public logon

Function strLogon()
If logon = "Rosemary Soluade" Then
strLogon = "SELECT * FROM [Bank Details]"
Else
strLogon = logon
End If
MsgBox (strLogon)
End Function

I want this to work so as that If Rosemary Soluade is selected, then she can see all records from all the salespersons. But this is not working at present. The main table that holds the sales person field is called Bank Details, the field is called SalesPersonAfrica.

Any ideas? Thank you for your help already.

Matt Pearcey

 

[JeremyNYC]

Matt,

A couple of issues here. The first one is that I'm guessing your table design could use some work. I would say it's probably inadvisable to have fields for each region. It sounds like you're asking for a lot of pain when the regional breakdown changes (and I can guarantee that it will--these things just happen). You might want to start a new thread where you ask people to advise you on that (and include a list of all your tables and fields).

As far as getting this code to "work"...I'm not sure what this code is trying to do. Are you tring to set the recordsource of a form? You don't have a line that does that. Also, in one branch of your if/then structure you set the variable to a sql statement and in the other you leave it as a person's name. Also, you're using a person's name, and I would say that's dangerous; either use the person's ID or use their rank, as discussed in my previous posting.

Is there other code that sets the form's recordsource to the result of this function? I would guess there would have to be.

What happens when you run this? And what's the other code like?

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.