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?

Thx

Is this what you're trying to do?

Autofill

Okay I entered the following and it did not work. RequestedBy is the name of field assigned to the combo box.


Private Sub Requested_By_AfterUpdate()
If IsNull(Me.RequestorsE_mail) Then
Me.RequestorsE_mail = Me.RequestedBy.Column(4)
End If

If IsNull(Me.RequestorsPhone) Then
Me.RequestorsPhone = Me.RequestedBy.Column(5)
End If

If IsNull(Me.RequestorsExtension) Then
Me.RequestorsExtension = Me.RequestedBy.Column(6)
End If
End Sub


What I am doing wrong?

well, apparently I was missing an "_" in my name. The code should read:


Private Sub Requested_By_AfterUpdate()
If IsNull(Me.RequestorsE_mail) Then
Me.RequestorsE_mail = Me.Requested_By.Column(4)
End If

If IsNull(Me.RequestorsPhone) Then
Me.RequestorsPhone = Me.Requested_By.Column(5)
End If

If IsNull(Me.RequestorsExtension) Then
Me.RequestorsExtension = Me.Requested_By.Column(6)
End If
End Sub

However, when I do that I get another error message that says the following:

Run-time error '-2147352567 (800200009)':

The field 'Task Assignments.RequestorsE-mail' cannot contain a Null value because the Required property for this field is set to True. Enter a value in this field.

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)
End If

If IsNull(Me.RequestorsPhone) Then
Me.RequestorsPhone = Me.RequestedBy.Column(3)
End If

If IsNull(Me.RequestorsExtension) Then
Me.RequestorsExtension = Me.RequestedBy.Column(4)
End If
End Sub

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.

Anyone know what I am doing wrong?

Any other ideas?

For starters, the column property is 0 based, so the extension column would be 3, not 4 (and same for the others). What is the column count property of the combo?

Column Count = 1

Eh, have you tried setting that to the actual number of columns (4 in post 5)?

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?

Sure, that's controlled by the column widths property. A width of 0 for a column hides it.

That fixed it; Thank you!

Happy to help!