Capturing SubForm Events

[Ed Warren]

Setup

Form1 --> unbound (just acts as a container for the other two forms)
Subform1a -> Bound to Query1
SubForm1b - Bound to Query2

Desired:
When the onCurrent Event fires in SubForm1a -->
set the filter on subForm1b to [ForeignKey] = [SubForm1a
Primary Key]

I have it almost working by using a button on Form1 to call a sub procedure
in SubForm1b to apply the filter.

SubForm1b

Public Sub setFilter(passedKey)
me.Filter = "[ForeignKey] = " & passedKey
me.FilterOn = true
End Sub


Form1.Button1 OnClick Event

Private Sub Button1_Click()
Forms![Form1]![Form1b].Form.SetFilter (PrimaryKey)
Me.TherapistList.Visible = True
End Sub


What I cannot make happen is a call from SubForm1a OnCurrent to the
Button1_Click() procedure.

The call from the onCurrent event in SubForm1a :

Forms("Form1").Form.Button1_Click

Generates an error ---

At this point I have a "GCE" -- General conceptual Error and cannot figure
out how to make them work well together.

Any help appreciated

Thanks

Ed Warren

 

[tina]

well, i'm assuming that you want the form to open with SubForm1b already
filtered to match the first record in SubForm1a. correct?

to do this, forget the button on the main form, and the other VBA code you
posted. instead, start fresh with the main form, and the two subforms, and
no "filtering" code anywhere. for brevity, i'll refer to the subforms as A
and B, respectively. in the Current event of A, add the following procedure,
as

Private Sub Form_Current()

On Error Resume Next

Me.Parent!ChildSubForm1b.Form.Filter = _
"ForeignKey = " & Me!PrimaryKey
Me.Parent!ChildSubForm1b.Form.FilterOn = True

End Sub

substitute the correct field names, of course.
in the Load event of B, add the following procedure, as

Private Sub Form_Load()

On Error Resume Next

Me.Filter = "ForeignKey = " _
& Me.Parent!ChildSubForm1b!PrimaryKey
Me.FilterOn = True

End Sub

again, substitute the correct fieldnames.
here's what happens:
1) if A loads first, the code in the Current event errs (and continues
without stopping execution) because B has not loaded yet - and so can't be
referenced. no problem, because when B loads next, its' code runs
successfully because A is available to be referenced. so the result is a
correctly filtered subform.
2) if B loads first, the code in the Load event errs (and continues w/o
stopping execution) because A has not loaded yet. again, no problem, because
when A loads next, its' code runs successfully because B is available to be
referenced.
either way, once the main form is open, the code on the Current event of A
correctly filters B, each time you move between records.

hth

 

[Guest]

Ed:

I would do this a different way. No need for code as you have. On form#2
the data source is Query2. Set the 'Criteria' of the ForeignKey field in
Query2 to the value of the PrimaryKey field on SubForm1A like this.

=[forms]![Form1]![SubForm1A].form![SubForm1a Primary Key]

You may have to issue a DoCmd.Requery or a Me.Refresh command on an
AfterUpdate action.

Jim

 

[Ed Warren]

Thanks and I've already been there, and you get the error that the subform
does not support the filter action. (also noted in a MicroSoft KB article),
so I tried a work around.

Ed Warren.

well, i'm assuming that you want the form to open with SubForm1b already
filtered to match the first record in SubForm1a. correct?

to do this, forget the button on the main form, and the other VBA code you
posted. instead, start fresh with the main form, and the two subforms, and
no "filtering" code anywhere. for brevity, i'll refer to the subforms as A
and B, respectively. in the Current event of A, add the following
procedure,
as

Private Sub Form_Current()

On Error Resume Next

Me.Parent!ChildSubForm1b.Form.Filter = _
"ForeignKey = " & Me!PrimaryKey
Me.Parent!ChildSubForm1b.Form.FilterOn = True

End Sub

substitute the correct field names, of course.
in the Load event of B, add the following procedure, as

Private Sub Form_Load()

On Error Resume Next

Me.Filter = "ForeignKey = " _
& Me.Parent!ChildSubForm1b!PrimaryKey
Me.FilterOn = True

End Sub

again, substitute the correct fieldnames.
here's what happens:
1) if A loads first, the code in the Current event errs (and continues
without stopping execution) because B has not loaded yet - and so can't be
referenced. no problem, because when B loads next, its' code runs
successfully because A is available to be referenced. so the result is a
correctly filtered subform.
2) if B loads first, the code in the Load event errs (and continues w/o
stopping execution) because A has not loaded yet. again, no problem,
because
when A loads next, its' code runs successfully because B is available to
be
referenced.
either way, once the main form is open, the code on the Current event of A
correctly filters B, each time you move between records.

hth

Setup

Form1 --> unbound (just acts as a container for the other two forms)
Subform1a -> Bound to Query1
SubForm1b - Bound to Query2

Desired:
When the onCurrent Event fires in SubForm1a -->
set the filter on subForm1b to [ForeignKey] = [SubForm1a
Primary Key]

I have it almost working by using a button on Form1 to call a sub procedure
in SubForm1b to apply the filter.

SubForm1b

Public Sub setFilter(passedKey)
me.Filter = "[ForeignKey] = " & passedKey
me.FilterOn = true
End Sub


Form1.Button1 OnClick Event

Private Sub Button1_Click()
Forms![Form1]![Form1b].Form.SetFilter (PrimaryKey)
Me.TherapistList.Visible = True
End Sub


What I cannot make happen is a call from SubForm1a OnCurrent to the
Button1_Click() procedure.

The call from the onCurrent event in SubForm1a :

Forms("Form1").Form.Button1_Click

Generates an error ---

At this point I have a "GCE" -- General conceptual Error and cannot figure
out how to make them work well together.

Any help appreciated

Thanks

Ed Warren

 

[Ed Warren]

I've been there and when you do the requery, Access takes you go back to the
first record in the first form.

I've always done this with two separate forms, using code to check if the
second is open then applying the filter to it. This time I wanted to have
two tables alongside each other in a common form in sync. So guess I have
to go back to my old ways.

Thanks,

Ed Warren

Ed:

I would do this a different way. No need for code as you have. On form#2
the data source is Query2. Set the 'Criteria' of the ForeignKey field in
Query2 to the value of the PrimaryKey field on SubForm1A like this.

=[forms]![Form1]![SubForm1A].form![SubForm1a Primary Key]

You may have to issue a DoCmd.Requery or a Me.Refresh command on an
AfterUpdate action.

Jim

Setup

Form1 --> unbound (just acts as a container for the other two forms)
Subform1a -> Bound to Query1
SubForm1b - Bound to Query2

Desired:
When the onCurrent Event fires in SubForm1a -->
set the filter on subForm1b to [ForeignKey] = [SubForm1a
Primary Key]

I have it almost working by using a button on Form1 to call a sub
procedure
in SubForm1b to apply the filter.

SubForm1b

Public Sub setFilter(passedKey)
me.Filter = "[ForeignKey] = " & passedKey
me.FilterOn = true
End Sub


Form1.Button1 OnClick Event

Private Sub Button1_Click()
Forms![Form1]![Form1b].Form.SetFilter (PrimaryKey)
Me.TherapistList.Visible = True
End Sub


What I cannot make happen is a call from SubForm1a OnCurrent to the
Button1_Click() procedure.

The call from the onCurrent event in SubForm1a :

Forms("Form1").Form.Button1_Click

Generates an error ---

At this point I have a "GCE" -- General conceptual Error and cannot
figure
out how to make them work well together.

Any help appreciated

Thanks

Ed Warren

 

[tina]

hmm, well, i tested the solution in an Access 2000 db running in Access 2003
on Windows 2000 Pro, before posting. a difference in the db version,
software version, or OS, could be the cause or a contributing factor in the
different results we got. sorry my solution didn't work for you.

Thanks and I've already been there, and you get the error that the subform
does not support the filter action. (also noted in a MicroSoft KB article),
so I tried a work around.

Ed Warren.

well, i'm assuming that you want the form to open with SubForm1b already
filtered to match the first record in SubForm1a. correct?

to do this, forget the button on the main form, and the other VBA code you
posted. instead, start fresh with the main form, and the two subforms, and
no "filtering" code anywhere. for brevity, i'll refer to the subforms as A
and B, respectively. in the Current event of A, add the following
procedure,
as

Private Sub Form_Current()

On Error Resume Next

Me.Parent!ChildSubForm1b.Form.Filter = _
"ForeignKey = " & Me!PrimaryKey
Me.Parent!ChildSubForm1b.Form.FilterOn = True

End Sub

substitute the correct field names, of course.
in the Load event of B, add the following procedure, as

Private Sub Form_Load()

On Error Resume Next

Me.Filter = "ForeignKey = " _
& Me.Parent!ChildSubForm1b!PrimaryKey
Me.FilterOn = True

End Sub

again, substitute the correct fieldnames.
here's what happens:
1) if A loads first, the code in the Current event errs (and continues
without stopping execution) because B has not loaded yet - and so can't be
referenced. no problem, because when B loads next, its' code runs
successfully because A is available to be referenced. so the result is a
correctly filtered subform.
2) if B loads first, the code in the Load event errs (and continues w/o
stopping execution) because A has not loaded yet. again, no problem,
because
when A loads next, its' code runs successfully because B is available to
be
referenced.
either way, once the main form is open, the code on the Current event of A
correctly filters B, each time you move between records.

hth

Setup

Form1 --> unbound (just acts as a container for the other two forms)
Subform1a -> Bound to Query1
SubForm1b - Bound to Query2

Desired:
When the onCurrent Event fires in SubForm1a -->
set the filter on subForm1b to [ForeignKey] = [SubForm1a
Primary Key]

I have it almost working by using a button on Form1 to call a sub procedure
in SubForm1b to apply the filter.

SubForm1b

Public Sub setFilter(passedKey)
me.Filter = "[ForeignKey] = " & passedKey
me.FilterOn = true
End Sub


Form1.Button1 OnClick Event

Private Sub Button1_Click()
Forms![Form1]![Form1b].Form.SetFilter (PrimaryKey)
Me.TherapistList.Visible = True
End Sub


What I cannot make happen is a call from SubForm1a OnCurrent to the
Button1_Click() procedure.

The call from the onCurrent event in SubForm1a :

Forms("Form1").Form.Button1_Click

Generates an error ---

At this point I have a "GCE" -- General conceptual Error and cannot figure
out how to make them work well together.

Any help appreciated

Thanks

Ed Warren

 

[tina]

btw, sometimes, if Access gets snitty about recognizing a subform as a form
object, you can make it happy by using a With statement, as

Private Sub Form_Current()

On Error Resume Next

Dim lngPrimKey As Long

lngPrimKey = Me!PrimaryKey

With Me.Parent!ChildSubForm1b.Form
.Filter = "ForeignKey = " & lngPrimKey
.FilterOn = True
End With

End Sub

hth

Thanks and I've already been there, and you get the error that the subform
does not support the filter action. (also noted in a MicroSoft KB article),
so I tried a work around.

Ed Warren.

well, i'm assuming that you want the form to open with SubForm1b already
filtered to match the first record in SubForm1a. correct?

to do this, forget the button on the main form, and the other VBA code you
posted. instead, start fresh with the main form, and the two subforms, and
no "filtering" code anywhere. for brevity, i'll refer to the subforms as A
and B, respectively. in the Current event of A, add the following
procedure,
as

Private Sub Form_Current()

On Error Resume Next

Me.Parent!ChildSubForm1b.Form.Filter = _
"ForeignKey = " & Me!PrimaryKey
Me.Parent!ChildSubForm1b.Form.FilterOn = True

End Sub

substitute the correct field names, of course.
in the Load event of B, add the following procedure, as

Private Sub Form_Load()

On Error Resume Next

Me.Filter = "ForeignKey = " _
& Me.Parent!ChildSubForm1b!PrimaryKey
Me.FilterOn = True

End Sub

again, substitute the correct fieldnames.
here's what happens:
1) if A loads first, the code in the Current event errs (and continues
without stopping execution) because B has not loaded yet - and so can't be
referenced. no problem, because when B loads next, its' code runs
successfully because A is available to be referenced. so the result is a
correctly filtered subform.
2) if B loads first, the code in the Load event errs (and continues w/o
stopping execution) because A has not loaded yet. again, no problem,
because
when A loads next, its' code runs successfully because B is available to
be
referenced.
either way, once the main form is open, the code on the Current event of A
correctly filters B, each time you move between records.

hth

Setup

Form1 --> unbound (just acts as a container for the other two forms)
Subform1a -> Bound to Query1
SubForm1b - Bound to Query2

Desired:
When the onCurrent Event fires in SubForm1a -->
set the filter on subForm1b to [ForeignKey] = [SubForm1a
Primary Key]

I have it almost working by using a button on Form1 to call a sub procedure
in SubForm1b to apply the filter.

SubForm1b

Public Sub setFilter(passedKey)
me.Filter = "[ForeignKey] = " & passedKey
me.FilterOn = true
End Sub


Form1.Button1 OnClick Event

Private Sub Button1_Click()
Forms![Form1]![Form1b].Form.SetFilter (PrimaryKey)
Me.TherapistList.Visible = True
End Sub


What I cannot make happen is a call from SubForm1a OnCurrent to the
Button1_Click() procedure.

The call from the onCurrent event in SubForm1a :

Forms("Form1").Form.Button1_Click

Generates an error ---

At this point I have a "GCE" -- General conceptual Error and cannot figure
out how to make them work well together.

Any help appreciated

Thanks

Ed Warren

 

[Ed Warren]

Thanks, I tried your solution "from scratch" and it works just as
advertised. I must have put in a 'fat finger' edit somewhere the first time
I tried it.

Ed Warren

btw, sometimes, if Access gets snitty about recognizing a subform as a
form
object, you can make it happy by using a With statement, as

Private Sub Form_Current()

On Error Resume Next

Dim lngPrimKey As Long

lngPrimKey = Me!PrimaryKey

With Me.Parent!ChildSubForm1b.Form
.Filter = "ForeignKey = " & lngPrimKey
.FilterOn = True
End With

End Sub

hth

Thanks and I've already been there, and you get the error that the
subform
does not support the filter action. (also noted in a MicroSoft KB article),
so I tried a work around.

Ed Warren.

well, i'm assuming that you want the form to open with SubForm1b
already
filtered to match the first record in SubForm1a. correct?

to do this, forget the button on the main form, and the other VBA code you
posted. instead, start fresh with the main form, and the two subforms, and
no "filtering" code anywhere. for brevity, i'll refer to the subforms
as A
and B, respectively. in the Current event of A, add the following
procedure,
as

Private Sub Form_Current()

On Error Resume Next

Me.Parent!ChildSubForm1b.Form.Filter = _
"ForeignKey = " & Me!PrimaryKey
Me.Parent!ChildSubForm1b.Form.FilterOn = True

End Sub

substitute the correct field names, of course.
in the Load event of B, add the following procedure, as

Private Sub Form_Load()

On Error Resume Next

Me.Filter = "ForeignKey = " _
& Me.Parent!ChildSubForm1b!PrimaryKey
Me.FilterOn = True

End Sub

again, substitute the correct fieldnames.
here's what happens:
1) if A loads first, the code in the Current event errs (and continues
without stopping execution) because B has not loaded yet - and so can't be
referenced. no problem, because when B loads next, its' code runs
successfully because A is available to be referenced. so the result is
a
correctly filtered subform.
2) if B loads first, the code in the Load event errs (and continues w/o
stopping execution) because A has not loaded yet. again, no problem,
because
when A loads next, its' code runs successfully because B is available
to
be
referenced.
either way, once the main form is open, the code on the Current event
of A
correctly filters B, each time you move between records.

hth


Setup

Form1 --> unbound (just acts as a container for the other two forms)
Subform1a -> Bound to Query1
SubForm1b - Bound to Query2

Desired:
When the onCurrent Event fires in SubForm1a -->
set the filter on subForm1b to [ForeignKey] = [SubForm1a
Primary Key]

I have it almost working by using a button on Form1 to call a sub
procedure
in SubForm1b to apply the filter.

SubForm1b

Public Sub setFilter(passedKey)
me.Filter = "[ForeignKey] = " & passedKey
me.FilterOn = true
End Sub


Form1.Button1 OnClick Event

Private Sub Button1_Click()
Forms![Form1]![Form1b].Form.SetFilter (PrimaryKey)
Me.TherapistList.Visible = True
End Sub


What I cannot make happen is a call from SubForm1a OnCurrent to the
Button1_Click() procedure.

The call from the onCurrent event in SubForm1a :

Forms("Form1").Form.Button1_Click

Generates an error ---

At this point I have a "GCE" -- General conceptual Error and cannot
figure
out how to make them work well together.

Any help appreciated

Thanks

Ed Warren

 

[tina]

you're welcome, Ed, glad it worked for you in the end!

Thanks, I tried your solution "from scratch" and it works just as
advertised. I must have put in a 'fat finger' edit somewhere the first time
I tried it.

Ed Warren

btw, sometimes, if Access gets snitty about recognizing a subform as a
form
object, you can make it happy by using a With statement, as

Private Sub Form_Current()

On Error Resume Next

Dim lngPrimKey As Long

lngPrimKey = Me!PrimaryKey

With Me.Parent!ChildSubForm1b.Form
.Filter = "ForeignKey = " & lngPrimKey
.FilterOn = True
End With

End Sub

hth

Thanks and I've already been there, and you get the error that the
subform
does not support the filter action. (also noted in a MicroSoft KB article),
so I tried a work around.

Ed Warren.


well, i'm assuming that you want the form to open with SubForm1b
already
filtered to match the first record in SubForm1a. correct?

to do this, forget the button on the main form, and the other VBA

code
you

posted. instead, start fresh with the main form, and the two

subforms,
and

no "filtering" code anywhere. for brevity, i'll refer to the subforms
as A
and B, respectively. in the Current event of A, add the following
procedure,
as

Private Sub Form_Current()

On Error Resume Next

Me.Parent!ChildSubForm1b.Form.Filter = _
"ForeignKey = " & Me!PrimaryKey
Me.Parent!ChildSubForm1b.Form.FilterOn = True

End Sub

substitute the correct field names, of course.
in the Load event of B, add the following procedure, as

Private Sub Form_Load()

On Error Resume Next

Me.Filter = "ForeignKey = " _
& Me.Parent!ChildSubForm1b!PrimaryKey
Me.FilterOn = True

End Sub

again, substitute the correct fieldnames.
here's what happens:
1) if A loads first, the code in the Current event errs (and continues
without stopping execution) because B has not loaded yet - and so

can't
be

referenced. no problem, because when B loads next, its' code runs
successfully because A is available to be referenced. so the result is
a
correctly filtered subform.
2) if B loads first, the code in the Load event errs (and continues w/o
stopping execution) because A has not loaded yet. again, no problem,
because
when A loads next, its' code runs successfully because B is available
to
be
referenced.
either way, once the main form is open, the code on the Current event
of A
correctly filters B, each time you move between records.

hth


Setup

Form1 --> unbound (just acts as a container for the other two forms)
Subform1a -> Bound to Query1
SubForm1b - Bound to Query2

Desired:
When the onCurrent Event fires in SubForm1a -->
set the filter on subForm1b to [ForeignKey] = [SubForm1a
Primary Key]

I have it almost working by using a button on Form1 to call a sub
procedure
in SubForm1b to apply the filter.

SubForm1b

Public Sub setFilter(passedKey)
me.Filter = "[ForeignKey] = " & passedKey
me.FilterOn = true
End Sub


Form1.Button1 OnClick Event

Private Sub Button1_Click()
Forms![Form1]![Form1b].Form.SetFilter (PrimaryKey)
Me.TherapistList.Visible = True
End Sub


What I cannot make happen is a call from SubForm1a OnCurrent to the
Button1_Click() procedure.

The call from the onCurrent event in SubForm1a :

Forms("Form1").Form.Button1_Click

Generates an error ---

At this point I have a "GCE" -- General conceptual Error and cannot
figure
out how to make them work well together.

Any help appreciated

Thanks

Ed Warren