"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message news:<416b400c$0$1289$5a62ac22@per-qv1-newsreader-01.iinet.net.au>...[color=blue]
> 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" <david.a.boyd@boeing.com> wrote in message
> news:e73515e.0410110938.79642461@posting.google.co m...[color=green][color=darkred]
> >> 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" <david.a.boyd@boeing.com> wrote in message
> >> news:e73515e.0410081242.15477d53@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[/color]
> >
> > 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[/color][/color]
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
