Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2004
    Posts
    64

    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.

    THANKS.

  2. #2
    Join Date
    Jan 2004
    Posts
    64

    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?

    Any help with this will be appreciated.

    Thanks.
    Last edited by bsarman; 01-14-04 at 14:55.

  3. #3
    Join Date
    Feb 2003
    Location
    Winston-Salem,NC
    Posts
    34

    ...

    Are the sales reps records stored in the same table or are there sperate ones for each of the users?

  4. #4
    Join Date
    Jan 2004
    Posts
    64
    Same table.

  5. #5
    Join Date
    Feb 2003
    Location
    Winston-Salem,NC
    Posts
    34

    .....

    not sure yet how to limit what they can see within a single table....would be much easier if the records were seperate but I think I can fin a way around that...let me work on it a bit.

  6. #6
    Join Date
    Jan 2004
    Posts
    64

    Red face

    Experts..Please offer solution. Really want to learn how to do this.

    Thanks...

  7. #7
    Join Date
    Feb 2003
    Location
    Winston-Salem,NC
    Posts
    34

    ....

    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.
    Note:

    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

    Note:
    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.
    Attached Files Attached Files

  8. #8
    Join Date
    Jan 2004
    Posts
    64
    thanks for your help. i'll give it a try now.

  9. #9
    Join Date
    Jan 2004
    Posts
    64
    thanks for trying but this isn't what i'm trying to achieve. there's no security with this. i need the salesrep to login with password. also what i want to open is forms not queries.

    thanks again.

  10. #10
    Join Date
    Feb 2003
    Location
    Winston-Salem,NC
    Posts
    34

    .....

    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.

  11. #11
    Join Date
    Jan 2004
    Posts
    64
    thanks, appreciate the help.

  12. #12
    Join Date
    Feb 2003
    Location
    Winston-Salem,NC
    Posts
    34

    .....

    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.

  13. #13
    Join Date
    Jan 2004
    Posts
    64
    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....

  14. #14
    Join Date
    Jan 2004
    Posts
    64

    Thumbs down

    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).
    thanks all!!
    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 & "")

    rsSalesReps.Close
    Set rsSalesReps = Nothing
    conDB.Close
    Set conDB = Nothing

    Text2.SetFocus
    If strSalesPwd = Trim(Text2.Text & "") Then

    stDocName = "Contacts"
    stLinkCriteria = "[SalesRep]=" & Me![Combo0]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    Else
    MsgBox "Wrong Password"
    End If


    Exit_Command4_Click:
    Exit Sub

    Err_Command4_Click:
    MsgBox Err.Description
    Resume Exit_Command4_Click

    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •