How does one filter the RowSource of a combo box

[GraeagleBill]

I don't see a "Filter" qualifier in the properties of a combo box for either of ControlSource OR RowSource. So, how does one filter the data for a combo box?



As it is now, the RowSource is a saved query. I'd like NOT to have the RowSource specified as a fully expressed SQL expression.

[June7]

Then you will have to put filter parameters in the query object.

Or make the RowSource an SQL statement that pulls from the query object.

[GraeagleBill]

Then you will have to put filter parameters in the query object.

That's a new one for me. How do I do that?

[June7]

How can this be new? It is basic to building queries. Open query in design view and put parameters in the Criteria row.

Exactly what to do you want to base the filtering on?

[GraeagleBill]

I just don't know how to change the criteria from code.

The query I started with: (QFreqUsed)


What I'm trying to get to without having to express the SQL in code:

Select Case intNewTTypeID
    Case 6, 7
        Me.cboDescriptions.RowSource = "SELECT * FROM tblFreqUsed WHERE (((FreqU)=True) AND (TypeFltrID )= 67) ORDER BY Description;"
    Case 16
        Me.cboDescriptions.RowSource = "SELECT * FROM tblFreqUsed WHERE (((FreqU)=True) AND (TypeFltrID )= 16) ORDER BY Description;"
    Case Else
        Me.cboDescriptions.RowSource = "SELECT * FROM tblFreqUsed WHERE (((FreqU)=True) AND (TypeFltrID )= 0) ORDER BY Description;"
End Select

[June7]

That code should change the RowSource.

Then need:

Me.cboDescription.Requery


What is intNewTTypeID based on? Since the criteria is dependent on value of this variable, how can you avoid VBA?

[GraeagleBill]

Sorry June, I think my failure to better describe the issue has caused nothing but confusion. I started with the combo control property RowSource = QFreqUsed. As development progressed, I came up with a user requested modification to limit the content of the combo based on the current functional condition of the app. With that, I wanted to filter the RowSource of the combo. Something more like:

Private Sub tbNewDescription_DblClick(Cancel As Integer)
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
' Descriptions to come from list based on type of transaction.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Select Case intNewTTypeID
    Case 6, 7
        Me.cboDescriptions.RowSource = QFreqUsed "TypeFltrID = 67"

    Case Is > 50     ' Values greater than 50 are translations from base 16
        Me.cboDescriptions.RowSource = QFreqUsed "TypeFltrID = 16"

    Case Else
        Me.cboDescriptions.RowSource = QFreqUsed "TypeFltrID = 0"
End Select

Me.cboDescriptions.Visible = True
Me.cboDescriptions.SetFocus
Me.cboDescriptions.Dropdown

End Sub

Since I'd never had the occasion to dynamically change criteria to a RowSource query in code OTHER than with SQL as posted earlier, I thought I'd ask my true friends at the forum.

[GraeagleBill]

I'll just go with this:

Private Sub tbNewDescription_DblClick(Cancel As Integer)
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Dim intTempID As Integer
Select Case intNewTTypeID
    Case 6, 7
        intTempID = 67
    Case Is > 50     'It will be a check number if greater than 50
        intTempID = 16
    Case Else
        intTempID = 0
End Select
        
Me.cboDescriptions.RowSource = "SELECT * FROM tblFreqUsed WHERE (((FreqU)=True) AND (TypeFltrID )= " & _
                               intTempid & ") ORDER BY Description;"
Me.cboDescriptions.Visible = True
Me.cboDescriptions.SetFocus
Me.cboDescriptions.Dropdown

End Sub

[June7]

What does 'current functional condition of the app' mean? You want to filter the combobox based on some value in record on the form?

So again, what is intNewTTypeID based on? Is this a VBA variable or a field name? If this is a VBA variable, should it be set to value from a field of the form current record? Because your code does not now set value of intNewTTypeID, therefore will always flow to the Case Else.

[June7]

I still don't know what intNewTTypeID is nor how it gets a value.

Have you tested code?

[GraeagleBill]

Not sure how to answer your question other than to say that intNewTTypeID is a VBA variable that is set according to a transaction type choice the user makes. That choice is what creates the condition or state from which another dropdown choice the user makes about a description of the transaction.

This is a very robust app primarily centered around a single form behind which are 1216 lines of VBA code. The segment I posted is the sub that in part processes the additional user actions pertaining to choices about transaction descriptions. The latter is where the combo box is employed and where the new functionality came about where the choices presented are limited depending on the transaction type, i.e., the VBA variable intNewTTypeID.

The code posted in #8 works perfectly and why I marked the issue resolved.

[June7]

intNewTTypeID is not declared nor set in the procedure you posted. So I presume this is a public variable declared and set elsewhere.

[GraeagleBill]

intNewTTypeID is global to the form's module and is set elsewhere.