Strange Subform behavior... can someone help?

[Ed_Landau]

I have a form with a subform. The 2 are not related based on the primary key
of the parent form. From the main form, I have a combobox who's event calls a
public subroutine in the subform... and that subroutine sets the subform
filter.

Works beautifully !

The subform dutifully displays appropriate records as I change the selection
in the combobox.


The problem is that if I click anywhere on the subform, things go haywire. If
I now change the combobox again (after having clicked in the subform), NO
RECORDS get displayed in the subform unless I select the same selection it
was at when I clicked in the subform.

I tried to use me.recordsource instead of me.filter… and again, it worked
beautifully until I click anywhere in the subform.

I tried setting the Allow Edits in the subform to False, thinking this had
something to do with the fact that the form was “dirty†but that made no
difference.

Anyone have any clues????

Thanks
-Ed

 

[Scott Lichtenberg]

Ed,

I'm not sure what's causing your problem, so I'm not sure this will help,
but when I have this type of form, I generally manipulate the subforms
recordset from the parent, rather than by calling a function within the
subform. Try moving the code into your parent form. You access the subform
with the following syntax.

Me!MySubform.Form.Recordsource = "SELECT * FROM MyTable WHERE MyField =
" & Me!cboMyComboBox

or

Me!MySubform.Form.Filter = "MyField = " & Me!cboMyComboBox
Me!MySubform.Form.FilterOn = True

Hope this helps

 

[Ed_Landau via AccessMonster.com]

Hi Scott: Great idea. I did this (and actually, I was not able to access
the subform with me.subform.form but with form_subform.recordsource).

Anyway... I know it worked because the recordsource did change (like it did
before) and the proper records are being displayed in the subform UNTIL I
click in the subform... so again, even if I change the subform's recordsource
in the parent, NO RECORDS get displayed in the subform once I click in the
subform just once. Only the records which were displayed at the time of
the click can ever be displayed.

STRANGE !!

But that was a great try .

-Ed


Scott Lichtenberg wrote:
... Try moving the code into your parent form. ...

 

[Scott Lichtenberg]

Hi Ed,

What you are trying to do sounds like a very normal, simple thing that we do
all the time. Once you've set the recordsource property of the subform, the
records should stay there unless something else is operating on them. Here
are some things to look at.

Is there any code in the subform, especially in the OnOpen, OnLoad, and
OnCurrent events, that might be requerying or resetting the recordsource?

Is the form set for data entry? This might move you to a new record when go
into the subform.

Is there anything in the Parent/Child relationship fields of the subform
control on the main form. When you fill in the source object, Access will
fill in the relationship if it thinks there is one. Since your forms really
aren't linked, having these relationships might cause Access to go to a
"null" record when you go into the subform.

Let me know if any of these pan out for you.
Scott