Open From with filtered records (1 Viewer)

[mtagliaferri]

I have a form which opens a list of records from a query, all records are displayed, on the form I have some command buttons to filter these records as view All, Active or resigned

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

It all works fine, what I would like is that the initial list of records when the form is opened is only the active records (Or non Resigned); I have placed a false criteria in the query and indeed it filters all the correct records however the command button for viewing the resigned records does not work.
Is there a way as the form is opened to show only those active records without manipulating the query?

 

[missinglinq]

Return the Form's Record Source to the original Query, without the filtering, then use this in the Form_Load event:

Call CmdViewActive_Click

Linq ;0)>

 

[mtagliaferri]

I have placed the code as you have mentioned

Private Sub Form_Load()
Call CmdViewActive_Click

End Sub

But I get an error "Run Time Error 28 Out of stack place
and the debug is

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

 

[missinglinq]

So...

DoCmd.BrowseTo acForm, "frmCrewMemberList", "", "[Resigned] = False", "", 1

works fine, when triggered from a Command Button, when clicked, but if the OnClick Sub is called, it errors out?

Did you return the Form's Record Source to your original Query
, as I instructed...the Query that was used when the Command Button worked?

Linq ;0)>

 

[mtagliaferri]

That's correct, has it got to do with the form is on a navigational form which is displayed when clicking on the tab?

 

[mtagliaferri]

Still have not found the solution for this, as in the pic I have a navigational form, when the crew member tab is actioned it shows all records from a query, the record source is set to the query that simply extract the data from the table and displays them in numerical order. The 3 command buttons work perfectly with the 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

However the initial view when the crew member tab is actioned would have to filter the records from the query as only active, leaving then the bittons to filter as per required.