Unanswered: set up login/password to distrubute data among salesreps.
I need to set up login/passwords for salesreps so when they enter the db, they will only be able to see the records that are assigned to them by their sales manager.
Eg: db has a total of 500 records.
db has 250 records with Salesrep = Mike,
db has 150 records with Salesrep = Bob,
db has 100 records with Salesrep = Tim.
When Mike opens the db, he puts in his login/password, db opens and Mike can only see the 250 records that are assigned to him by his sales manager.
Second part of the question is this:
All records are: 1) Open 2) Pending or 3) Closed.
When Mike logs in, I need him to have an option to open any of the 3 choices from a selection.
I thought, if I can manage to have him access the db with his login/password this would open a subform with 3 buttons that have 1) Open Records, 2) Pending Records, and 3) Closed Records. Then when he clicks one of these buttons it opens ONLY the records that are assigned to him.
How do I achieve this? I'm stuck!! Please Help.
Your help is really appreciated since this seems different than setting up user groups.
Re: set up login/password to distrubute data among salesreps.
I managed to set up a form where the salesrep selects (his/her) name from a combo box, then clicks openform to open the contact and see only his/her records.
Now how can I add a password option to this, so a salesrep wouldn't simply select another salesrep's name from the combo box and enter their records?
Sorry this took so long but I am not an "Expert" and it just takes a while to set this stuff up from scratch, none the less this should function like u want. It would take me a while to document what you need to do to make this work like I have it so I think it best if you take a look at it and see if it is right before I tell you how to do this.
Username Password Control
Admin 1111 Full
Bob 2222 Can see only his stuff
Mike 4444 Can see only his stuff
Tim 3333 Can see only his stuff
when you close the forms press F11 to see the database window also by default it will give 2 or so error msgs if you try to go to someone (other than who you are logged in as) elses stuff. Just press halt then ok
Let me know if I need to make changes that you would like to see or if it is ok if you would like me to tell you how to do this.
I will be.... I set up 4 users listed in my last post but when I downloaded the zipped file they were all gone. As I had it they could only access their respective information when they logged in as themselves, and that was with different usernames and passwords...I have no idea why the users were removed when I zipped the file. I will work more on that. I know it is what you are wanting. As for wanting forms to load instead of queries....go into the forms properties for each user and in the on load/open event write a select query to only bring back the data you are looking for. Like I said I will try to figure out why the users I created were removed and get back to you.
The reason why the users were deleted was b/c the users are not linked to the database but rather the access program on that particular machine. I will write up a document on how to set this up if you want but unless the sales reps are using the same pc it won't do much good. There may be another way to achieve this I will do my best to find it.
I'm a little confused and upset that I did not get any replies or suggestions from senior and more experienced members regarding my post.
I just thought that was interesting/dissappointing and wanted to share my feelings....
here's the answer i found to my own question.
unfortunately i had to figure it out myself (even though it took me 3 days).
i wanted to share it so the next guy who's looking for an answer can reach it easier than i did.
Private Sub Command4_Click()
On Error GoTo Err_Command4_Click
Dim stDocName As String
Dim stLinkCriteria As String
Dim conDB As ADODB.Connection
Dim rsSalesReps As ADODB.Recordset
Dim strSalesPwd As String
Set conDB = New ADODB.Connection
conDB.Provider = "Microsoft.Jet.OLEDB.4.0"
conDB.Open "c:\7.mdb", "Admin", ""
Set rsSalesReps = New ADODB.Recordset
rsSalesReps.Open "select SalesRepPwd from SalesReps where SalesRepID=" & Me![Combo0], conDB
strSalesPwd = Trim(rsSalesReps!SalesRepPwd & "")
Set rsSalesReps = Nothing
Set conDB = Nothing
If strSalesPwd = Trim(Text2.Text & "") Then
stDocName = "Contacts"
stLinkCriteria = "[SalesRep]=" & Me![Combo0]
DoCmd.OpenForm stDocName, , , stLinkCriteria
MsgBox "Wrong Password"