Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Posts
    38

    Post Unanswered: Allow certain users to make change

    My database is currently in Access 2002.
    How can I allow only certain users to change a student to a particular counselor. I'm thinking a simple if statement should do it but I don't really know to much of code so any help would be helpful.

    Currently, we have twenty users and at this time anyone can assign a student to any counselor by simply changing the name of couselor on the student's record. Which is generally ok and we want to continue to do this with the exception of one counselor.

    I want to allow only User A and B to assign a student's record to counselor Z.

    The IF statements I have come out with don't make any sense.
    I'd appreciate any help.
    Thanks

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    If CurrentUser()="User A" OR CurrentUser()="User B" Then CounselorNameTxt.Enabled=True
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah - sorry Mike - Your last post did inspire this idea

    If counsellor is a drop down (for example):
    Code:
    Private Sub Form_Load()
    Dim sSQL As String
     
    sSQL = "SELECT CounsellorName from CounsellorTable"
    If CurrentUser()<> "User A" AND CurrentUser()<>"User B" Then 
    sSQL = sSQL & " WHERE CounsellorName  <> 'z'" 
    End If
     
    Me.cboCounselorName.RecordSource = End Sub
    You'll need to enforce limit to list too...
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Oct 2003
    Posts
    38
    It is a combo box and I'm getting a syntax error for the last line.


    Code:
    Private Sub Form_Load()
    Dim sSQL As String
     
    sSQL = "SELECT CounsellorName from CounsellorTable"
    If CurrentUser()<> "User A" AND CurrentUser()<>"User B" Then 
    sSQL = sSQL & " WHERE CounsellorName  <> 'z'" 
    End If
     
    Me.cboCounselorName.RecordSource = End Sub
    Here is what I did. I copied the above code and substitued some areas where I thought my fields should go. This is what it looks like.
    I would also like to include a message box so the user know why they can't make that change.

    Code:
    Private Sub Form_Load()
    Dim sSQL As String
     
    sSQL = "SELECT Counselor Last Name from Counselors"
    If CurrentUser()<> "dpeters" AND CurrentUser()<>"opatterson" Then 
    sSQL = sSQL & " WHERE CounsellorName  <> 'tgarza'" 
    End If
     
    Me.cboCounselorName.RecordSource = End Sub

  5. #5
    Join Date
    Nov 2004
    Location
    Bangor, ME USA
    Posts
    44
    By what I read try enclosing the field name in [ ]. VBA and SQL do not like spaces in field names.

    Take your code:
    sSQL = "SELECT Counselor Last Name from Counselors"
    If CurrentUser()<> "dpeters" AND CurrentUser()<>"opatterson" Then
    sSQL = sSQL & " WHERE CounsellorName <> 'tgarza'"
    End If
    and try:
    sSQL = "SELECT [Counselor Last Name] from Counselors"
    If CurrentUser()<> "dpeters" AND CurrentUser()<>"opatterson" Then
    sSQL = sSQL & " WHERE CounsellorName <> 'tgarza'"
    End If

    HTH
    Kevin M Carman

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oops - Did I really put RecordSource?
    Code:
    Private Sub Form_Load() Dim sSQL As String sSQL = "SELECT Counselor [Last Name] from Counselors" If CurrentUser()<> "dpeters" AND CurrentUser()<>"opatterson" Then sSQL = sSQL & " WHERE CounsellorName <> 'tgarza'" End If Me.cboCounselorName.RowSource = End Sub
    And as Kevin says - it's bad mojo to have spaces in any object names.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by pootle flump
    Ah - sorry Mike - Your last post did inspire this idea

    If counsellor is a drop down (for example):
    Code:
    Private Sub Form_Load()
    Dim sSQL As String
     
    sSQL = "SELECT CounsellorName from CounsellorTable"
    If CurrentUser()<> "User A" AND CurrentUser()<>"User B" Then 
    sSQL = sSQL & " WHERE CounsellorName  <> 'z'" 
    End If
     
    Me.cboCounselorName.RecordSource = End Sub
    You'll need to enforce limit to list too...
    Dan ... It gets back to our usual gripe about not having enough info ... I try not to make any assumptions. My read of this was that there was a textbox with the counselor name in it ... Which I WOULD NEVER DO ...
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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