5. Then go to Design View of frmInputSpecialityCodes, figure A, and 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...
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.
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 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 multivalue 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 selctions 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 wahy 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.
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)
3. Copy and paste the below Function in a Module window, save and close. Do not name your Module the same as the Function.
|| Floor Coverings
=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]