VBA modify Multivalued Field

Private Sub Criteria_Tracker_ID_Exit(Cancel As Integer)

Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim childRS As Recordset
Dim var As Variant
Dim CriteriaName As String
Dim strRange1 As String
Dim strRange2 As String

CriteriaName = ""

Set db = CurrentDb
Set rst1 = db.OpenRecordset("SF Data and Technology Priorities")
strRange1 = "SELECT [Criteria Tracker ID] FROM [SF Data and Technology Priorities] WHERE [ID] = " & Me.[ID].Value
' "Me" identify current selected record on the form.
Set rs1 = db.OpenRecordset(strRange1)

' Multivalued field is actually a recordset. Here "Criteria Tracker ID" is multivalued field
Set childRS = rs1![Criteria Tracker ID].Value

If childRS.RecordCount = 0 Then
    Me.[Criteria Name].Value = ""
    Me.[Criteria Name].SetFocus
    Exit Sub
End If

childRS.MoveFirst

' Loop through the records in the child recordset.
Do Until childRS.EOF
   strRange2 = "SELECT [Sector] FROM [SF Criteria Tracker] WHERE [ID] = " & childRS.Fields(0).Value
   Set rs2 = db.OpenRecordset(strRange2)
   CriteriaName = CriteriaName & rs2![Sector].Value & "; "
   rs2.Close
   childRS.MoveNext
Loop

rs1.Close
Me.[Criteria Name].Value = CriteriaName
Me.[Criteria Name].SetFocus
' [Criteria Name] can be written as Criteria_Name, we can use _ to replace space in the field name
End Sub

Form VBA Sort or Order by 2 or more fields

Me.OrderBy = Me.cboSort1 & " DESC, " & Me.cboSort2 & " ASC"
Me.OrderByOn = True

Add VBA code to a control (button, combobox etc.) on from

1. Go to design or layout view of the form
2. Right click the Control and select "Properties" ==> "Event" tab
3. Go to "On click", select "...", on popup box select "Code Builder" (Alternatively, select "[Event Procedure]", then click "...")
4.Change Control name if necessary by going to "Other" tab ==> "Name"

Click a button on a form to export a table

1. Go to design or layout view of the form
2. Right click the command button and select "Properties" ==> "Event" tab
3. Go to "On click", select "...", on popup box select "Macro Builder"
4. Under "Action Catalog" on the right (click "Action Catalog" under "Design" tab on the top if it is shown", select "Data Import/Export"==> "ExportWithFormatting"

Create Dropdown list in Form (only one value can be selected)

1. Go to Design or Layout view of the form
2. Right click the field, select "Properties"
3. Go to "Data" tab
4. Change "Row Source" Type to "Value List"
5. Enter list of choices under "Row Source", e.g. "Yes";"No", or click "..." to enter

You may want to add below for data validation to prevent accidentally direct data inputs to the linked table (not necessary)
1. Go to the Design view of the table linked to the form.
2. Select Field ==> go to "General" tab at the bottom.
3. Go to "Validation Rule" and enter list of choices, e.g. "Yes";"No"

Create dropdown list with multiple selection check boxes in form

1. Go to the Design view of the table linked to the form.
2. Select Field ==> go to "Lookup" tab at the bottom.
3. Change "Row source Type" to "Value List"
4. Go to "Row Source" and enter list of choices,
   e.g. "ABCP";"ABS";"All Asset Classes";"CB";"CMBS";"RMBS";"SME";"SC";"LOC"

Apply Conditonal Formatting to Form

Go to Design or Layout View, click on the column/field,  a "Form Design Tool" section appears on the top menu bar, go to format ==> Conditional Formatting

example rules: ([Status]="Backlog" Or [Status]="Completed") And [Active]="Yes"