Putting Multiple Fiedls into a Combo Box

[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

 

[Douglas J Steele]

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

 

[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]

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.

 

[Guest]

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

Kate