Solved Use List Box on a Sub-Form to Filter Query On Another Sub-Form (1 Viewer)

tmyers

Well-known member
Local time
Today, 07:15
Joined
Sep 8, 2020
Messages
1,090
I'm back! (Not that I really ever go away at this point).
With some help of a developer friend of mine, I have made a slightly complicated set of sub-forms (at least in my eyes).
Form.PNG

On this form, I have the left List Box which is just a typical list box bound to a table to show my applications list of contractors. When you select a contractor from the left list box and click the button, some VBA runs and moves the value to the right list box. For testing purposes, the two boxes before the left list box just show the ID of the selected contractor in the right list box.

Those items work wonderfully (minus the fact I need to make a button to remove items from the right list box). When you select a item from the right box, the ID appears in the box below showing that it is "holding" the ID of that contractor. You can then select a value from the combobox and enter a quantity which then populates in the queries sub form.

As you can see in the picture, the query shows everything entered regardless of what is selected (I know nothing is selected in the picture). How can I get the query or its sub form to filter so it only shows the selected contractor/ID's items? I would like it to show everything for the selected, or be blank. I think showing everything at any time could potentially confuse my users.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:15
Joined
May 21, 2018
Messages
8,463
See solution. I modified the multi select to choose all values in a listbox. Works on any data type
Code:
Option Compare Database
Option Explicit


Public Enum FieldType
  ft_Text = 0
  ft_Numeric = 1
  ft_Date = 2
  ft_Boolean = 4
End Enum

Public Function GetFilterFromList(LstBox As ListBox, FieldName As String, Field_Type As FieldType, Optional Column As Integer = 0) As String
  Dim fltr As String
  Dim I As Long
  Dim val As String

  For I = 0 To LstBox.ListCount - 1
    val = LstBox.Column(Column, I)
    If Field_Type = ft_Text Then
      val = "'" & Replace(val, "'", "''") & "'"
    ElseIf Field_Type = ft_Date Then
      val = "#" & Format(CDate(val), "mm/dd/yyyy") & "#"
    ElseIf Field_Type = ft_Boolean Then
      Select Case val
      Case "Yes", "On", "True"
        val = "-1"
      Case Else
        val = "0"
      End Select
    End If
    If fltr = "" Then
      fltr = val
    Else
      fltr = fltr & ", " & val
    End If
  Next I
  If fltr <> "" Then
    fltr = FieldName & " IN (" & fltr & ")"
  End If
  GetFilterFromList = fltr
End Function
 

Attachments

  • MultiSelectListBoxControl V2.accdb
    1.4 MB · Views: 146

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:15
Joined
May 21, 2018
Messages
8,463
To be clear in your case it will return a filter to apply to your subform. Or you can build a rowsource query.
It will return something like
TheFilter = ContractorID IN (12345, 6789, 4545, 6666)

To apply that to the subform
me.subformName.form.filter = TheFilter

Or
me.subformName.form.rowsource = "Select * from somequery WHERE " & thefilter
 

tmyers

Well-known member
Local time
Today, 07:15
Joined
Sep 8, 2020
Messages
1,090
I have very little experience in dealing with public code.
Where would I put that? On the form/subform itself?
I have only ever really done on event coding.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:15
Joined
May 21, 2018
Messages
8,463
Look at how I did it. The code is in a module. My guess you want to call it from your add button event. When you add a contractor to the right side you can apply the filter.

However I may have misread this. I thought you want to show all records in the subform related to all records in the listbox "Contractors on the Job".
Now rereading it, it sounds like maybe you mean something different. Can you clarify.
 

tmyers

Well-known member
Local time
Today, 07:15
Joined
Sep 8, 2020
Messages
1,090
Sure thing.
You would pick a contractor from the left box (one at a time because I dislike multi-select). You click the "Add" button which runs

Code:
Private Sub AddContractorToJobCmd_Click()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblContractorJob")
    rst.AddNew
    rst!ContractorID = Me.lstContractors
    rst!JobID = Me.Parent.JobID
    rst.Update
    rst.Close
    dbs.Close
    
    Set rst = Nothing
    Set dbs = Nothing
    
    Me.lstContractors.Requery
    Me.lstContractorsOnJob.Requery

End Sub

And moves the selected to the right box. When you select a contractor from the newly populated right box, the boxes below the left show their ID's (just to show me its working). I then need the query that is the datasheet to the far right to then be filtered to that ID.

In the picture I posted, there is a EM with a count of 50 for the contractor Claypool, and a count of 5 from Gutridge. I need to somehow have a button, or background code that runs to filter it to only show the selected contractor, or nothing (nothing is for my users sake). I initially thought this would be a simple WHERE function within the query, but haven't been able to get it working.

query.PNG

Above is the query in question. I tried adding the ContractorJobID to it (which is what the boxes below the left listbox show), but couldn't get it to work. Possibly due to the values for ContractorJobID being on the "main" subform, and the query being in a subform within that subform.
 

tmyers

Well-known member
Local time
Today, 07:15
Joined
Sep 8, 2020
Messages
1,090
I figured it out. I did my criteria statement incorrectly in the query.
I should have added ContractorID to the query with [Forms]![JobQuote]![ContractorJob subform].[Form]![ContractorID]. I did not go deep enough into the forms.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:15
Joined
May 21, 2018
Messages
8,463
So I did misread it. I thought you wanted records for each of the contractors, not just one.
 

tmyers

Well-known member
Local time
Today, 07:15
Joined
Sep 8, 2020
Messages
1,090
You still gave me good insight on filtering however, so I still very much appreciate your help!
 

Users who are viewing this thread

Top Bottom