acCmdfiltermenu cascading issue (1 Viewer)

chrisjames25 · Jan 10, 2019

Hi

I have a continuous form with made to look like datasheet. It has 3 columns. Category Genus Variety.

The following code brings up a filter menu for each "column" when i double click on it.

Code:

Private Sub Lbl_Column1_DblClick(Cancel As Integer)
Dim Items As Integer

Items = Me.RecordsetClone.RecordCount
If Items = 0 Then
Me.FilterOn = False

Else

DoCmd.GoToControl "Txt_Column1"
DoCmd.GoToRecord , "", acFirst
DoCmd.RunCommand acCmdFilterMenu
End If

End Sub

That works great but what i really want is the form to remember my selection. For example using drinks as an example. If i chose alcoholic from the category menu the filter would be applied but if i then double click on the next column i would still be able to choose sub drink categories that are not alcoholic. What i want is like in excel when u filter one column the following columns also filter down to only show what is available within the already filtered column - just like you do with cascading comboboxes.

Any ideas? finding nothing on the web.

Ranman256 · Jan 10, 2019

don't use GoToRecord. Show all records then filter them all.

build the 'where' clause by cycling thru all the combos....
it executes after a find button CLICK event
if null, ignore.
if not, apply.

Code:

'----------------
sub btnFilter_click()
'----------------
dim sWhere as string 

sWhere = "1=1"
if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"

If sWhere = "1=1" Then
  Me.FilterOn = False
Else
  Me.Filter = sWhere
  Me.FilterOn = True
End If
end sub

chrisjames25 · Jan 10, 2019

Hi. Sorry not understanding your response. Could be my poor explanation.

I have sorted cascading combovoxes. It is the inbuilt filter menu I am struggling to cascade in a continuous form made to look like data sheet. I will strip my database out tomorrow and upload a sample database to show what I am after A’s appreciate I am rubbish at explaining.

chrisjames25 · Jan 11, 2019

Hi

Please find attached demo data.

As mentioned in previous posts. Whati want is if i double click the label for column 1 "category" it brings up a filter menu.

If i filter just Clematis in the category section i want column 2 when i double click it to only show the filter options that are within the selected category chosen in column 1.

At present it would show me every genus that is present in the genus table.

Hope that makes sense

chrisjames25 · Jan 12, 2019

Hi. I solved the issue. The fix has left me confused but happy it works.

The form was originally populated by a record source constructed within the form itself by clicking on the record source button and creating a query.

I decided to create same query and save it as a query. I then used this qry as my recordsource in the form and it works exactly how i want it to work.

What is the differnece. Why would one work and not the other. THe qry itself was the same. Just one was saved and one wasnt?

acCmdfiltermenu cascading issue (1 Viewer)

chrisjames25

Registered User.

Ranman256

Well-known member

chrisjames25

Registered User.

chrisjames25

Registered User.

Attachments

chrisjames25

Registered User.

Similar threads

Users who are viewing this thread