Filtering Issue From Form To ComboBoxes

[Guest]

I have a form used as a filter form with the following code

Private Sub CmdOK_Click(
On Error GoTo Err_CmdOK_Clic
Dim strWhere As Strin
Dim strLink As Strin
If Len(Me.CmbFilter & vbNullString) = 0 The
MsgBox "Please select one of the options.", vbInformation, "Selection Required
CmbFilter.SetFocu
ElseIf Me.CmbFilter = "See All" The
DoCmd.OpenForm "Web Status Clients Form
DoCmd.Close acForm, "Web Status Clients Search Form
ElseIf Me.CmbFilter = "Filter By.." The
If Not IsNull(Me.CmbRegions) And CmbRegions.Value <> "All" The
strWhere = strWhere & strLink & "[Regions]=""" & CmbRegions & """
strLink = " And
If Me.CmbRegions.Value = All The
strWhere = strWhere & strLin
strLink = " And
End I
End I
If Not IsNull(Me.CmbActivity) And CmbActivity.Value <> "All" The
strWhere = strWhere & strLink & "[Activity Ranking]=""" & CmbActivity & """
strLink = " And
If Me.CmbActivity.Value = "All" The
strWhere = strWhere & strLin
strLink = " And
End I
End I
If Not IsNull(Me.TxtContactLName) And TxtContactLName.Value <> "All" The
strWhere = strWhere & strLink & "[Last Name]Like """ & "*" & TxtContactLName & "*" & """
strLink = " And
If Me.TxtContactLName.Value = All The
strWhere = strWhere & strLin
strLink = " And
End I
End I
DoCmd.Minimiz
DoCmd.OpenForm "Web Status Clients Form", acNormal, , strWher
'DoCmd.Close acForm , "Web Status Clients Search Form
End I
Exit_CmdOK_Click
Exit Su
Err_CmdOK_Click
MsgBox Err.Descriptio
Resume Exit_CmdOK_Clic
End Su

The form "Web Status Clients Form" opens filtered perfectly..

Now on the "Web Status Clients Form", I have two drop-downs, one bound to a table and one unbound. The bound one is used for new entries into the form and the unbound one is used as a FindFirst search. This works fine too..

The problem is I can't get the drop-downs to filter just those records...the form based on the Record Navigation toolbar shows the filter is working, but I somehow need to get the drop-downs to only show those filtered records for various filter options that the user may use in the first form...Any help is greatly appreciated! If you require more info, please do post here...Thank you!

 

[Wayne Morgan]

The Row Source of the combo boxes isn't affected by the filtering of the
form. You will need to modify the query in the Row Source of the combo boxes
to limit them to the lists that you want. This will be similar to the way
you have put together the strWhere you have now. Take the current SQL for
the Row Source query and concatenate in the correct Where part. If you do
this in a stored query rather than assigning the SQL directly to the Row
Source, you'll also have to Requery the combo box after making the change.

--
Wayne Morgan
Microsoft Access MVP

I have a form used as a filter form with the following code:

Private Sub CmdOK_Click()
On Error GoTo Err_CmdOK_Click
Dim strWhere As String
Dim strLink As String
If Len(Me.CmbFilter & vbNullString) = 0 Then
MsgBox "Please select one of the options.", vbInformation, "Selection Required"
CmbFilter.SetFocus
ElseIf Me.CmbFilter = "See All" Then
DoCmd.OpenForm "Web Status Clients Form"
DoCmd.Close acForm, "Web Status Clients Search Form"
ElseIf Me.CmbFilter = "Filter By.." Then
If Not IsNull(Me.CmbRegions) And CmbRegions.Value <> "All" Then
strWhere = strWhere & strLink & "[Regions]=""" & CmbRegions & """"
strLink = " And "
If Me.CmbRegions.Value = All Then
strWhere = strWhere & strLink
strLink = " And "
End If
End If
If Not IsNull(Me.CmbActivity) And CmbActivity.Value <> "All" Then
strWhere = strWhere & strLink & "[Activity Ranking]=""" & CmbActivity & """"
strLink = " And "
If Me.CmbActivity.Value = "All" Then
strWhere = strWhere & strLink
strLink = " And "
End If
End If
If Not IsNull(Me.TxtContactLName) And TxtContactLName.Value <> "All" Then
strWhere = strWhere & strLink & "[Last Name]Like """ & "*" & TxtContactLName & "*" & """"
strLink = " And "
If Me.TxtContactLName.Value = All Then
strWhere = strWhere & strLink
strLink = " And "
End If
End If
DoCmd.Minimize
DoCmd.OpenForm "Web Status Clients Form", acNormal, , strWhere
'DoCmd.Close acForm , "Web Status Clients Search Form"
End If
Exit_CmdOK_Click:
Exit Sub
Err_CmdOK_Click:
MsgBox Err.Description
Resume Exit_CmdOK_Click
End Sub

The form "Web Status Clients Form" opens filtered perfectly...

Now on the "Web Status Clients Form", I have two drop-downs, one bound to

a table and one unbound. The bound one is used for new entries into the
form and the unbound one is used as a FindFirst search. This works fine
too...

The problem is I can't get the drop-downs to filter just those

records...the form based on the Record Navigation toolbar shows the filter
is working, but I somehow need to get the drop-downs to only show those
filtered records for various filter options that the user may use in the
first form...Any help is greatly appreciated! If you require more info,
please do post here...Thank you!

 

[Guest]

Response to Wayne Morgan

Hello and Thanks for responding

Unfortunately I tried this but since there are so many fields that the user has the option to filter by, the result was a message from access telling me that the expression is too complex to be evaluated. Is there any way I can get the comboboxes to pull from the filter form

_End of Response

Response to SFAxess

This seems like a useful idea, but I'm not sure how to apply it. Can you please elaborate

_End of Response

Thank you both for your time and patience

__Start of Wayne Morgan Message_
The Row Source of the combo boxes isn't affected by the filtering of th
form. You will need to modify the query in the Row Source of the combo boxe
to limit them to the lists that you want. This will be similar to the wa
you have put together the strWhere you have now. Take the current SQL fo
the Row Source query and concatenate in the correct Where part. If you d
this in a stored query rather than assigning the SQL directly to the Ro
Source, you'll also have to Requery the combo box after making the change

--
Wayne Morga
Microsoft Access MV
___End of Wayne Morgan Message__

__Start of SFAxess Message_

You need to set the Row Source of your combo boxes to a
SQL statement which reflects only the records you want to
show given the filtering of the form
You can set this from the "filter form". Use a "With"
statement a la
With Forms("Web Status Clients Form"
.ComboBox1.RowSource="SELECT * FROM MyTable WHERE
Field1='John Doe';
.ComboBox2.RowSource="SELECT * FROM MyTable WHERE
Field1=12345;
End Wit

You would probably use 2 variables to represent the SQL
statement which you set in your IF ELSEIF statements
earlier
Let me know if this doesn't make sense
___End of SFAxess Message__

 

[Wayne Morgan]

Create a stored query (a query in the Queries tab of the database window)
and try setting the criteria in the design grid. Open the query and see what
you get. If the output is what you want to see in the Row Source, set the
Row Source to this query. You can uncheck the fields in the query that you
don't want to have output (i.e. those that are there just for filtering
purposes, the only ones checked to show should be the ones needed to fill
the columns in the combo box). If this works, you can modify the query when
needed the requery the combo box. Doing this in the query design grid may
make it easier to see what is happening. You can then switch to SQL view to
see what the results look like and modify the SQL as needed. This can get to
be tedious, concatenating all of it together, but it does work.

Example:
CurrentDb.QueryDefs("MyQuery").SQL = "SELECT ...... WHERE ....;"
Me.cboMyCombo.Requery

--
Wayne Morgan
Microsoft Access MVP

Response to Wayne Morgan:

Hello and Thanks for responding!

Unfortunately I tried this but since there are so many fields that the

user has the option to filter by, the result was a message from access
telling me that the expression is too complex to be evaluated. Is there any
way I can get the comboboxes to pull from the filter form?