Results 1 to 5 of 5

Thread: NotInList (2K)

  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    NotInList (2K)

    Edited by HansV to break EXTREMELY long lines - please heed the warning about <!t>[pre] tags!

    Good Morning Everyone!

    Here's my situation:

    I have a form that updates User's information. The recordsource is tbl_User. tbl_User has the following fields:
    pkUserID--Primary Key
    UserName--Name of User
    UserID--User's Login ID
    fkDeptID--Foreign Key to tbl_Dept--ComboBox for the department the user is in
    fkUserRightsID--Foreign Key to tbl_AccessRights --ComboBox for the access permission the user has
    Active--yes/no--is this user active or not

    If a user has admin rights, they have access to frm_UpdateUserInformation.

    The purpose of this form is to ADD, MODIFY, or UPDATE user information. On the form I have a combo box which list's the User's by Name. If the ADMIN person is adding a NEW USER, I have the following code in the NotInList:
    <pre>Dim db As Database
    Dim sName As String
    Dim rs As Object

    Set db = CurrentDb

    'Ask the user if they want to add to the list
    If MsgBox("Are you sure you want to add this person?", vbYesNo + vbQuestion, _
    "Add new value?") = vbYes Then

    'The user clicked Yes - add the new value
    db.Execute "INSERT INTO tbl_User(UserName) VALUES (""" & NewData & """)", dbFailOnError

    'Tell Access you've added the new value
    Response = acDataErrAdded

    Else

    'The user clicked No - discard the new value
    Me.cboAddUser.Undo
    'Tell Access you've discarded the new value
    Response = acDataErrContinue

    End If

    db.Close
    Set db = Nothing
    Me.cboAddUser.Requery
    </pre>


    OK...so here are my problems
    1. <LI>The code to add the user works just fine. However, as the NEW USER is added, the ADMIN person won't know what the NEW USER'S LOGINID will be, and shouldn't have to ask. So I'm using the Global Address List and more code to locate the user and UPDATE the LOGINID field with the NEW USER'S ACCOUNT (which is their LoginID).
      <LI>I want the msg to the ADMIN person to include the NEW USER'S name they just typed in. For instance, in the combo box, the ADMIN person typed "Roberta Price", I want the message to read: "Are you sure you want to add ROBERTA PRICE?"

    The following code is what I'm using to locate the NEW USER'S loginID
    <pre> Public Sub UpdateLoginID()
    Dim FName, LName, UserName, sLoginID As String

    UserName = Me.cboAddUser.Column(1)
    FName = Left([UserName], InStr(1, [UserName], " ") - 1)
    LName = IIf(InStr(InStr([UserName], " ") + 1, [UserName], " ") <> 0, Right([UserName], _
    Len([UserName]) - InStr(InStr([UserName], " ") + 1, "[UserName]", " ")), _
    Right([UserName], Len([UserName]) - InStr([UserName], " ")))

    'Locate the users login id based on first and last name
    Me.UserID = DLookup("[Account]", "[Global Address List]", "[First] = '" & _
    FName & "' and [Last] = '" & LName & "'")</pre>


    However, I keep running into different problems. 1) Since the user hasn't been added to the list until the cbo has been exited, UpdateLoginID() won't fire. If I add code to the AfterUpdate event of the cbo to hold the name, the NotInList works, but not the UpdateLoginID(). The system keeps telling me to SAVE, I do, and still nothing. I've tried requerying the cbo and still nothing.

    Can someone please provide me insight as to how to achieve what I'm attempting to do?

    Thanks in advance.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: NotInList (2K)

    Good Afternoon!

    About the text of the message box: you can use NewData, just like you do in the SQL statement:

    If MsgBox("Are you sure you want to add " & NewData & "?", vbYesNo + vbQuestion, ...

    About the login ID: you could set it in the Before Update event of the form (if it is still empty). Or you could set it in the SQL statement that you execute in the On Not In List event.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NotInList (2K)

    Thanks Hans,
    adding NEWDATA worked just fine, but adding the code to the BeforeUpdate didn't. Msgbox keeps asking me to add the new user but never adds the LoginID.

    I don't know how to add the " Call UpdateLoginID()" to the SQL statement. I would greatly appreciate it if you could lend your advice.

    Thanks again.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: NotInList (2K)

    See if this code for the On Not In List event works:
    <code>
    Dim db As Database
    Dim FName As String
    Dim LName As String
    Dim UserName As String
    Dim sLoginID As String
    Dim strSQL As String
    Dim intPos As Integer

    ' Ask the user if they want to add to the list
    If MsgBox("Are you sure you want to add this person?", _
    vbYesNo + vbQuestion, "Add new value?") = vbYes Then

    ' The user clicked Yes - add the new value

    ' Extract first and last name
    intPos = InStr(NewData, " ")
    FName = Left(NewData, intPos - 1)
    intPos = InStrRev(NewData, " ")
    LName = Mid(NewData, intPos + 1)

    ' Locate the users login id based on first and last name
    sLoginID = DLookup("<!t>[Account]<!/t>", "<!t>[Global Address List]<!/t>", _
    "<!t>[First]<!/t> = '" & FName & "' and <!t>[Last]<!/t> = '" & LName & "'")

    strSQL = "INSERT INTO tbl_User(UserName, UserID) VALUES ('" & _
    NewData & "','" & sLoginID & "')"
    Set db = CurrentDb
    db.Execute strSQL, dbFailOnError
    Set db = Nothing

    ' Tell Access you've added the new value
    Response = acDataErrAdded

    Else

    ' The user clicked No - discard the new value
    Me.cboAddUser.Undo
    ' Tell Access you've discarded the new value
    Response = acDataErrContinue

    End If</code>

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NotInList (2K)

    WONDERFUL!!!!!! WONDERFUL!!!!!

    Thank you so very very much!!!!
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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