Custom filter (acCmdFilterMenu) (1 Viewer)

Capitala

Member
Local time
Today, 16:01
Joined
Oct 21, 2021
Messages
58
Good day!
I use the follwoing code to filer a filed on a form

Public Function FilterForm()
On Error GoTo errlbl
Set SelectedButton = ActiveControl
Me.Controls(ActiveControl.Tag).SetFocus
Me.Recordset.MoveFirst
DoCmd.RunCommand acCmdFilterMenu
Exit Function
errlbl:
If Err.Number = 3021 Then
MsgBox "No records Returned", vbInformation
Me.Filter = ""
Me.FilterOn = False
SetButtons
Else
MsgBox Err.Number & " " & Err.Description
End If
End Function

I use a combo box for the particular bound field. It was working fine but I had a requirement to make the row source of the combo box comes from two tables using Union SQL statement
The contents of the combo box are fine and collate data from two tables BUT when I use the filter form it shows only those elements in the control source not the row source.
Kindly advise
 

Capitala

Member
Local time
Today, 16:01
Joined
Oct 21, 2021
Messages
58
Attached a sample database.
In form (expense), the combo box (expense_item) collates data from (expenses) table and (revenues) table.
There's a filter button on form header. it shows only the contents of (expenses) table to which the combo box is bound.
I hope it's clear now
 

Attachments

  • Database2.accdb
    640 KB · Views: 328

oleronesoftwares

Passionate Learner
Local time
Today, 09:01
Joined
Sep 22, 2014
Messages
1,159
hi,
i am unable to view your database after downloading it, can you send images of the combox box in drop down mode and images of the select statement of the combo box
 

Capitala

Member
Local time
Today, 16:01
Joined
Oct 21, 2021
Messages
58
Thanks for your prompt reply. An image attached.
Illustration of issue.png
 

oleronesoftwares

Passionate Learner
Local time
Today, 09:01
Joined
Sep 22, 2014
Messages
1,159
The contents of the combo box are fine and collate data from two tables BUT when I use the filter form it shows only those elements in the control source not the row source
is the filter form a list box? lets see the query that retrieve records that is filtered.
DoCmd.RunCommand acCmdFilterMenu
is this a macro and what is it based on?


Apologies if my questions are many, if any other person is able to download the attachment sent, pls do.
 

Capitala

Member
Local time
Today, 16:01
Joined
Oct 21, 2021
Messages
58
this:
DoCmd.RunCommand acCmdFilterMenu
is a part of the function (Filterform)
this recalls the standard filter that's found just on the right top corner of a field. I think the idea will be clear only when you have a look on the database
 

Minty

AWF VIP
Local time
Today, 16:01
Joined
Jul 26, 2013
Messages
10,354
This looks like the same thing @MajP invokes here https://www.access-programmers.co.u...rm-filter-and-requires-almost-no-code.311279/

As it's an inbuilt access filter, I'm not sure how much you can amend its behaviour.
Maybe for some reason, it doesn't appear to like the union query. Have you tried creating a stored query against the union and using that as the row source?

Actually, after re-reading that, the filter would only see the available bound results I think?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:01
Joined
May 7, 2009
Messages
19,169
DoCmd.RunCommand acCmdFilterMenu
this will filter your form.
but you have "wrong" concept about it.
it will only show Items already saved to your Table/Query.
if your table has No Record, it will not show anything.
if you only have expense_item "a" saved to your table, it will only show "a".
since you have "a" item saved in one record, and "b" on another record, it will
show "a" and "b" (no "x").
try to add record with "x" and the next time you click on this button
it will show a, b, x.

so what you are seeing right now is but right.
 

Users who are viewing this thread

Top Bottom