I have a form called "Word Order - Request". I have two tables called "Task Assignments" and "Requestor". The Requestor table has a list of user names with associated e-mail addresses, default phone number and extension number. In the Task Assignments table I have several fields including RequestedBy, RequestorsE-Mail, RequestorsPhone and RequestorsExtension. On the form I have a combo box for the RequestedBy field. The user can pick from the list or put in a different value not in the list. The form then prompts the user for e-mail address, phone number and extension. Once the user picks a person from the list, When the user gets to the RequestorsE-Mail field, I want to check and see if the field is null (using If IsNull). If it is, then I want to do a SQL select (or some equivalent command) and use what the user put into the RequestedBy field to lookup a corresponding record in the Requestor table (which may or may not be in the table) and match the Name in the Requestor file and return the e-mail address, the phone number and the extension that I have previously stored and associated with that name in the Requestor table and make them the default.
Would I use an event on the RequestedBy field to preload the RequestorsE-Mail, RequestorsPhone and RequestorsExtension fields or would it be better to use an event on each of these three fields (and if so which one)? Most importantly, how do I get the information from the Requestor table?
Okay, I went through and took out all of the spaces, dashes and underscores from the variable names and now have the following code:
Private Sub RequestedBy_AfterUpdate()
If IsNull(Me.RequestorsEmail) Then
Me.RequestorsEmail = Me.RequestedBy.Column(2)
If IsNull(Me.RequestorsPhone) Then
Me.RequestorsPhone = Me.RequestedBy.Column(3)
If IsNull(Me.RequestorsExtension) Then
Me.RequestorsExtension = Me.RequestedBy.Column(4)
It no longer gives me an error when it runs, but it also doesn't do anything that I can see. The fields are still blank when I click on them. The one thing I don't get is using the "Me.RequestedBy.Column(#)" variable. The RequestedBy is a combo box with just one column. It matches up with a table called Requestor which has 7 columns in it.
Update: I went into the Data tab of the RequestedBy field and changed the Row Select to be:
SELECT Requestor.DefaultRequestorName, Requestor.DefaultRequestorEmail, Requestor.DefaultRequestorPhone, Requestor.DefaultRequestorExtension FROM Requestor ORDER BY Requestor.LastName, Requestor.FirstName;
Reran the code and am still not getting anything to show up in the text boxes "RequestorsEmail, RequestorsPhone and RequestorsExtension.
Okay, I set the column count to 4 and decremented the three numbers in parenthesis by one and it works. However, it also shows the columns in the drop down list. Is there any way to display just the first column but still have access to the other three columns?