Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Form Subform behavior (XP)

    Hello,

    I have a main form based on a table of radio station information that is working beautifully. Part of that form's information is a sales_contactID field. I've linked that contactID field to a subform (based on the sales_contact table using the same contactID field). The subform contains 4 fields: the contactID field, along with the salesperson's name, tele #, and email address. Right now each of these fields are text boxes, but I need to change the salesperson's name field to a combo box so that I can choose a name. I want the other two fields to automatically populate based on the salesperson's name.

    As I scroll through the records, everything works nicely. The contact info cycles along with the information on the main form.

    What I want to achieve and cannot do is this: When I need to create a new record (adding a new radio station) on the main form, I want to pick a salesperson's name from the subform's contact person list and have the other 2 contact fields to automatically populate.

    TIA

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

    Re: Form Subform behavior (XP)

    If I understand correctly, there is only a single sales contact for each record in the main form. If so, there is no need to use a subform.

    Change the sales_contactID text box on the main form to a combo box.
    Set its Row Source to a query that selects contactID, name, phone number and e-mail address from the sales_contact table, sorted by name.
    Set the Column Count property to 4, and the Column Widths property to 0",1",0",0".
    The combo box will display the name, but be bound to the sales_contactID field.

    To display phone number and e-mail address, you have two options:
    <UL><LI>Create two text boxes (on the main form, since there is no subform any more) with Control Source =[ComboBoxName].[Column](2) and =[ComboBoxName].[Column](3). The column index is zero-base, so Column(2) is the third column and Column(3) the fourth.
    <LI>Alternatively, you can create a query based on the main table and the sales_contact table, linked on sales_contactID vs contactID; make this into an outer join by double clicking the line between the tables. Select the option to return ALL records from the main table. Add all fields from the main table to the query design grid, plus the phone number and e-mail address field from sales_contact.

    Set the Record Source of the form to this query, and create text boxes on the main form bound to the phone number and e-mail address fields.[/list]This will take some work, but in the end, your form will be more efficient.

  3. #3
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Subform behavior (XP)

    I got the answer to my question and thought I would post it for learning purposes:

    So your combo box (calling it "Salesperson") has a rowsource which presents the following 4 columns of info:

    SalesmanID (assuming you are placing the id into the record) - bound column, not visible
    Salesman's name
    Tel #
    Email address

    On the after update event of the combo box the following sample code will work:

    Me!TelNo = Me!Salesperson.Column(2)
    Me!EmailAddr = Me!Salesperson.Column(3)

    When using the "Column" property the index starts at 0, so the 1st column is really index 0, 2nd column, index 1 and so on.

    Regards and thanks.

  4. #4
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Subform behavior (XP)

    Hans,

    I had no idea you had replied. Usually, I get an email stating that someone had replied to my post. I will incorporate your solution and not use a subform (which is much better!)

    Lots of Thanks!

Posting Permissions

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