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.)
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)
=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
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).
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)
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.