Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Putting Multiple Fiedls into a Combo Box

Discussion in 'Information Technology' started by Kate, Jul 28, 2006.

  1. Kate

    Kate
    Expand Collapse
    Guest

    Hi

    Apologies if this is answered somewhere but have trawled through and can
    only find users wanting to display all fields across.

    I have a form based on a large database. In the form header, I have a combo
    box which selects just one record - all of the rest of the data is then
    assigned and in relation to that record.

    I have 4 fields: Contact1, Contact2, Contact3, Contact4. There is a section
    in the form where sales reps can send 'hot leads' in and I want a combo box
    that has the four contacts as a drop down to select.

    e.g. In the form header, I select ABC Company - (all data is then in
    relation to this company -which is already set up). I then want to send a
    'hot lead' in but need to speciffy which contact from my main list to call
    about it - therefore I want a dropdown with the four names (already in the
    table) so I can chose which one needs a call.

    I hope this makes sense and I'm being clear!
    Thanks in advance
    Kate
     
  2. Loading...

    Similar Threads Forum Date
    SciTech Putting Sunshine In The Tank Breaking News Jul 9, 2011
    Pacific Anuradha Koirala: Putting a Stop to Trade in Women Breaking News May 24, 2011
    Giving Up Your Spiritual Journey (and Putting Down Roots) Spiritual Articles May 5, 2010
    Putting Your Faith in God Spiritual Articles Feb 13, 2006
    Putting the Fallouts of the Islamic Invasion and British Occupation of India in Perspective (News Fr Interfaith Dialogues Jun 2, 2005

  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    The biggest problem is that you've got what's referred to as a repeating
    group: Contact1, Contact2, Contact3 and Contact4.

    This really should be modelled as 4 separate rows in a second table, rather
    than 4 fields in a single row in the first table.

    If you're stuck, you can create a query that unions together the four
    fields, and use that query as the Row Source for the combo box:

    SELECT Contact1 FROM MyTable
    UNION
    SELECT Contact2 FROM MyTable
    UNION
    SELECT Contact3 FROM MyTable
    UNION
    SELECT Contact4 FROM MyTable

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Kate" <Kate@discussions.microsoft.com> wrote in message
    news:2CADBA1E-7D17-45A4-80CF-61DF7ABF927C@microsoft.com...
    > Hi
    >
    > Apologies if this is answered somewhere but have trawled through and can
    > only find users wanting to display all fields across.
    >
    > I have a form based on a large database. In the form header, I have a

    combo
    > box which selects just one record - all of the rest of the data is then
    > assigned and in relation to that record.
    >
    > I have 4 fields: Contact1, Contact2, Contact3, Contact4. There is a

    section
    > in the form where sales reps can send 'hot leads' in and I want a combo

    box
    > that has the four contacts as a drop down to select.
    >
    > e.g. In the form header, I select ABC Company - (all data is then in
    > relation to this company -which is already set up). I then want to send a
    > 'hot lead' in but need to speciffy which contact from my main list to call
    > about it - therefore I want a dropdown with the four names (already in the
    > table) so I can chose which one needs a call.
    >
    > I hope this makes sense and I'm being clear!
    > Thanks in advance
    > Kate
     
  4. Kate

    Kate
    Expand Collapse
    Guest

    Thanks very much - that worked a treat!

    However, I'm now trying to get a text field on the form to populate with the
    telephone number of the contact that is picked from the combo box. The
    fields are Contact1_number, etc.

    Sorry to ask another question - I thought I had this nailed but clearly not!

    "Douglas J Steele" wrote:

    > The biggest problem is that you've got what's referred to as a repeating
    > group: Contact1, Contact2, Contact3 and Contact4.
    >
    > This really should be modelled as 4 separate rows in a second table, rather
    > than 4 fields in a single row in the first table.
    >
    > If you're stuck, you can create a query that unions together the four
    > fields, and use that query as the Row Source for the combo box:
    >
    > SELECT Contact1 FROM MyTable
    > UNION
    > SELECT Contact2 FROM MyTable
    > UNION
    > SELECT Contact3 FROM MyTable
    > UNION
    > SELECT Contact4 FROM MyTable
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Kate" <Kate@discussions.microsoft.com> wrote in message
    > news:2CADBA1E-7D17-45A4-80CF-61DF7ABF927C@microsoft.com...
    > > Hi
    > >
    > > Apologies if this is answered somewhere but have trawled through and can
    > > only find users wanting to display all fields across.
    > >
    > > I have a form based on a large database. In the form header, I have a

    > combo
    > > box which selects just one record - all of the rest of the data is then
    > > assigned and in relation to that record.
    > >
    > > I have 4 fields: Contact1, Contact2, Contact3, Contact4. There is a

    > section
    > > in the form where sales reps can send 'hot leads' in and I want a combo

    > box
    > > that has the four contacts as a drop down to select.
    > >
    > > e.g. In the form header, I select ABC Company - (all data is then in
    > > relation to this company -which is already set up). I then want to send a
    > > 'hot lead' in but need to speciffy which contact from my main list to call
    > > about it - therefore I want a dropdown with the four names (already in the
    > > table) so I can chose which one needs a call.
    > >
    > > I hope this makes sense and I'm being clear!
    > > Thanks in advance
    > > Kate

    >
    >
    >
     
  5. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Change the query so that it returns not only the contact name but the
    contact phone number as well. Only you can answer whether this is:

    SELECT Contact1, Phone1 FROM MyTable
    UNION
    SELECT Contact2, Phone2 FROM MyTable
    UNION
    SELECT Contact3, Phone3 FROM MyTable
    UNION
    SELECT Contact4, Phone4 FROM MyTable

    or

    SELECT MyTable.Contact1, MyOtherTable.Phone
    FROM MyTable INNER JOIN MyOtherTabe
    ON MyTable.Contact1 = MyOtherTable.Contact
    UNION
    SELECT MyTable.Contact2, MyOtherTable.Phone
    FROM MyTable INNER JOIN MyOtherTabe
    ON MyTable.Contact2 = MyOtherTable.Contact
    UNION
    SELECT MyTable.Contact3, MyOtherTable.Phone
    FROM MyTable INNER JOIN MyOtherTabe
    ON MyTable.Contact3 = MyOtherTable.Contact
    UNION
    SELECT MyTable.Contact4, MyOtherTable.Phone
    FROM MyTable INNER JOIN MyOtherTabe
    ON MyTable.Contact4 = MyOtherTable.Contact

    Now that your combo box contains the phone number, put code in the
    AfterUpdate event of the combo box to populate the list box:

    Private Sub MyComboBox_AfterUpdate()

    Me.MyTextBox = Me.MyComboBox.Column(1)

    End Sub

    Note that the Column collection starts numbering at 0.

    Another alternative would be simply to put logic in the combo's AfterUpdate
    to look up the phone number if it's not possible to create a union query as
    I've outlined above.


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Kate" <Kate@discussions.microsoft.com> wrote in message
    news:A3865CCD-449C-49C7-A73A-91D97D8E8274@microsoft.com...
    > Thanks very much - that worked a treat!
    >
    > However, I'm now trying to get a text field on the form to populate with

    the
    > telephone number of the contact that is picked from the combo box. The
    > fields are Contact1_number, etc.
    >
    > Sorry to ask another question - I thought I had this nailed but clearly

    not!
    >
    > "Douglas J Steele" wrote:
    >
    > > The biggest problem is that you've got what's referred to as a repeating
    > > group: Contact1, Contact2, Contact3 and Contact4.
    > >
    > > This really should be modelled as 4 separate rows in a second table,

    rather
    > > than 4 fields in a single row in the first table.
    > >
    > > If you're stuck, you can create a query that unions together the four
    > > fields, and use that query as the Row Source for the combo box:
    > >
    > > SELECT Contact1 FROM MyTable
    > > UNION
    > > SELECT Contact2 FROM MyTable
    > > UNION
    > > SELECT Contact3 FROM MyTable
    > > UNION
    > > SELECT Contact4 FROM MyTable
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > > "Kate" <Kate@discussions.microsoft.com> wrote in message
    > > news:2CADBA1E-7D17-45A4-80CF-61DF7ABF927C@microsoft.com...
    > > > Hi
    > > >
    > > > Apologies if this is answered somewhere but have trawled through and

    can
    > > > only find users wanting to display all fields across.
    > > >
    > > > I have a form based on a large database. In the form header, I have a

    > > combo
    > > > box which selects just one record - all of the rest of the data is

    then
    > > > assigned and in relation to that record.
    > > >
    > > > I have 4 fields: Contact1, Contact2, Contact3, Contact4. There is a

    > > section
    > > > in the form where sales reps can send 'hot leads' in and I want a

    combo
    > > box
    > > > that has the four contacts as a drop down to select.
    > > >
    > > > e.g. In the form header, I select ABC Company - (all data is then in
    > > > relation to this company -which is already set up). I then want to

    send a
    > > > 'hot lead' in but need to speciffy which contact from my main list to

    call
    > > > about it - therefore I want a dropdown with the four names (already in

    the
    > > > table) so I can chose which one needs a call.
    > > >
    > > > I hope this makes sense and I'm being clear!
    > > > Thanks in advance
    > > > Kate

    > >
    > >
    > >
     
  6. Kate

    Kate
    Expand Collapse
    Guest

    Thanks so much - the first union query seemed to do just the trick!!

    Kate

    "Douglas J Steele" wrote:

    > Change the query so that it returns not only the contact name but the
    > contact phone number as well. Only you can answer whether this is:
    >
    > SELECT Contact1, Phone1 FROM MyTable
    > UNION
    > SELECT Contact2, Phone2 FROM MyTable
    > UNION
    > SELECT Contact3, Phone3 FROM MyTable
    > UNION
    > SELECT Contact4, Phone4 FROM MyTable
    >
    > or
    >
    > SELECT MyTable.Contact1, MyOtherTable.Phone
    > FROM MyTable INNER JOIN MyOtherTabe
    > ON MyTable.Contact1 = MyOtherTable.Contact
    > UNION
    > SELECT MyTable.Contact2, MyOtherTable.Phone
    > FROM MyTable INNER JOIN MyOtherTabe
    > ON MyTable.Contact2 = MyOtherTable.Contact
    > UNION
    > SELECT MyTable.Contact3, MyOtherTable.Phone
    > FROM MyTable INNER JOIN MyOtherTabe
    > ON MyTable.Contact3 = MyOtherTable.Contact
    > UNION
    > SELECT MyTable.Contact4, MyOtherTable.Phone
    > FROM MyTable INNER JOIN MyOtherTabe
    > ON MyTable.Contact4 = MyOtherTable.Contact
    >
    > Now that your combo box contains the phone number, put code in the
    > AfterUpdate event of the combo box to populate the list box:
    >
    > Private Sub MyComboBox_AfterUpdate()
    >
    > Me.MyTextBox = Me.MyComboBox.Column(1)
    >
    > End Sub
    >
    > Note that the Column collection starts numbering at 0.
    >
    > Another alternative would be simply to put logic in the combo's AfterUpdate
    > to look up the phone number if it's not possible to create a union query as
    > I've outlined above.
    >
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Kate" <Kate@discussions.microsoft.com> wrote in message
    > news:A3865CCD-449C-49C7-A73A-91D97D8E8274@microsoft.com...
    > > Thanks very much - that worked a treat!
    > >
    > > However, I'm now trying to get a text field on the form to populate with

    > the
    > > telephone number of the contact that is picked from the combo box. The
    > > fields are Contact1_number, etc.
    > >
    > > Sorry to ask another question - I thought I had this nailed but clearly

    > not!
    > >
    > > "Douglas J Steele" wrote:
    > >
    > > > The biggest problem is that you've got what's referred to as a repeating
    > > > group: Contact1, Contact2, Contact3 and Contact4.
    > > >
    > > > This really should be modelled as 4 separate rows in a second table,

    > rather
    > > > than 4 fields in a single row in the first table.
    > > >
    > > > If you're stuck, you can create a query that unions together the four
    > > > fields, and use that query as the Row Source for the combo box:
    > > >
    > > > SELECT Contact1 FROM MyTable
    > > > UNION
    > > > SELECT Contact2 FROM MyTable
    > > > UNION
    > > > SELECT Contact3 FROM MyTable
    > > > UNION
    > > > SELECT Contact4 FROM MyTable
    > > >
    > > > --
    > > > Doug Steele, Microsoft Access MVP
    > > > http://I.Am/DougSteele
    > > > (no e-mails, please!)
    > > >
    > > >
    > > > "Kate" <Kate@discussions.microsoft.com> wrote in message
    > > > news:2CADBA1E-7D17-45A4-80CF-61DF7ABF927C@microsoft.com...
    > > > > Hi
    > > > >
    > > > > Apologies if this is answered somewhere but have trawled through and

    > can
    > > > > only find users wanting to display all fields across.
    > > > >
    > > > > I have a form based on a large database. In the form header, I have a
    > > > combo
    > > > > box which selects just one record - all of the rest of the data is

    > then
    > > > > assigned and in relation to that record.
    > > > >
    > > > > I have 4 fields: Contact1, Contact2, Contact3, Contact4. There is a
    > > > section
    > > > > in the form where sales reps can send 'hot leads' in and I want a

    > combo
    > > > box
    > > > > that has the four contacts as a drop down to select.
    > > > >
    > > > > e.g. In the form header, I select ABC Company - (all data is then in
    > > > > relation to this company -which is already set up). I then want to

    > send a
    > > > > 'hot lead' in but need to speciffy which contact from my main list to

    > call
    > > > > about it - therefore I want a dropdown with the four names (already in

    > the
    > > > > table) so I can chose which one needs a call.
    > > > >
    > > > > I hope this makes sense and I'm being clear!
    > > > > Thanks in advance
    > > > > Kate
    > > >
    > > >
    > > >

    >
    >
    >
     

Share This Page