Need help with using popup for filtering main form

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

Re: Need help with using popup for filtering main form

Thelma Roslyn Lubkin wrote in
news:fmek7m$k3i$1@uwm.edu:

> 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

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.

Private Sub cboPrograms_Click()
FilterForm
End Sub

Private Sub cboSchools_Click()
FilterForm
End Sub

Private Sub FilterForm()
Dim p$
Dim s$
p = Nz(cboPrograms.Column(0), "")
s = Nz(cboSchools.Column(0), "")
If Len(p) > 0 And Len(s) > 0 Then
RecordSource = "SELECT enr.* FROM Enrolments enr LEFT JOIN
Classes c ON enr.Class=c.ID WHERE School=" & s & " AND Program="
& p & " ORDER BY c.Sequence" Program.DefaultValue = CLng(p)
School.DefaultValue = CLng(s)
Enrolment.Visible = True
InsideHeight = Detail.Height * 20 + FormHeader.Height +
FormFooter.Height UniqueTable = "Enrolments"
End If
End Sub

I am using MS-SQL Server as a backend so the Unique Table line is likely
to be redundant if you are not.

The Access object browser states that cboSchools.Column(0) returns a
variant. Many posters here seem to think it returns whatever the type of
the field to which its bound. After ten or fifteen years working with
the combo box object I am convinced that cboSchools.Column(0) returns a
string, always.

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.

The notation Forms!FormName!ControlName!Form blah, blah was originated
by God and given to Adam as the chief punishment for biting on the
apple.


--
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.

Re: Need help with using popup for filtering main form

lyle fairfield wrote:
: 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 ...



: 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.

Re: Need help with using popup for filtering main form

Thelma Roslyn Lubkin wrote in news:fmermj$1gt
$1@uwm.edu:

> lyle fairfield wrote:
>: 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 ...
>
>
>
>: 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.

Re: Need help with using popup for filtering main form

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.

Re: Need help with using popup for filtering main form

lyle fairfield wrote:
: 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.

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.

Re: Need help with using popup for filtering main form

Salad wrote:

: 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


: 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