Faking a Multi Value Field (1 Viewer)

[MajP]

I have seen a few posts lately on people wanting to use multi valued fields. Most Access developers steer away from them and build their own related tables. Although they are properly normalized and work well, they can get very confusing even if you know the ins and outs. I wanted to see if I could make a fake generic MV control that could be easily tailored. I could only make it so generic. Now I understand why it takes a wizard to make these. They have to store a lot of meta data about the related tables. So I give my solution about a B- because it still requires sufficient work to tailor it, but it will be much much faster then building from scratch.

I tried this as a subform which would be a simpler approach to tailor, but I kept crashing the application for some reason.

1) You have to copy and modify the fake MVF control for each MVF you want to fake.
2) You have to roll your own concatenation procedure
3) you modify only modify the constants and sql string in these parts of the code

'--------------------------------------- MODIFY CONSTANTS -----------------------------------------
  Const AssociatedCombBoxName = "cboFlagColors"
  Const Domain = "tblColors"
  Const ValueField = "Color"
  Const PK_Field = "ColorID"
  Const Caption = "Select Color"
  Const FormName = "frmCOuntries"
  Const ListWidth = 2.5 'Inches
  Const ListHeight = 3.5 'Inches
  '--------------------------------------- END MODIFY CONSTANTS -----------------------------------------

'------------------------------------------------------  Non Generic Code --------------------------------------------------------
'------------------------------------------------------  These Two Procedures will Need Modification ---------------------
'Code needs to be tailored for different tables and fields
Private Sub FillSelections()
  'Fills the selections in the Fake MVF control based on the current ID
  Dim rs As ADODB.Recordset
  Dim rsSelections As DAO.Recordset
  Dim currentID As Long
  Dim selectionID As Long
  Set rs = Me.Recordset
  Dim strSql As String
  currentID = m_ParentForm.CountryID
  strSql = "Select ColorID_FK from tblCountryFlagColor where countryID_fk = " & currentID 'modifysql
  Set rsSelections = CurrentDb.OpenRecordset(strSql)
  Do While Not rsSelections.EOF
      rs.Find "PK_Field = " & rsSelections!colorID_FK 'modify foreign key
      rs!Selected = True
      rs.Update
    rsSelections.MoveNext
  Loop
  rs.MoveFirst
  Set Me.Recordset = rs
End Sub

Private Sub UpdateSelections()
  'Writes and deletes selection to the related table
  Dim rs As ADODB.Recordset
  Dim currentID As Long
  Dim selectionID As Long
  Set rs = Me.Recordset
  Dim strSql As String
  currentID = m_ParentForm.CountryID
  rs.MoveFirst
  Do While Not rs.EOF
    If rs.Fields("Selected") = True Then
      selectionID = rs.Fields("PK_Field")
      strSql = "Insert INTO tblCountryFlagColor (CountryID_FK, colorID_FK) values (" & currentID & ", " & selectionID & ")"
     ' Debug.Print strSql
      CurrentDb.Execute strSql
    End If
    rs.MoveNext
  Loop
  rs.MoveFirst

  Do While Not rs.EOF
    If rs.Fields("Selected") = False Then
      selectionID = rs.Fields("PK_Field")
      strSql = "delete * from tblCountryFlagColor where countryID_FK = " & currentID & " AND ColorID_FK = " & selectionID
     ' Debug.Print strSql
      CurrentDb.Execute strSql
    End If
    rs.MoveNext
  Loop
  m_ParentForm.Recalc
End Sub

  strSql = "SELECT Color FROM tblCountryFlagColor INNER JOIN tblColors ON tblCountryFlagColor.ColorID_FK = tblColors.ColorID "
  strSql = strSql & " WHERE CountryID_FK = " & CountryID & " ORDER BY CountryID_FK"
  Set rs = CurrentDb.OpenRecordset(strSql)
  Do While Not rs.EOF
    If ConcatColors = "" Then
      ConcatColors = rs!Color
    Else
      ConcatColors = ConcatColors & "; " & rs!Color
    End If
    rs.MoveNext
  Loop
End Function

@isladogs. As mentioned a while back here is an example use of a ADODB in memory (disconnected) recordset. Also I was going to use your code to precisely position the popup, but did not get around to it.