Form Design to Allow Multiple Selections (Add your Ideas) (2 Viewers)

[MajP]

In response to this

@DakotaRidge and I are very interested in examining all possibilities for implementing controls with multiselection capability and store those values in normal child tables.

Sorry folks, Access World will not accept my posts

@CJ_London, what happened to the Fake MVF thread?... it's gone!... Did Gremlins invade AWF and wreaking havoc?

www.access-programmers.co.uk

This thread is designed to allow people to showcase techniques they use.
The example I am posting is the common checklist.
This uses 4 modifications to the same technique
1. using a subform
2. using a multi select listbox
3. Using an in memory ADO recordset
4. using a crosstab

These are all described in the Access Europe presentation I did recently

Access Europe User Group - Wed 2 July: Faking ‘Non-Normal’ User Interfaces with Normal Data (Pete Poppe)

The next Access Europe User Group meeting will be on Wednesday 2 July 2025 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). For local times, please check World Time Buddy In this session, we are very pleased to welcome back Pete Poppe (AKA MajP) who will cover several...

www.access-programmers.co.uk

 

[BlueSpruce]

Excellent, Thanks! Heads up, @DakotaRidge

 

[MajP]

This demo lets you fake a MVF combo box control as filter selection.

 

[BlueSpruce]

In response to this

*** Removed link that was here since BlueSpruce still not reached 100th post ***

This thread is designed to allow people to showcase techniques they use.
The example I am posting is the common checklist.
This uses 4 modifications to the same technique
1. using a subform
2. using a multi select listbox
3. Using an in memory ADO recordset
4. using a crosstab

These are all described in the Access Europe presentation I did recently

*** Removed link that was here since BlueSpruce still not reached 100th post ***

Very Nice!.. I didn't see an ER, but it's not hard to figure out the relationships. I will study your code and most likely have questions for you. Thanks again!

 

[BlueSpruce]

This demo lets you fake a MVF combo box control as filter selection.

View attachment 120744

This one is also good, but I prefer the generic version's UI cosmetics and functionality. I noticed you're using several of Colin's form handling modules:

 

[MajP]

I noticed you're using several of Colin's form handling modules:

Those are used to accurately position a pop up.
@isladogs probably has the most comprehensive discussion on this topic

Getting pop-up form to center on monitor

I'm somewhat confused by your lengthy (but incomplete) reply so apologies if I'm missing the point here Form positions and sizes are measured in twips and cannot currently exceed the integer limit of 32767 (approx. 22.75 inches) Your code will give unpredictable results if you try to exceed...

www.access-programmers.co.uk

but I better like the generic version's UI anf functionality.

Not sure what that means because there is no such thing as a generic unbound multiselect combobox.

 

[BlueSpruce]

Those are used to accurately position a pop up.

Why not just use something like this to position modal popups?

Private Sub Form_Load()
    Form.Move Left:=5400, Top:=625, Width:=18350, Height:=11250
End Sub

I use similar functions in my apps, written by Ken Getz (RIP), for manipulating forms, control handling, etc. e.g.

Not sure what that means because there is no such thing as a generic unbound multiselect combobox.

I meant, I like your Generic Checklist v2 demo app better than the Fake MVF Filette app

 

[MajP]

I meant, I like your Generic Checklist v2 demo app better than the Fake MVF Filette app

OK I guess, but those are demoing two very different concepts. Pretty much apples and oranges.

 

[BlueSpruce]

OK I guess, but those are demoing two very different concepts. Pretty much apples and oranges.

What's different about their design and functionality?

Those are used to accurately position a pop up.

Why not just use something like this sample code to accurately position modal popups?

Private Sub Form_Load()
    Form.Move Left:=5400, Top:=625, Width:=18350, Height:=11250
End Sub

 

[MajP]

What's different about their design and functionality.

I was referring to the checklist examples and the mvf filter.

Why not just use something like this sample code to accurately position modal popups?

Look, writing code hard wired to do one thing one time is easy. Making generic code that is easily reused is hard. Your suggestion would require the user to hardwire the positions every time they position a control which would be a pain, instead of just assigning the control and letting the code do it.

 

[BlueSpruce]

I was referring to the checklist examples and the mvf filter.

Look, writing code hard wired to do one thing one time is easy. Making generic code that is easily reused is hard. Your suggestion would require the user to hardwire the positions every time they position a control which would be a pain, instead of just assigning the control and letting the code do it.

Understood. As for generic positioning code, that's true. In our circumstances, we all use the same monitor size, display settings, and precisely position each popup so that we can see specific data in the background.

 

[MajP]

Understood. As for generic positioning code, that's true. In our circumstances, we all use the same monitor size, display settings, and precisely position each popup so that we can see specific data in the background.

Like I said. Doing one specific thing, hard wired is easy. @isladogs code is far more complex supporting monitors with various resolutions, resizing forms, different types of forms.

 

[RonPaii]

I made 2 changes to your example to open the FakeMVF_Control over the button that opens it from frmUsersADO.

frmUserADO, pass the Left and Top though OpenArgs

Private Sub cmdAddremove_Click()
  DoCmd.OpenForm "FakeMVF_Control", , , , , acDialog, _
                 Me.Form.WindowLeft _
               + Me.cmdAddremove.Left & _
                 "," & _
                 Me.Form.WindowTop _
               + Me.FormHeader.Height _
               + Me.cmdAddremove.Top _
               + Me.cmdAddremove.Height
End Sub

FakeMVF_Control, get the values from OpenArgs and move the form.

Private Sub MoveListControl()
   Me.Moveable = True
   If Not m_AssociatedComboBox Is Nothing Then
     Me.Move m_AssociatedComboBox.Left, m_AssociatedComboBox.Top, m_ListWidth, m_ListHeight
   Else
        If Not IsNull(Me.OpenArgs) Then
            Dim FromOpenArgs() As String
            FromOpenArgs = Split(Me.OpenArgs, ",")
            Me.Form.Move CLng(FromOpenArgs(0)), CLng(FromOpenArgs(1)), m_ListWidth, m_ListHeight
        Else
            Me.Move 1440, 1440, m_ListWidth, m_ListHeight
        End If
   End If
   Me.chkSelected.SetFocus
End Sub

 

[adhoustonj]

Solid demo. Thanks for sharing this with us. Very helpful!

 

[Josef P.]

That would be my non-fake version:

 

[MajP]

That would be my non-fake version:

As previously posted, this example uses the same technique of using a "holding table" for the MVFs.

MVF Discussion and Uses (If you plan to Use Them)

Since there is a lot of discussion now on MVF, I created this thread to provide discussion on what you can do with them and how to use them. There is plenty of discussion of the cons of MVF and what you cannot do with them, so no need to rehash that here. But assuming you are planning to use...

www.access-programmers.co.uk

Using this same concept of a "holding table" for the MVF you could use this to write to a visible junction / child table as @DakotaRidge asked. Instead of using it as a filter you could use it as a control for a visible table. But at that point I do not know if that really gets you anything. Require work, and not great benefit.

 

[Josef P.]

A fixed join with the MVF table is too inflexible for me. I retrieve the array from the MVF and use it to create the filter expression. I can then use this directly for T-SQL statements via ADODB.
I use the MVF only for visual effect.

A typical example of how I use it: filtering status values.

 

[RonPaii]

I added a empty combo box control to better emulate MVF.

Using the KeyDown and Click events to open the FakeMVF_Control and the Form_Current event to show the 1st selection in the control.

Private Sub cbAdRemove_KeyDown(KeyCode As Integer, Shift As Integer)
    If Not IsNull(Me.UserID) Then
        KeyCode = 0
        cbAdRemove_MouseUp 1, 1, 1, 1
    End If
End Sub

Private Sub cbAdRemove_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
  If Not IsNull(Me.UserID) Then
        DoCmd.OpenForm "FakeMVF_Control", , , , , acDialog, _
                       Me.Form.WindowLeft _
                     + Me.cbAdRemove.Left & _
                       "," & _
                       Me.Form.WindowTop _
                     + Me.FormHeader.Height _
                     + Me.cbAdRemove.Top _
                     + Me.cbAdRemove.Height
        Form_Current
    End If
End Sub

Private Sub Form_Current()
    On Error GoTo ErrorHandler
    Dim SQLText As String
    If IsNull(Me.UserID) Then
        Me.cbAdRemove.Value = Null
    Else
        SQLText = "SELECT tblSelections.SelectionText " & _
                    "FROM tblUserSelections " & _
                    "INNER JOIN tblSelections " & _
                            "ON tblUserSelections.SelectionID_FK " & _
                                 "= tblSelections.SelectionID " & _
                    "WHERE tblUserSelections.UserID_FK = " & Me.UserID & _
                    " ORDER BY tblSelections.SelectionText;"
         With CurrentDb.OpenRecordset(SQLText, RecordsetTypeEnum.dbOpenSnapshot)
            If .RecordCount > 0 Then
                .MoveFirst
                Me.cbAdRemove.Value = .Fields(0)
            Else
                Me.cbAdRemove.Value = Null
            End If
            .Close
        End With
    End If
Done:
    Exit Sub
ErrorHandler:
    Me.cbAdRemove.Value = Null
    Resume Done
End Sub

 

[RonPaii]

Attached is a variation of your sample database. I it makes the FakeMVF_Control form more general. It handles any selection lists and user selections as long as the record set is in the correct order and type. The calling form creates 2 record sets to be used by MVF form.

See 2 new modules and a new form FakeMVF_Control_e.
ClassUDT is use to allow passing a user defined type as an object
modMVFe has a function to open the MVF form passing a reference to ClassUDT though OpenArgs. It also includes functions to convert a object reference to a string and back.

This is the call in frmUserADO

Private Sub cbAdRemove2_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
    On Error GoTo errcbAdRemove2_MouseUp
    Dim AvailSelections As DAO.Recordset
    Dim UserSelections As DAO.Recordset
    
    If Not IsNull(Me.UserID) Then
        Set AvailSelections _
                = CurrentDb.OpenRecordset( _
                    "SELECT tblSelections.SelectionID, tblSelections.[SelectionText] " & _
                    "FROM tblSelections;", _
                    RecordsetTypeEnum.dbOpenSnapshot)
        Set UserSelections _
                = CurrentDb.OpenRecordset( _
                    "SELECT tblUserSelections.UserID_FK, tblUserSelections.SelectionID_FK " & _
                    "FROM tblUserSelections " & _
                    "WHERE tblUserSelections.UserID_FK=" & Me.UserID & ";", _
                    RecordsetTypeEnum.dbOpenDynaset, _
                    RecordsetOptionEnum.dbFailOnError)
        MVFeActivate "MVF emulate", _
                     Me.Form.WindowLeft _
                   + Me.cbAdRemove2.Left, _
                     Me.Form.WindowTop _
                   + Me.FormHeader.Height _
                   + Me.cbAdRemove2.Top _
                   + Me.cbAdRemove2.Height, _
                     Me.cbAdRemove2, _
                     AvailSelections, _
                     UserSelections
        AvailSelections.Close
        UserSelections.Close
        Form_Current
    End If
donecbAdRemove2_MouseUp:
    On Error Resume Next
    Set AvailSelections = Nothing
    Set UserSelections = Nothing
errcbAdRemove2_MouseUp:
    Resume donecbAdRemove2_MouseUp
End Sub