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

DoCmd.OpenForm "frmSpecialityCodes"
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.
VBA
Tips (Main)
Home
Copy Fields Down from above Record
Loop thru records and OutPutTo seperate .RTF or .PDF
Modified Spell Check
Code Snippets
Lock\Unlock Bound Controls
Loop while renumbering two columns
Create a Table with Dynamic Field Names
Snippets for Exporting to Excel
Log Field Changes
Log Record Deletions
Check for Duplicate Values
ValidateData()
ClearClipboard()
Selecting an Excel Worksheet from Access
Send eMail to Multiple Recipients
Cancel Save in a Bound Form
Automatically Send eMail Notifications
fFindBookmark()
Looping Records to Send eMail
fxlFindReplace()
Public Function SpecialityCodes(ByVal SpecialityName As String) As String2006 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
Click to send feedback...
This site uses cookies to collect data on usage. By continuing to browse this site you consent to this policy. Find out more here.