Access Diva Multi-Value field

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

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

DoCmd.OpenForm "frmSpecialityCodes"

Now, when you go to Vendor Profile and double-click in the Specialty Code(s) field to pull up the form Specialty Codes double clicking in that ID field transfers it to Specialty 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.

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 Specialty Codes, should be 26 characters long, if in the US (or however many characters are in your alphabet).

If you want the full name of the values to show at the top of the Form as in figure 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...

Creating a Multi-Value field using the Alphabet...

Before there were multivalued fields in Access (and I still don't use them but that's another story) I needed a way for Users to able to make multiple selections quickly, store those selections and then have them show up in reports. While in this example I used TEXT as my Primary Key the code is easily adaptable to NUMERIC making it go way beyond the limit of the alphabet. So, let's get started...

1. Create a table name it tlkpSpecialityCodes. 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. For a complete list of Reserved Words click here.)

2. Fill your table with some sample data, i.e.

Don't forget to create a form for entering your codes into tlkpSpecialityCodes (see figure A)

When I want my Users to know that a Double-Click event will produce another Form I make the background color of the control green.

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

VBA

Tips (Main)

Home

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

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

scSpecialityCodeID	scName
A	Furniture
B	Fabrics
C	Floor Coverings
etc...

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

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