refiltering a form after passing an initial filter

[ArielZusya]

I've got a form that, on the click of a button, closes itself and opens a new
form with a filter value passed by a previous form. The code for that looks
as follows:

Private Sub btnOpenNext_Click ()

Dim stLinkCriteria As String
Dim stFormName As String

stLinkCriteria = "RefNum = '" & me.RefNum.Value & "'"
stFormName = "frmNext"

DoCmd.CloseForm
DoCmd.OpenForm stFormName, , , stLinkCriteria

End Sub

Once on the new form I've got an option group with toggle buttons which I'd
like to use to filter the form further. I just don't want to lose my
original filter. The way I've done the toggle button option group in the
past is by changing the RecordSource to an SQL string reflecting the change I
wanted. The code would look something like:

Private Sub optFilterMyRecords_Click()
Dim stFilterSQL As String

Select Case Me!optFilterMyRecords
Case 1 'All records
stFilterSQL = "SELECT tblMain.RefNum, _
tblMain.FirstName, tblMain.LastName, _
tblMain.ID_Main FROM tblMain;"
Case 2 'Just the records with FirstName = John
stFilterSQL = "SELECT tblMain.RefNum, _
tblMain.FirstName, tblMain.LastName, _
tblMain.ID_Main FROM tblMain WHERE _
(((tblMain.FirstName)='John'));"
Case 3 'Just the records with FirstName = Jane
stFilterSQL = "SELECT tblMain.RefNum, _
tblMain.FirstName, tblMain.LastName, _
tblMain.ID_Main FROM tblMain WHERE _
(((tblMain.FirstName)='Jane'));"
End Select

Me.RecordSource = stFilterSQL
Me.Requery

End Sub

What I want is to pass that RefNum to the frmNext but then when I click one
of the toggle buttons in the option group I want that RefNum to be
incorporated into the new query. My understanding is that when I pass the
RefNum to frmNext I'm not creating a query for frmNext but instead using
RefNum as a Filter for frmNext. Is there a way to make this all work?
Thanks for your help!

 

[Steve Sanford]

You're changing the recordsourse for the form. You can use the filter event
without having to change the record source. Try this:

Comment out the current
"Private Sub optFilterMyRecords_Click()" and paste in this one:

'-------Code begin-------------------------------
Private Sub optFilterMyRecords_Click()

Select Case Me.optFilterMyRecords
Case 1 'All records
Me.Filter = ""
Me.FilterOn = False
Case 2 'Just the records with FirstName = John
Me.Filter = "FirstName = 'John'"
Me.FilterOn = True
Case 3 'Just the records with FirstName = Jane
Me.Filter = "FirstName = 'Jane'"
Me.FilterOn = True
End Select

End Sub
'-------Code end---------------------------------


HTH

 

[ArielZusya]

Thanks for your response. The problem is this method loses my original sort
which was passed to the form which is what I was trying to avoid. Any
suggestions? Thanks.

 

[Jeanette Cunningham]

Hi Ariel,
I read your previous post.
You need the Where clause for stFilterSQL to have two fields in it.
Create a module level variable to save the form's filter value when the form
opens like this:
Put this line at the top of your form just below the line Option Explicit
Dim mstrInitFilter as string 'variable to hold value of form's
opening filter

Declaring it as a module level variable makes it available to all code on
the form all the time the form is open

When the form loads, you can grab the filter in the form's load event
mstrInitFilter = Me!Filter


Now when the option group is updated you can use both the value of LastName
and RefNum in the where clause.

example (untested air code)

Case 2 'Just the records with FirstName = John
stFilterSQL = "SELECT tblMain.RefNum, _
tblMain.FirstName, tblMain.LastName, _
tblMain.ID_Main FROM tblMain WHERE _
(((tblMain.FirstName)='John')) And tblMain.RefNum = " &
mstrInitFilter & "";

if the field RefNum is a text field, you will need to change " &
mstrInitFilter & " to
""" & mstrInitFilter & """

do something similar with each of the other cases for the option group.

Jeanette Cunningham

 

[ArielZusya]

Thanks for the response. I ended up solving the problem by passing the same
string to WhereCondition as to OpenArgs in my DoCmd.OpenForm and then on the
new form I just called to Me.OpenArgs any time I wanted to point to the
original filter I simply pointed to the Me.OpenArgs. Simple yet effective.
At any rate the code in the Case looks something like this:

Case 1
me.filter = me.openargs & " AND {second filter statement}"

 

[Steve Sanford]

Sorry, I missed the part about "RefNum = '" & me.RefNum.Value & "'" in the
open form command.

But you did it exactly right.

 

[Jeanette Cunningham]

Great!
Jeanette Cunningham

Thanks for the response. I ended up solving the problem by passing the
same
string to WhereCondition as to OpenArgs in my DoCmd.OpenForm and then on
the
new form I just called to Me.OpenArgs any time I wanted to point to the
original filter I simply pointed to the Me.OpenArgs. Simple yet effective.
At any rate the code in the Case looks something like this:

Case 1
me.filter = me.openargs & " AND {second filter statement}"