Filter Records (1 Viewer)

mtagliaferri · Jul 14, 2017

I have modified the following query to display only records where Rsigned = False when the form is opened.

Code:

SELECT tblCrewMember.StaffNumber, tblCrewMember.Surname, tblCrewMember.FirstName, tblCrewMember.Position, tblCrewMember.Base, tblCrewMember.Nationality, tblCrewMember.DOJ, tblCrewMember.Resigned, tblCrewMember.ResignedDate, tblCrewMember.DOB, tblCrewMember.IDCrewMember, tblCrewMember.Gender, tblCrewMember.CrewType, tblCrewMember.CallSign, tblCrewMember.MaidenName
FROM tblCrewMember
[COLOR="Red"]WHERE (((tblCrewMember.Resigned)=False))[/COLOR]
ORDER BY tblCrewMember.StaffNumber;

I have then three command buttons that no longer work after I changed the query, these are to filter All/Resigned/Active

Code:

Private Sub CmdViewAll_Click()
    Dim strsearch As String
    strsearch = "SELECT * from qryCrewMemberList"
    Me.RecordSource = strsearch
End Sub

Private Sub CmdViewResigned_Click()
DoCmd.BrowseTo acForm, "frmCrewMemberList", "", "[Resigned] = True", "", 1
End Sub


Private Sub cmdPrint_Click()

   Dim strFilter As String

If Me.FilterOn Then strFilter = Me.Filter

DoCmd.OpenReport "rptCrewMemberList", acViewPreview, "", strFilter, acNormal
DoCmd.RunCommand acCmdPrint

End Sub

What codes should I use to make it work, or is there different way to have those records filtered when opens, bare in mind that that form is contained in a navigational form.

pbaldy · Jul 14, 2017

For them to work the query would have to include the True records. Change that and put this in the load event:

Me.Filter = "Resigned=False"
Me.FilterOn = True

mtagliaferri · Jul 15, 2017

Thanks Paul,
I have one small issue if I modify the query as you mentioned:

Code:

SELECT tblCrewMember.StaffNumber, tblCrewMember.Surname, tblCrewMember.FirstName, tblCrewMember.Position, tblCrewMember.Base, tblCrewMember.Nationality, tblCrewMember.DOJ, tblCrewMember.Resigned, tblCrewMember.ResignedDate, tblCrewMember.DOB, tblCrewMember.IDCrewMember, tblCrewMember.Gender, tblCrewMember.CrewType, tblCrewMember.CallSign, tblCrewMember.MaidenName
FROM tblCrewMember
[COLOR="Red"]WHERE (((tblCrewMember.Resigned)=True))[/COLOR]
ORDER BY tblCrewMember.StaffNumber;

and place on the load event

Code:

Me.Filter = "Resigned=False"
Me.FilterOn = True

when the form load there are no records displayed, on the cmd "View All" it will load only the non resigned.

If I remove

Code:

[COLOR="Red"]WHERE (((tblCrewMember.Resigned)=True))[/COLOR]

when opening the form it all works correctly, it displays non resigned records and when running the cmd button "View All", "View Active" and "View Resigned"
However I have something really strange, as the form is actually inside a Navigation form wen launched through this it all cmd buttons work with the exclusion of "View Resigned"

Code:

Private Sub CmdViewActive_Click()
    DoCmd.BrowseTo acForm, "frmCrewMemberList", "", "[Resigned] = False", "", 1
End Sub

Private Sub CmdViewAll_Click()
    Dim strsearch As String
    strsearch = "SELECT * from qryCrewMemberList"
    Me.RecordSource = strsearch
End Sub

Private Sub CmdViewResigned_Click()
    DoCmd.BrowseTo acForm, "frmCrewMemberList", "", "[Resigned] = True", "", 1
End Sub

Any idea?

pbaldy · Jul 15, 2017

Well, if on load you filter out the a True records, there'd be none to browse to (I've never used that command). At some point you'd need to either unfiltered or refilter the form. To un filter:

Me.Filter= ""
Me.FilterOn = False

mtagliaferri · Jul 15, 2017

I am a bit confused....where would I put

Me.Filter=""
ME.FilterOn - False

pbaldy · Jul 15, 2017

At whatever point you want all records available. Certainly before browsing to a True records since you filter them out on load.

mtagliaferri · Jul 26, 2017

Thanks pbaldy, I got there eventuality with this!!!

Code:

Private Sub CmdViewResigned_Click()
    Me.Filter = ""
    Me.Filter = False
    DoCmd.BrowseTo acForm, "frmCrewMemberList", "", "[Resigned] = True", "", 1
End Sub

It works when I run it opening the specific form, but the final user has this form inside a Navigational form, so if I run it from the navigational form it does not work (it flashes briefly)
Any ideas of why?

isladogs · Jul 26, 2017

I've never used DoCmd.BrowseTo and not sure of its purpose.

Try this instead

Code:

Private Sub CmdViewResigned_Click()
    Me.Filter = ""
    Me.Filter = False
    DoCmd.OpenForm "frmCrewMemberList", , , "[Resigned] = True", , 1
End Sub

If an optional argument is empty, you don't need the ""

mtagliaferri · Jul 26, 2017

I have tried your solution, but still not getting the result...flickering has gone but it is displaying all records rather then the resigned ones

isladogs · Jul 26, 2017

Check the number of commas before the filter. Should be 3.

Sent from my iPhone using Tapatalk

pbaldy · Jul 26, 2017

The syntax to refer to the form changes when it's used as a subform:

http://www.theaccessweb.com/forms/frm0031.htm

And I think navigation forms are different.

mtagliaferri · Jul 26, 2017

It is definitely in a navigational form...

isladogs · Jul 26, 2017

Ah yes, you said that in post #1!
That may make life more difficult & also limit potential help as many of us avoid them!

However did you recheck the number of commas as I suggested in post #10?

mtagliaferri · Jul 27, 2017

YEs there are 3 commas

Code:

rivate Sub CmdViewResigned_Click()
    Me.Filter = ""
    Me.Filter = False
    DoCmd.OpenForm "frmCrewMemberList", , , "[Resigned] = True", , 1
End Sub

isladogs · Jul 27, 2017

Thinking about this further, your code has the argument 1 which opens the form in hidden mode.
How do you know the filter isn't working?

As the form is already open, then perhaps the existing form remains displayed

If you want it to open normally the argument is 0.

Code:

DoCmd.OpenForm "frmCrewMemberList", , , "[Resigned] = True", , 0

But this is the default so it can be omitted

Code:

DoCmd.OpenForm "frmCrewMemberList", , , "[Resigned] = True"

One more question? What happens if you omit these lines?

Code:

Me.Filter = ""
Me.Filter = False

so its just:

Code:

Private Sub CmdViewResigned_Click()
    DoCmd.OpenForm "frmCrewMemberList", , , "[Resigned] = True"
End Sub

Remember I don't use navigation forms so can't check your situation

Filter Records (1 Viewer)

mtagliaferri

Registered User.

pbaldy

Wino Moderator

mtagliaferri

Registered User.

pbaldy

Wino Moderator

mtagliaferri

Registered User.

pbaldy

Wino Moderator

mtagliaferri

Registered User.

isladogs

MVP / VIP

mtagliaferri

Registered User.

isladogs

MVP / VIP

pbaldy

Wino Moderator

mtagliaferri

Registered User.

isladogs

MVP / VIP

mtagliaferri

Registered User.

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread