Edited by HansV to break EXTREMELY long lines – please heed the warning about 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:
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
OK…so here are my problems
- 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).
- 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
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 & "'")
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.
