Main Form & Continuous Subform (with Requery) Problem

Dave Boyd

Hi,

I have two very similar forms each with a subform. The main form gets
a few fields from the user and passes this back to a query that the
subform is bound to. The requery is done when the user enters the
last qualifying field on the main form.

In one case this works fine, the subform shows the data the user wants
to update -- which means showing all the data put in previously (ie
showing this via the requery and the continuous option on the subform)
and standing by for input. In the other case, the requery refuses to
display in the continuous subform showing one blank record (really a
partial record). With the parent form filled in, I can manually
trigger the query and see it has the correct records.

Does anyone know of some obscure setting or restriction that I need to
tweak to get the blasted thing to work?

Thanks,

Dave

Nov 13 '05 #1

Allen Browne

I think your subform is bound to a query that contains criteria such as:
Forms!YourMainForm!SomeTextbox
and the problem is that this does not always load/stay up to date correctly.

Would it be possible to use the LinkMasterFields/LinkChildFields properties
of the subform instead? That should make life simpler if it is possible, and
it works for most things unless you are choosing a range rather a discreet
value (e.g. from start date to end date), or trying to get all values when
some of the controls in the linkage may be null.

Another alternative is to reassign the RecordSource of the subform in the
Current event of the main form. By creating a SQL string that concatenates
any literal values into the string, you can handle ranges, ignore nulls, and
get the exact result you want. You may need to check that Access does not
become overhelpful and decide to assign values to the
LinkMasterFields/LinkChildFields based on the new RecordSource. And you also
have to be careful to avoid cyclic dependencies, such as something in the
main form that is dependent on something in the subform. Conditional
formatting can also trigger this.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dave Boyd" <da**********@boeing.com> wrote in message
news:e7*************************@posting.google.co m...

I have two very similar forms each with a subform. The main form gets
a few fields from the user and passes this back to a query that the
subform is bound to. The requery is done when the user enters the
last qualifying field on the main form.

In one case this works fine, the subform shows the data the user wants
to update -- which means showing all the data put in previously (ie
showing this via the requery and the continuous option on the subform)
and standing by for input. In the other case, the requery refuses to
display in the continuous subform showing one blank record (really a
partial record). With the parent form filled in, I can manually
trigger the query and see it has the correct records.

Does anyone know of some obscure setting or restriction that I need to
tweak to get the blasted thing to work?

Thanks,

Dave

Nov 13 '05 #2

Dave Boyd

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<41**********************@per-qv1-newsreader-01.iinet.net.au>...

I think your subform is bound to a query that contains criteria such as:
Forms!YourMainForm!SomeTextbox
and the problem is that this does not always load/stay up to date correctly.

Would it be possible to use the LinkMasterFields/LinkChildFields properties
of the subform instead? That should make life simpler if it is possible, and
it works for most things unless you are choosing a range rather a discreet
value (e.g. from start date to end date), or trying to get all values when
some of the controls in the linkage may be null.

Another alternative is to reassign the RecordSource of the subform in the
Current event of the main form. By creating a SQL string that concatenates
any literal values into the string, you can handle ranges, ignore nulls, and
get the exact result you want. You may need to check that Access does not
become overhelpful and decide to assign values to the
LinkMasterFields/LinkChildFields based on the new RecordSource. And you also
have to be careful to avoid cyclic dependencies, such as something in the
main form that is dependent on something in the subform. Conditional
formatting can also trigger this.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dave Boyd" <da**********@boeing.com> wrote in message
news:e7*************************@posting.google.co m...

I have two very similar forms each with a subform. The main form gets
a few fields from the user and passes this back to a query that the
subform is bound to. The requery is done when the user enters the
last qualifying field on the main form.

In one case this works fine, the subform shows the data the user wants
to update -- which means showing all the data put in previously (ie
showing this via the requery and the continuous option on the subform)
and standing by for input. In the other case, the requery refuses to
display in the continuous subform showing one blank record (really a
partial record). With the parent form filled in, I can manually
trigger the query and see it has the correct records.

Does anyone know of some obscure setting or restriction that I need to
tweak to get the blasted thing to work?

Thanks,

Dave

Allen,

Yes, you are correct and I believe the criteria you mention would
allow me to use form/subform linked fields, but it's not obvious what
I should do. (I'm guessing the original form (that works) was
generated by an Access wizard -- possibly the former database
architect, but not likely -- and I'm trying to manually duplicate that
as an inexperienced form builder.)

On the main form I have a set of master fields. I want the subform to
display the subset of records equivalent to the select query where the
two fixed master fields are chosen by the user on the main form.

How would the linked fields approach be setup? I tried to eliminate
the query by linking both forms to the table and then linking the two
fields in the main form to the subform. The two fields don't actually
appear in the subform. There are actually row source queries on combo
boxes for some of the subform fields since they do double duty as
display slots for existing records and entry of new data for the next
user defined record. According to what I understand, when I enter the
linked master fields the form should display all related records?
This doesn't seem to work so I've still got some screwed up somewhere.

Dave

Nov 13 '05 #3

Allen Browne

Hi Dave

I'm not really clear here - esp. about the combos in the subform. If you are
trying to use the non-bound column of the combo in the LinkChildFields of
the subform control, it might be better to a use a query as the RecordSource
of the subform, and include the combo's table so that the field you need to
link on is in the RecordSource.

If you need an example of how subforms work, open the Northwind sample
database, and look at the Customer Orders form. The first subform - Customer
Orders Subform1 - has CustomerID in both the LinkMasterFields and
LinkChildFields, so the subform shows the records matching the CustomerID in
the main form. The 2nd subform - Customer Orders Subform2 has its
LinkChildFields set to OrderID, and its LinkMasterFields set to
[Customer Orders Subform1].Form![OrderID]
That means that its records will match the OrderID in the other subform.

Hopefully that example demonstrates what needs to go into the
LinkChildFields/LinkMasterFields in your example. If you can get the fields
into the source query, it is simplest and most stable.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dave Boyd" <da**********@boeing.com> wrote in message
news:e7*************************@posting.google.co m...

I think your subform is bound to a query that contains criteria such as:
Forms!YourMainForm!SomeTextbox
and the problem is that this does not always load/stay up to date
correctly.

Would it be possible to use the LinkMasterFields/LinkChildFields
properties
of the subform instead? That should make life simpler if it is possible,
and
it works for most things unless you are choosing a range rather a
discreet
value (e.g. from start date to end date), or trying to get all values
when
some of the controls in the linkage may be null.

Another alternative is to reassign the RecordSource of the subform in the
Current event of the main form. By creating a SQL string that
concatenates
any literal values into the string, you can handle ranges, ignore nulls,
and
get the exact result you want. You may need to check that Access does not
become overhelpful and decide to assign values to the
LinkMasterFields/LinkChildFields based on the new RecordSource. And you
also
have to be careful to avoid cyclic dependencies, such as something in the
main form that is dependent on something in the subform. Conditional
formatting can also trigger this.
"Dave Boyd" <da**********@boeing.com> wrote in message
news:e7*************************@posting.google.co m...

>
> I have two very similar forms each with a subform. The main form gets
> a few fields from the user and passes this back to a query that the
> subform is bound to. The requery is done when the user enters the
> last qualifying field on the main form.
>
> In one case this works fine, the subform shows the data the user wants
> to update -- which means showing all the data put in previously (ie
> showing this via the requery and the continuous option on the subform)
> and standing by for input. In the other case, the requery refuses to
> display in the continuous subform showing one blank record (really a
> partial record). With the parent form filled in, I can manually
> trigger the query and see it has the correct records.
>
> Does anyone know of some obscure setting or restriction that I need to
> tweak to get the blasted thing to work?
>
> Thanks,
>
> Dave

Allen,

Yes, you are correct and I believe the criteria you mention would
allow me to use form/subform linked fields, but it's not obvious what
I should do. (I'm guessing the original form (that works) was
generated by an Access wizard -- possibly the former database
architect, but not likely -- and I'm trying to manually duplicate that
as an inexperienced form builder.)

On the main form I have a set of master fields. I want the subform to
display the subset of records equivalent to the select query where the
two fixed master fields are chosen by the user on the main form.

How would the linked fields approach be setup? I tried to eliminate
the query by linking both forms to the table and then linking the two
fields in the main form to the subform. The two fields don't actually
appear in the subform. There are actually row source queries on combo
boxes for some of the subform fields since they do double duty as
display slots for existing records and entry of new data for the next
user defined record. According to what I understand, when I enter the
linked master fields the form should display all related records?
This doesn't seem to work so I've still got some screwed up somewhere.

Dave

Nov 13 '05 #4

Dave Boyd

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<41**********************@per-qv1-newsreader-01.iinet.net.au>...

Hi Dave

I'm not really clear here - esp. about the combos in the subform. If you are
trying to use the non-bound column of the combo in the LinkChildFields of
the subform control, it might be better to a use a query as the RecordSource
of the subform, and include the combo's table so that the field you need to
link on is in the RecordSource.

If you need an example of how subforms work, open the Northwind sample
database, and look at the Customer Orders form. The first subform - Customer
Orders Subform1 - has CustomerID in both the LinkMasterFields and
LinkChildFields, so the subform shows the records matching the CustomerID in
the main form. The 2nd subform - Customer Orders Subform2 has its
LinkChildFields set to OrderID, and its LinkMasterFields set to
[Customer Orders Subform1].Form![OrderID]
That means that its records will match the OrderID in the other subform.

Hopefully that example demonstrates what needs to go into the
LinkChildFields/LinkMasterFields in your example. If you can get the fields
into the source query, it is simplest and most stable.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dave Boyd" <da**********@boeing.com> wrote in message
news:e7*************************@posting.google.co m...

I think your subform is bound to a query that contains criteria such as:
Forms!YourMainForm!SomeTextbox
and the problem is that this does not always load/stay up to date
correctly.

Would it be possible to use the LinkMasterFields/LinkChildFields
properties
of the subform instead? That should make life simpler if it is possible,
and
it works for most things unless you are choosing a range rather a
discreet
value (e.g. from start date to end date), or trying to get all values
when
some of the controls in the linkage may be null.

Another alternative is to reassign the RecordSource of the subform in the
Current event of the main form. By creating a SQL string that
concatenates
any literal values into the string, you can handle ranges, ignore nulls,
and
get the exact result you want. You may need to check that Access does not
become overhelpful and decide to assign values to the
LinkMasterFields/LinkChildFields based on the new RecordSource. And you
also
have to be careful to avoid cyclic dependencies, such as something in the
main form that is dependent on something in the subform. Conditional
formatting can also trigger this.
"Dave Boyd" <da**********@boeing.com> wrote in message
news:e7*************************@posting.google.co m...
>
> I have two very similar forms each with a subform. The main form gets
> a few fields from the user and passes this back to a query that the
> subform is bound to. The requery is done when the user enters the
> last qualifying field on the main form.
>
> In one case this works fine, the subform shows the data the user wants
> to update -- which means showing all the data put in previously (ie
> showing this via the requery and the continuous option on the subform)
> and standing by for input. In the other case, the requery refuses to
> display in the continuous subform showing one blank record (really a
> partial record). With the parent form filled in, I can manually
> trigger the query and see it has the correct records.
>
> Does anyone know of some obscure setting or restriction that I need to
> tweak to get the blasted thing to work?
>
> Thanks,
>
> Dave

Allen,

Yes, you are correct and I believe the criteria you mention would
allow me to use form/subform linked fields, but it's not obvious what
I should do. (I'm guessing the original form (that works) was
generated by an Access wizard -- possibly the former database
architect, but not likely -- and I'm trying to manually duplicate that
as an inexperienced form builder.)

On the main form I have a set of master fields. I want the subform to
display the subset of records equivalent to the select query where the
two fixed master fields are chosen by the user on the main form.

How would the linked fields approach be setup? I tried to eliminate
the query by linking both forms to the table and then linking the two
fields in the main form to the subform. The two fields don't actually
appear in the subform. There are actually row source queries on combo
boxes for some of the subform fields since they do double duty as
display slots for existing records and entry of new data for the next
user defined record. According to what I understand, when I enter the
linked master fields the form should display all related records?
This doesn't seem to work so I've still got some screwed up somewhere.

Dave

Allen,

Thanks for your patience. I tried your second solution of changing
the recordsource in the handling code and found it easiest to use the
AfterUpdate? event for the "last" field on the main form. This is
where the requery that didn't work was anyway. (Using the current
event got me in a world of brown goo that fired at the wrong time or
complained about not being able to change focus etc.)

The trick (for me) was to find the recordsource property for the
subform to allowing setting it in the running code rather than at
subform design time. The subform path you might expect leads to the
main form/subform link property sheet and has no recordsource (for GUI
beginners this is the bizarre sequence where clicking on the subform
gives this linkage property set and clicking again --with a resulting
black square-- gives the subform property set). However, the
CodeContextObject.[subform name].Form.RecordSource path allowed me to
move in the SQL query to the sweet spot and CodeContextObject.[subform
name].requery now works.

You were right on that the query wasn't up to date, but just why still
eludes me. Thanks for your help!

Dave

Nov 13 '05 #5