Need help with using popup for filtering main form

[Thelma Roslyn Lubkin]

I am still having trouble trying to use a popup form to allow user to
set filters for the main form.
The main form is based on a single table.

The popup contains 5 listboxes, so the user can filter on 5 fields in
this table, and can include as many field values as s/he needs.
The popup is reached from a command button on the main form
[I'm including only enough code to demonstrate what I'm trying to do]:

This button is on the main form, Datasystem:
Private Sub ButtonChooseFilter_Click()

Static intOpen As Integer
If intOpen = 0 Then
'Popup will remain open while main form is open
DoCmd.OpenForm "filterForDatasystem"
intOpen = 1
End If
'But popup will only be visible when user requests filter change
Forms!filterForDatasystem.Visible = True
Forms!filterForDatasystem.SetFocus
--------------------------

This button is on the popup, forFilterDatasystem
Sub ButtonApplyFilter_Click()

strSQL = SQLfilter 'Function SQLfilter builds SQL statement from the
'content of the listboxes

'Pass the filter, i.e. strSQL to a mainForm text box
Forms!Datasystem.txtSQL.Value = strSQL
Forms!Datasystem.SetFocus

'This instead of closing popup, as I want listboxes
'to retain their values for the next time user wants
'to change filter
Me.Visible = False
-----------------------------

'Back on the main form:
Private Sub Form_gotFocus()

Static strSQLText As String

If txtSQL.Value <strSQLText Then

Call Updatefilter
strSQLText = txtSQL.Value
End If
Sub Updatefilter()

strFilterSQL = txtSQL.Value

Me.Filter = strFilterSQL 'Set Filter criteria
Me.FilterOn = True 'Turn Filter on
'build txtFilters.value
Me.Requery 'Apply New Filter

I've been trying variations of this scheme for two days now
with no success.

At some point the whole process will lock up and I will get either
a polite Microsoft message that an unexplained error has occurred
and I've lost all my changes -- this is the gentlest blowup: at least
I can get to what I had before OR

The form cannot be opened because there isn't enough memory available.
This means I can't see it in design mode; I can't see its code: I have
nothing but my backup left [fortunately I have one]

I have no idea how to go about debugging such a thing: I tried to set
a breakpoint in the buttonApplyFilter_click routine and it was
ignored. I can't use debug.pring because my form is completely
unreachable after I try to run it...I need a debugging strategy

thanks, --thelma

[Thelma Roslyn Lubkin]

lyle fairfield <ly******@yahoo.cawrote:
: Today I filtered a formn not with a popup, but with two comboboxes in
: the form's header.

: Here is my code. It might be helpful or it might not.

Thank you, but I had written the form with the five listboxes
in the form's header, and that worked ... and then it was
decided that the main form needed that header space ...
<snipped code>
: I hope you will not be offended when I say that good code is almost
: always simple code. In addition for me it is almost (well let's
: emphasize the mostly he says remembering he posted some rambling junk
: earlier in the day) always short code. After twenty or so lines in a
: procedure, my eyes cross and I need a jolt of a quaint Canadian
: medicinal prescription called Newfie Screech.

I'm afraid that it's much easier to appreciate simple, elegant
code than it is to write it. I am happy just to be able to
struggle through to something that works.

I am still very interested in any thoughts on a debugging
strategy for my quandary.

--thelma
: --
: lyle fairfield

: In ancient times Ulysses had to outwit the sirens, who had the power to
: charm by their song all sailors who heard them, so that mariners were
: compelled to cast themselves into the sea to their destruction. Today,
: he would have to deal with (free) Aim-Mail.

[lyle fairfield]

Thelma Roslyn Lubkin <th****@alpha2.csd.uwm.eduwrote in news:fmermj$1gt
$[e-mail]:

lyle fairfield <ly******@yahoo.cawrote:
: Today I filtered a formn not with a popup, but with two comboboxes in
: the form's header.

: Here is my code. It might be helpful or it might not.

Thank you, but I had written the form with the five listboxes
in the form's header, and that worked ... and then it was
decided that the main form needed that header space ...
<snipped code>
: I hope you will not be offended when I say that good code is almost
: always simple code. In addition for me it is almost (well let's
: emphasize the mostly he says remembering he posted some rambling junk
: earlier in the day) always short code. After twenty or so lines in a
: procedure, my eyes cross and I need a jolt of a quaint Canadian
: medicinal prescription called Newfie Screech.

I'm afraid that it's much easier to appreciate simple, elegant
code than it is to write it. I am happy just to be able to
struggle through to something that works.

I am still very interested in any thoughts on a debugging
strategy for my quandary.

When I am faced with this problem I put, at the end of each function,
method, involved loop, or suspect piece of code

MsgBox 1
(for the first)

Msgbox 2
(for the second)

Then I run the code, clicking OK to my msgBox PopUps.

If "5" is the last message I get before the big crash, then I say to
myself,

"Ah hah! Everything seems OK up to Msgbox 5, but something between Msgbox
5 and Msgbox 6 is at fault."

Examination of the code between between Msgbox 5 and Msgbox 6 may make me
say, "Of course, it's !!!whatever!!!, what an idiot I am!"

If it doesn't I remove my Msgboxes and break up the code between Msgbox 5
and Msgbox 6 with new Msgbox steps at what seem like appropriate places.

I may repeat this process ending up with a line by line insertion if
necessary.

In a few minutes I have found the offending line of code.
--
lyle fairfield

In ancient times Ulysses had to outwit the sirens, who had the power to
charm by their song all sailors who heard them, so that the mariners were
compelled to cast themselves into the sea to their destruction.
Today, he would have to deal with (free) Aim-Mail.

[Salad]

Thelma Roslyn Lubkin wrote:

I am still having trouble trying to use a popup form to allow user to
set filters for the main form.
The main form is based on a single table.

The popup contains 5 listboxes, so the user can filter on 5 fields in
this table, and can include as many field values as s/he needs.
The popup is reached from a command button on the main form
[I'm including only enough code to demonstrate what I'm trying to do]:

This button is on the main form, Datasystem:
Private Sub ButtonChooseFilter_Click()

Static intOpen As Integer
If intOpen = 0 Then
'Popup will remain open while main form is open
DoCmd.OpenForm "filterForDatasystem"
intOpen = 1
End If
'But popup will only be visible when user requests filter change
Forms!filterForDatasystem.Visible = True
Forms!filterForDatasystem.SetFocus
--------------------------

This button is on the popup, forFilterDatasystem
Sub ButtonApplyFilter_Click()

strSQL = SQLfilter 'Function SQLfilter builds SQL statement from the
'content of the listboxes

'Pass the filter, i.e. strSQL to a mainForm text box
Forms!Datasystem.txtSQL.Value = strSQL
Forms!Datasystem.SetFocus

'This instead of closing popup, as I want listboxes
'to retain their values for the next time user wants
'to change filter
Me.Visible = False
-----------------------------

'Back on the main form:
Private Sub Form_gotFocus()

Static strSQLText As String

If txtSQL.Value <strSQLText Then

Call Updatefilter
strSQLText = txtSQL.Value
End If
Sub Updatefilter()

strFilterSQL = txtSQL.Value

Me.Filter = strFilterSQL 'Set Filter criteria
Me.FilterOn = True 'Turn Filter on
'build txtFilters.value
Me.Requery 'Apply New Filter

I've been trying variations of this scheme for two days now
with no success.

At some point the whole process will lock up and I will get either
a polite Microsoft message that an unexplained error has occurred
and I've lost all my changes -- this is the gentlest blowup: at least
I can get to what I had before OR

The form cannot be opened because there isn't enough memory available.
This means I can't see it in design mode; I can't see its code: I have
nothing but my backup left [fortunately I have one]

I have no idea how to go about debugging such a thing: I tried to set
a breakpoint in the buttonApplyFilter_click routine and it was
ignored. I can't use debug.pring because my form is completely
unreachable after I try to run it...I need a debugging strategy

thanks, --thelma

When you set a filter, Me.Filter = "whatever", Me.FilterOn = True,
there's really no need to Requery...except to slow things down while
requerying your new filter that has just been applied.

Anyway, you use Forms!filterForDatasystem.SetFocus. I created 2 forms;
Form4 and Form5. Each form contains only a command button. You can
create them for testing if you'd like.

Form4's code
Private Sub Command0_Click()
If Not IsLoaded("Form5") Then 'see many examples for IsLoaded
DoCmd.OpenForm "Form5"
Else
Forms("Form5").Visible = True
DoCmd.SelectObject acForm, "Form5"
End If
End Sub
Private Sub Form_Activate()
MsgBox "Activate" 'always executes
End Sub
Private Sub Form_Close()
If IsLoaded("Form5") Then DoCmd.Close acForm, "Form5"
End Sub
Private Sub Form_GotFocus()
MsgBox "Form4 Got Focus" 'never executes
End Sub

Form5's code
Private Sub Command0_Click()
Me.Visible = False

Forms!Form4.Form.SetFocus 'kicks on Form4's Activate. Unneeded
MsgBox "Form5 SetFocus"

'You can comment out the above 2 lines and use the
'following instead of set focus.
DoCmd.SelectObject acForm, "Form4" 'kicks on Form4's Activate. Use
MsgBox "Form5 Select object"

End Sub

You mention that you posted only the necessary code. If you didn't get
the gist from the above code or from Lyle's code then that is where your
error is, not in the code you posted.

[Thelma Roslyn Lubkin]

lyle fairfield <ly******@yahoo.cawrote:
: When I am faced with this problem I put, at the end of each function,
: method, involved loop, or suspect piece of code

: MsgBox 1
: (for the first)

: Msgbox 2
: (for the second)

: Then I run the code, clicking OK to my msgBox PopUps. <snip>

Does this mean that after I've done something to change my code,
that I should simply begin a debug run without trying to get back
to the form view to run, because I've had things die so that
simply clicking to return from code-view to design-view hung
everything...you don't need to answer: I will of course try that:
kicking myself in the forehead for not thinking of it myself.
Thank you. --thelma
: --
: lyle fairfield

: In ancient times Ulysses had to outwit the sirens, who had the power to
: charm by their song all sailors who heard them, so that the mariners were
: compelled to cast themselves into the sea to their destruction.
: Today, he would have to deal with (free) Aim-Mail.

[Thelma Roslyn Lubkin]

Salad <oi*@vinegar.comwrote:

: When you set a filter, Me.Filter = "whatever", Me.FilterOn = True,
: there's really no need to Requery...except to slow things down while
: requerying your new filter that has just been applied.

I will drop that requery then.

: Anyway, you use Forms!filterForDatasystem.SetFocus. I created 2 forms;
: Form4 and Form5. Each form contains only a command button. You can
: create them for testing if you'd like.

: Form4's code
: Private Sub Command0_Click()
: If Not IsLoaded("Form5") Then 'see many examples for IsLoaded
: DoCmd.OpenForm "Form5"

and replace my static variable by a test on IsLoaded.
: Else
: Forms("Form5").Visible = True
: DoCmd.SelectObject acForm, "Form5"
: End If

and use SelectObject in place of SetFocus
: End Sub
<snip>

: You mention that you posted only the necessary code. If you didn't get
: the gist from the above code or from Lyle's code then that is where your
: error is, not in the code you posted.

Thanks for these refinements.

If I've understood what he's saying, Lyle has now
shown me that I *can* debug, even as my mistakes
crash down so fast, so I'll try again

thank you, --thelma