|
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). (Make sure you either use the prefix ‘sc’ or use something other then ‘Name’ for your field name. Access does not like it when you use a Reserved Word as a field name.) 2. Fill you table with data, example: A. Furniture B. Fabrics C. Floor Coverings ect... 3. Copy and paste the below Function in a Module window, save and close. Do not name your Module the same as the Function. 4. Create a form housing tblSpecialityCodes and name it frmInputSpecialityCodes 5. Open the menu you want to enter these values, mine if frmVendorProfile 6. At the top of the Vendor Profile place a text box and copy/paste the below on the Control Source of the text box:
=IIf(Not IsNull([txtSpecialityCodeID]),"This company specializes in" & Chr(13) & Chr(10) & SpecialityCodes([vpSpecialityCodeID]),"")
7. In Double-Click Event Procedure of the field you want to store these values place (Mine is txtSpecialityCodes):
DoCmd.OpenForm "frmInputSpecialityCodes"
8. Then go design view of frmInputSpecialityCodes and and place the below in the Double-Click of the Primary Key field:
If IsLoaded("frmVendorProfile") Then Form_sfrVendorMain.txtSpecialityCodeID = Form_sfrVendorMain.txtSpecialityCodeID & Me![txtSpecialityCodeID] End If
9. Now go the menu where you have your field and double-click. Fill in as many values as you like. You will see the message at the top. |
|
Regina Whipp |
|
The difficult I do immediately, the impossible takes a little bit longer. |
How to use the alphabet to create a multi-value 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) SpecialityID = 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 |
|
Modules |
