The difficult I do immediately, the impossible takes a little bit longer.

1. Create a table name it tblSpecialityCodes. You will need two fields scSpecialityCodeID (Primary Key, Text; Field Length = 1) and scName (Non-Indexed, Text, Field Length = Up to 255). (If you decide to use *Name* as a field name make sure you either use the prefix ‘sc’ or something as Access does not like it when you use a Reserved Word as a field name...  Complete list of Reserved Words.)

2. Fill your table with data, example:
A. Furniture
B. Fabrics
C. Floor Coverings
etc
...

3. Copy and paste the below Function in a Module window, save and close. Do not name your Module the same as the Function.

5. Then go to Design View of frmInputSpecialityCodes and and place the below code in the Double_Click event procedure of the txtSpecialityCodeID (labeled ID in screen shot A) field:

Public Function SpecialityCodes(ByVal SpecialityName As String) As String
‘2006 Regina Whipp, modified from my original code 2003

Dim SpecialityID As String
Dim N As Long

For N = 1 To Len(SpecialityName)
SpecialtyID = DLookup("scName", "tblSpecialityCodes", "scSpecialityCodeID = '" & Mid(SpecialityName, N, 1) & "'")
If Not IsNull(SpecialityID) Then
     If SpecialityCodes <> "" Then SpecialityCodes = SpecialityCodes & ", "
          SpecialityCodes = SpecialityCodes & Trim(SpecialityID)
     End If
Next N
End Function

=IIf(Not IsNull([txtSpecialityCodeID]),"This company specializes in" & Chr(13) & Chr(10) & SpecialityCodes([vpSpecialityCodeID]),"")

To make it easy for your Users to enter the codes (and select the correct code)... In Double_Click Event Procedure of the field you want to store these values place (Mine is * Speciality Code(s) *, see C):

Now, when you go to Vendor Profile and double-click in the *Speciality Code(s)* field to pull up the form *Speciality Codes* double clicking in that *ID* field transfers it to *Speciality Code(s)* field on the Vendor Profile.  And, if you placed the code in the unbound text box as indicated in previous section, then the name at the top will also change.

If IsLoaded("frmVendorProfile") Then
     Form_sfrVendorMain.txtSpecialityCodeID = Form_sfrVendorMain.txtSpecialityCodeID & Me![txtSpecialityCodeID]
End If

4. Open the Form, in Design View, you want to enter these values, mine if frmVendorProfile.  Note: The TEXT field in your table (mine is tblVendorProfile), that is going to store your Speciality Codes, should be 26 characters long, if in the US (or however many characters are in your alphabet).

If you want the *Name* of the values to show at the top of the form (see B)... At the top of the Vendor Profile place a text box and copy/paste the below on the Control Source of the Text Box...

VBA
Creating a Multi-Value field using the Alphabet...
For this routine I am going to create one for Speciality Codes...
Don't forget to create a form for entering your codes into tblSpecialityCodes (see A)
DoCmd.OpenForm "frmSpecialityCodes"
When I want my Users to know that a Double-Click in the adjacent field will produce another Form I make the font colour of the Label green.

Cool Tip

aaaaaaaaaaaaiii