Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2012
    Posts
    18

    Question Unanswered: Combo box and on the fly defaults

    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

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Is this what you're trying to do?

    Autofill
    Paul

  3. #3
    Join Date
    Jan 2012
    Posts
    18
    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?

  4. #4
    Join Date
    Jan 2012
    Posts
    18
    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.

  5. #5
    Join Date
    Jan 2012
    Posts
    18
    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?
    Last edited by Polkster58; 01-04-12 at 11:28.

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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?
    Paul

  7. #7
    Join Date
    Jan 2012
    Posts
    18
    Column Count = 1

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Eh, have you tried setting that to the actual number of columns (4 in post 5)?
    Paul

  9. #9
    Join Date
    Jan 2012
    Posts
    18
    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?

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Sure, that's controlled by the column widths property. A width of 0 for a column hides it.
    Paul

  11. #11
    Join Date
    Jan 2012
    Posts
    18
    That fixed it; Thank you!

  12. #12
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Happy to help!
    Paul

Posting Permissions

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