Solved Combo in Contacts (1 Viewer)

[ClaraBarton]

I would like to combine phones into one combo box. I have business, mobile, home, fax, email fields. They take up a lot of room on a datasheet and many of them are empty. I thought I could combine them into one combo box but it's not coming out like I visualized. I though a union query would make them into one column. Not working out so well. I separated them out to their own table with a foreign key to their contact. I would need ContactID, Discription (phone, mobile, etc) and number or email address. I've read up on combos till I'm tired. Is this pie in the sky?

 

[MajP]

You can concatenate child records using a concatenate function

Microsoft Access tips: Concatenate values from related records

Return a string containing the values of related records.

allenbrowne.com

But I do not understand showing this in a combobox. I could understand a single textbox with the data like

Mobile: 122.345.6789
Home: 124.456.8999

 

[Uncle Gizmo]

I've read up on combos till I'm tired. Is this pie in the sky?

No, it's exactly the right way to think about it, and do it it.

I've covered your scenario in great detail in my blog on my Nifty Access website here:-

Excel in Access (Parts 1, 2 and 3 as Video Instructions) - Nifty Access

Excel in Access (Parts 1, 2 and 3 as Video Instructions) Excel in Access (Part 1) Excel in Access (Part 2) Excel in Access (Part 3) MS Access Your Data With Sub-Forms How to use the Normalization Tool Convert an Excel "Contacts" Spreadsheet to MS Access Convert an Excel "Contacts" Spreadsheet to...

www.niftyaccess.com

 

[MajP]

Example.
Employee telephone numbers
tblEmployeePhoneNo tblEmployeePhoneNo

ID	PhoneType	PhoneNumber	EmpID_FK
1​	Home	123-456-7890	1​
2​	Office	123-123-12345	1​
3​	Personal Cell	123-456-4321	1​
4​	Office	123-123-9999	2​
5​	Fax	123-345-7877	2​
6​	Office Cell	678-897-9999	2​

SELECT employees.lastname,
       employees.firstname,
       Concatrelated("phonetype & ': ' & phonenumber", "tblemployeephoneno",
       "empid_fk =" & [employeeid], "phonetype", Chr(13) & Chr(10)) AS Phones
FROM   employees;

Query1 Query1

Last Name	First Name	Phones
Davolio	Nancy	Home: 123-456-7890 Office: 123-123-12345 Personal Cell: 123-456-4321
Fuller	Andrew	Fax: 123-345-7877 Office: 123-123-9999 Office Cell: 678-897-9999

 

[ClaraBarton]

Well, here's the thing: I have this:

PhEmailID	ContactsID	EMail	Business	Home	Mobile	Fax
1	1		(315) 651-3807
2	2	jbauman88@gmail.com		(260) 582-9573
3	3	(585) 322-3665	(585) 322-3665	(843) 622-7609	(416) 347-7704
4	5	davisautomotive1@gmail.com	(315) 825-1180	(315) 825-1180	(330) 429-3295
5	6		(920) 639-5644

so how do I get them in Your format?

 

[MajP]

Query2 Query2

Last Name	First Name	Buisness	Home	Mobile	Fax	PhoneNos
Davolio	Nancy	123-456-7890	223-456-7890	456-678-8888	111-222-3333	Buisness: 123-456-7890 Home: 223-456-7890 Mobile: 456-678-8888 Fax: 111-222-3333
Fuller	Andrew		323-456-7999	444-456-7890		Home: 323-456-7999 Mobile: 444-456-7890
Leverling	Janet	456-789-9999			234-567-9876	Buisness: 456-789-9999 Fax: 234-567-9876
Peacock	Margaret		321-456-8765			Home: 321-456-8765
Buchanan	Steven

SELECT employees.lastname,
       employees.firstname,
       employees.buisness,
       employees.home,
       employees.mobile,
       employees.fax,
       Concatphones("buisness, home, mobile, fax", [buisness], [home], [mobile],
       [fax])
       AS PhoneNos
FROM   employees;

Public Function ConcatPhones(Captions As String, ParamArray PhoneNos() As Variant) As String
  Dim aCaptions() As String
  Dim i As Integer
  aCaptions = Split(Captions, ",")
  For i = 0 To UBound(aCaptions)
    If Not IsNull(PhoneNos(i)) Then
      If ConcatPhones = "" Then
        ConcatPhones = Trim(aCaptions(i)) & ": " & PhoneNos(i)
      Else
        ConcatPhones = ConcatPhones & vbCrLf & Trim(aCaptions(i)) & ": " & PhoneNos(i)
      End If
    End If
  Next i

End Function

 

[ClaraBarton]

Wow! You shine! I am so on this. Thank you.

 

[MajP]

Both of those functions are pretty universal. In the first you can concatenate any child records. The latter you can concatenate any columns. So you can use these for more than just this case.

 

[ClaraBarton]

Oh wait. That won't work! You'd have to run it every time a number was changed or added. That's why I sort of wanted a combo.

 

[MajP]

It is in a query. There is nothing to run. It happens automatically. See demo.
Any number you add appears in the concatenated list. Try adding, deleting, editing. What do you mean by display in a combo? I still do not understand what that would look like. Maybe you mean a listbox?

 

[ClaraBarton]

Oh! I get it! Thank you. thank you. thank you. I'm using it.

 

[ClaraBarton]

This is no small thing you just did for me. I love it.