Solved Combo in Contacts

ClaraBarton

Registered User.
Local time
Today, 03:10
Joined
Oct 14, 2019
Messages
665
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?
 
You can concatenate child records using a concatenate function
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
 
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:-

 
Example.
Employee telephone numbers
tblEmployeePhoneNo tblEmployeePhoneNo

IDPhoneTypePhoneNumberEmpID_FK
1​
Home123-456-7890
1​
2​
Office123-123-12345
1​
3​
Personal Cell123-456-4321
1​
4​
Office123-123-9999
2​
5​
Fax123-345-7877
2​
6​
Office Cell678-897-9999
2​
Code:
SELECT employees.lastname,
       employees.firstname,
       Concatrelated("phonetype & ': ' & phonenumber", "tblemployeephoneno",
       "empid_fk =" & [employeeid], "phonetype", Chr(13) & Chr(10)) AS Phones
FROM   employees;

Query1 Query1

Last NameFirst NamePhones
DavolioNancyHome: 123-456-7890
Office: 123-123-12345
Personal Cell: 123-456-4321
FullerAndrewFax: 123-345-7877
Office: 123-123-9999
Office Cell: 678-897-9999
 
Well, here's the thing: I have this:
PhEmailIDContactsIDEMailBusinessHomeMobileFax
11(315) 651-3807
22jbauman88@gmail.com(260) 582-9573
33(585) 322-3665(585) 322-3665(843) 622-7609(416) 347-7704
45davisautomotive1@gmail.com(315) 825-1180(315) 825-1180(330) 429-3295
56(920) 639-5644
so how do I get them in Your format?
 
Query2 Query2

Last NameFirst NameBuisnessHomeMobileFaxPhoneNos
DavolioNancy123-456-7890223-456-7890456-678-8888111-222-3333Buisness: 123-456-7890
Home: 223-456-7890
Mobile: 456-678-8888
Fax: 111-222-3333
FullerAndrew323-456-7999444-456-7890Home: 323-456-7999
Mobile: 444-456-7890
LeverlingJanet456-789-9999234-567-9876Buisness: 456-789-9999
Fax: 234-567-9876
PeacockMargaret321-456-8765Home: 321-456-8765
BuchananSteven


Code:
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;

Code:
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
 
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.
 
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.
 
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?
 

Attachments

Last edited:

Users who are viewing this thread

Back
Top Bottom