Empty sub-form???

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

Three forms, a parent, "DirectoryItems" and two
sub-forms, "FamDirItems" and "PermissionBoxes".
When ANY of the controls in "FamDirItems" get
the focus, Sub ShiftPB (code below) is called to
change the vertical location of the 2nd sub-form
and its filter expression.

However, the resulting sub-form becomes empty.
When I run essentially the identical code in the
code-sheet of the sub-form with a series of
values, entered via InputBox, as substitutes
for Me.FamilyID the form displays according
to the value I entered and perfect in every way.

Is there something in Access that gets messed up
when one sub-form attempts to alter the filter
properties of another sub-form?

The RecordSource of the failing sub-form is
quite simple in that it only returns 7 or 8 fields
from a single table.

One very perplexing observation occurs when the
first record in the 1st sub-form, "FamDirItems" is
chosen in that the 2nd sub-form "PermissionBoxes"
displays the correct values, but in all other cases
it displays empty.

Private Sub ShiftPB()
Dim strFilter As String
'================================================
' All detectable controls (see below) call here to shift the "Permissions
' Check-Boxes" to correspond positionally with the current family record.
'================================================
Forms!DirectoryItems!PermissionBoxes.Top = Me.CurrentSectionTop

strFilter = "[FamilyID] = " & Me.FamilyID & _
" AND ([RegAs] = ""Hd of Hsehold"" OR [RegAs] = ""Spouse"") "

Forms!DirectoryItems!PermissionBoxes.Form.Filter = strFilter
Forms!DirectoryItems!PermissionBoxes.Form.FilterOn = True
Forms!DirectoryItems!PermissionBoxes.Form.Requery
End Sub
 
I also tried turning off filtering and instead setting
the RecordSource via SQL.

Forms!DirectoryItems!PermissionBoxes.Top = Me.CurrentSectionTop
strRecSrc = "Select * from Registry WHERE FamilyID = " & Me.FamilyID & _
" AND (RegAs = ""Hd of Hsehold"" OR RegAs = ""Spouse"") "
Forms!DirectoryItems!PermissionBoxes.Form.RecordSource = strRecSrc


With the suspicion that perhaps the AND clause was
being miss-understood, I limited the WHERE clause
to only the "FamilyID = " & Me.FamilyID, but still
end up with an empty sub-form.

Bill
 
I've tried everything I know how. And, since I'm not
having any difficulties effecting changes in the filters
and/or RecordSource of the first sub-form "FamDirItems"
from the parent form's code sheet, I'm beginning to believe
that one can't do what I'm attempting in effecting property
changes across sub-forms. If that's so, can I call a public
Sub in the parent from the 1st sub-form to make the changes
in the 2nd sub-form?

Bill





Bill said:
I also tried turning off filtering and instead setting
the RecordSource via SQL.

Forms!DirectoryItems!PermissionBoxes.Top = Me.CurrentSectionTop
strRecSrc = "Select * from Registry WHERE FamilyID = " & Me.FamilyID & _
" AND (RegAs = ""Hd of Hsehold"" OR RegAs = ""Spouse"") "
Forms!DirectoryItems!PermissionBoxes.Form.RecordSource = strRecSrc


With the suspicion that perhaps the AND clause was
being miss-understood, I limited the WHERE clause
to only the "FamilyID = " & Me.FamilyID, but still
end up with an empty sub-form.

Bill

Bill said:
Three forms, a parent, "DirectoryItems" and two
sub-forms, "FamDirItems" and "PermissionBoxes".
When ANY of the controls in "FamDirItems" get
the focus, Sub ShiftPB (code below) is called to
change the vertical location of the 2nd sub-form
and its filter expression.

However, the resulting sub-form becomes empty.
When I run essentially the identical code in the
code-sheet of the sub-form with a series of
values, entered via InputBox, as substitutes
for Me.FamilyID the form displays according
to the value I entered and perfect in every way.

Is there something in Access that gets messed up
when one sub-form attempts to alter the filter
properties of another sub-form?

The RecordSource of the failing sub-form is
quite simple in that it only returns 7 or 8 fields
from a single table.

One very perplexing observation occurs when the
first record in the 1st sub-form, "FamDirItems" is
chosen in that the 2nd sub-form "PermissionBoxes"
displays the correct values, but in all other cases
it displays empty.

Private Sub ShiftPB()
Dim strFilter As String
'================================================
' All detectable controls (see below) call here to shift the "Permissions
' Check-Boxes" to correspond positionally with the current family record.
'================================================
Forms!DirectoryItems!PermissionBoxes.Top = Me.CurrentSectionTop

strFilter = "[FamilyID] = " & Me.FamilyID & _
" AND ([RegAs] = ""Hd of Hsehold"" OR [RegAs] = ""Spouse"") "

Forms!DirectoryItems!PermissionBoxes.Form.Filter = strFilter
Forms!DirectoryItems!PermissionBoxes.Form.FilterOn = True
Forms!DirectoryItems!PermissionBoxes.Form.Requery
End Sub
Click to expand...
Click to expand...
 
Bill said:
Three forms, a parent, "DirectoryItems" and two
sub-forms, "FamDirItems" and "PermissionBoxes".
When ANY of the controls in "FamDirItems" get
the focus, Sub ShiftPB (code below) is called to
change the vertical location of the 2nd sub-form
and its filter expression.

However, the resulting sub-form becomes empty.
When I run essentially the identical code in the
code-sheet of the sub-form with a series of
values, entered via InputBox, as substitutes
for Me.FamilyID the form displays according
to the value I entered and perfect in every way.

Is there something in Access that gets messed up
when one sub-form attempts to alter the filter
properties of another sub-form?

The RecordSource of the failing sub-form is
quite simple in that it only returns 7 or 8 fields
from a single table.

One very perplexing observation occurs when the
first record in the 1st sub-form, "FamDirItems" is
chosen in that the 2nd sub-form "PermissionBoxes"
displays the correct values, but in all other cases
it displays empty.

Private Sub ShiftPB()
Dim strFilter As String
'================================================
' All detectable controls (see below) call here to shift the "Permissions
' Check-Boxes" to correspond positionally with the current family record.
'================================================
Forms!DirectoryItems!PermissionBoxes.Top = Me.CurrentSectionTop

strFilter = "[FamilyID] = " & Me.FamilyID & _
" AND ([RegAs] = ""Hd of Hsehold"" OR [RegAs] = ""Spouse"") "

Forms!DirectoryItems!PermissionBoxes.Form.Filter = strFilter
Forms!DirectoryItems!PermissionBoxes.Form.FilterOn = True
Forms!DirectoryItems!PermissionBoxes.Form.Requery
End Sub
Click to expand...


I'm not sure exactly what's going on, but two thoughts occur to me, not
necessarily both related to the problem you report:

1. I don't know exactly whether your attempt to reposition the
PermissionBoxes subform is going to wiork as I think you intend. I'd
suggest removing that factor from the problem for the time being by
commenting out the repositioning code, and work on getting the subform to
display the right data. If for some reason commenting out the positioning
code solves the data problem, that will give a clue to the solution of the
problem.

2. There is/was an issue at one time involving Access having some trouble
distinguishing a filter applied to a subform from the filter applied to the
main form. What happened, IIRC, was that the filter would be applied to
both forms. So let's see if we can solve the data problem by getting rid of
the filter. I can think of a couple of ways to approach this:

Approach A: Rewriting Recordsource
----------------------------------------------------
Just rewrite the subform's recordsource property each time, like this:

'----- start of suggested code -----
Private Sub ShiftPB()
'================================================
' All detectable controls (see below) call here to shift the "Permissions
' Check-Boxes" to correspond positionally with the current family record.
'================================================

Dim strRS As String

' Commented out for now
' Me.Parent!PermissionBoxes.Top = Me.CurrentSectionTop

' NOTE TO BILL: Change the table name below.
strRS =
"SELECT * FROM YourTable WHERE " & _
"FamilyID = " & Me.FamilyID & _
" AND " & _
"(RegAs = 'Hd of Hsehold' OR RegAs = 'Spouse')"

With Me.Parent!PermissionBoxes.Form
If .RecordSource <> strRS Then
.RecordSource = strRS
End If
End With

End Sub
'----- end of suggested code -----


Approach B: Use Subform Linkage
-------------------------------------------------
An alternate approach would be to use the subform linkage properties to
cause the PermissionBoxes subform to be automatically filtered by its Link
Master/Child Fields properties. You can do this by putting a hidden control
on the main form with a controlsource expression that picks up the value of
FamilyID from the FamDirItems subform. Then you use that control as the
Link Master Field (or one of the Link Master Fields) of the PermissionBoxes
subform. For example, you might create a text box named "txtLinkFamily" on
the main form, set its Visible property to False, and set its controlsource
to

=[FamDirItems]![FamilyID]

Then you set properties for the PermissionBoxes subform as follows:

Link Master Fields: txtLinkFamily
Link Child Fields: FamilyID

Note: if this subform is already linked to the main form by another field,
you'll have to include that field, too, in the main Link Master and Link
Child Fields.

With the subform linked in this manner, you don't need any other code to
make it show the records for the FamilyID that is currently active on the
FamDirItems subform. You do need to make sure that the recordsource of
PermissionBoxes already applies the RegAs criterion you had in your original
filtering code.

I like approach B better, as it requires no code.
 
Bill said:
I've tried everything I know how. And, since I'm not
having any difficulties effecting changes in the filters
and/or RecordSource of the first sub-form "FamDirItems"
from the parent form's code sheet, I'm beginning to believe
that one can't do what I'm attempting in effecting property
changes across sub-forms.
Click to expand...

No, that's not it.
 
Dirk,
I'm trying to implement "Approach B", but I can't
find anywhere in the "PermissionBoxes" properties
any reference to "Link Child" or "Link Master".

When I created the 3 forms involved, I specified
to use existing forms when I added the sub-forms.
Could I have missed something in that process
that is the crux of this whole caper?

Bill

Dirk Goldgar said:
Bill said:
Three forms, a parent, "DirectoryItems" and two
sub-forms, "FamDirItems" and "PermissionBoxes".
When ANY of the controls in "FamDirItems" get
the focus, Sub ShiftPB (code below) is called to
change the vertical location of the 2nd sub-form
and its filter expression.

However, the resulting sub-form becomes empty.
When I run essentially the identical code in the
code-sheet of the sub-form with a series of
values, entered via InputBox, as substitutes
for Me.FamilyID the form displays according
to the value I entered and perfect in every way.

Is there something in Access that gets messed up
when one sub-form attempts to alter the filter
properties of another sub-form?

The RecordSource of the failing sub-form is
quite simple in that it only returns 7 or 8 fields
from a single table.

One very perplexing observation occurs when the
first record in the 1st sub-form, "FamDirItems" is
chosen in that the 2nd sub-form "PermissionBoxes"
displays the correct values, but in all other cases
it displays empty.

Private Sub ShiftPB()
Dim strFilter As String
'================================================
' All detectable controls (see below) call here to shift the "Permissions
' Check-Boxes" to correspond positionally with the current family record.
'================================================
Forms!DirectoryItems!PermissionBoxes.Top = Me.CurrentSectionTop

strFilter = "[FamilyID] = " & Me.FamilyID & _
" AND ([RegAs] = ""Hd of Hsehold"" OR [RegAs] = ""Spouse"") "

Forms!DirectoryItems!PermissionBoxes.Form.Filter = strFilter
Forms!DirectoryItems!PermissionBoxes.Form.FilterOn = True
Forms!DirectoryItems!PermissionBoxes.Form.Requery
End Sub
Click to expand...


I'm not sure exactly what's going on, but two thoughts occur to me, not
necessarily both related to the problem you report:

1. I don't know exactly whether your attempt to reposition the
PermissionBoxes subform is going to wiork as I think you intend. I'd
suggest removing that factor from the problem for the time being by
commenting out the repositioning code, and work on getting the subform to
display the right data. If for some reason commenting out the positioning
code solves the data problem, that will give a clue to the solution of the
problem.

2. There is/was an issue at one time involving Access having some trouble
distinguishing a filter applied to a subform from the filter applied to
the main form. What happened, IIRC, was that the filter would be applied
to both forms. So let's see if we can solve the data problem by getting
rid of the filter. I can think of a couple of ways to approach this:

Approach A: Rewriting Recordsource
----------------------------------------------------
Just rewrite the subform's recordsource property each time, like this:

'----- start of suggested code -----
Private Sub ShiftPB()
'================================================
' All detectable controls (see below) call here to shift the "Permissions
' Check-Boxes" to correspond positionally with the current family record.
'================================================

Dim strRS As String

' Commented out for now
' Me.Parent!PermissionBoxes.Top = Me.CurrentSectionTop

' NOTE TO BILL: Change the table name below.
strRS =
"SELECT * FROM YourTable WHERE " & _
"FamilyID = " & Me.FamilyID & _
" AND " & _
"(RegAs = 'Hd of Hsehold' OR RegAs = 'Spouse')"

With Me.Parent!PermissionBoxes.Form
If .RecordSource <> strRS Then
.RecordSource = strRS
End If
End With

End Sub
'----- end of suggested code -----


Approach B: Use Subform Linkage
-------------------------------------------------
An alternate approach would be to use the subform linkage properties to
cause the PermissionBoxes subform to be automatically filtered by its Link
Master/Child Fields properties. You can do this by putting a hidden
control on the main form with a controlsource expression that picks up the
value of FamilyID from the FamDirItems subform. Then you use that control
as the Link Master Field (or one of the Link Master Fields) of the
PermissionBoxes subform. For example, you might create a text box named
"txtLinkFamily" on the main form, set its Visible property to False, and
set its controlsource to

=[FamDirItems]![FamilyID]

Then you set properties for the PermissionBoxes subform as follows:

Link Master Fields: txtLinkFamily
Link Child Fields: FamilyID

Note: if this subform is already linked to the main form by another field,
you'll have to include that field, too, in the main Link Master and Link
Child Fields.

With the subform linked in this manner, you don't need any other code to
make it show the records for the FamilyID that is currently active on the
FamDirItems subform. You do need to make sure that the recordsource of
PermissionBoxes already applies the RegAs criterion you had in your
original filtering code.

I like approach B better, as it requires no code.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Bill said:
Dirk,
I'm trying to implement "Approach B", but I can't
find anywhere in the "PermissionBoxes" properties
any reference to "Link Child" or "Link Master".

When I created the 3 forms involved, I specified
to use existing forms when I added the sub-forms.
Could I have missed something in that process
that is the crux of this whole caper?
Click to expand...


Nope. I suspect that it's because you're looking at the properties of the
form, not of the subform control (on the main form) that displays the form.
Here's how to get at the properties of the subform control:

1. Open the main form in design view. If you already have the form open in
design view, click some control on the main form that is *not* on the
subform, or click the little box in the upper left corner that selects the
main form itself.

2. Click *once* on the subform control.

3. Click the Properties button on the toolbar, or click menu items
View->Properties.

4. The property sheet that will now be displayed should be the property
sheet of the subform control, and its caption bar should identify it as such
by saying "Subform/Subreport: <control name>".

5. Go to the Data tab of the property sheet. You'll see properties for Link
Child Fields and Link Master Fields.
 
Nope. I suspect that it's because you're looking at the properties of the
form, not of the subform control.

EXACTLY!!!

Setting the Child/Master Links using the invisible
control (txtLinkFamily) worked perfectly. And,
after I got that working properly, I re-enabled
the code in the FamDirItems code sheet to
make the change to the Top property of
PermissionBoxes in accordance with the
current record in FamDirItems. PERFECT!!

I could not have done this without you Dirk,
so thank you for the extended time and effort
you expended in helping me with this problem.

Thanks again,
Bill Stanton
 
Bill said:
Nope. I suspect that it's because you're looking at the properties of the
form, not of the subform control.

EXACTLY!!!

Setting the Child/Master Links using the invisible
control (txtLinkFamily) worked perfectly. And,
after I got that working properly, I re-enabled
the code in the FamDirItems code sheet to
make the change to the Top property of
PermissionBoxes in accordance with the
current record in FamDirItems. PERFECT!!
Click to expand...

So I was wrong in thinking that wouldn't work as written. Live and learn.
I could not have done this without you Dirk,
so thank you for the extended time and effort
you expended in helping me with this problem.
Click to expand...

You're welcome.
 
Back
Top