Selasa, 19 Juli 2011

[MS_AccessPros] Re: How to update combo box list options when record changes (automating F9)

 

Bingo! Thanks John, this works when I put it after the if-then codes that are currently in the On Current events.

As always, thanks so much for your help!
Heidi

--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Heidi-
>
> You were correct to subsitute the real subform control name for
> "SubformControlName". If the outer form isn't being updated except when you
> move to a new row, put that Requery in the outer form's Current event.
>
> BTW, unless Category on the outer form contains a wildcard, you should use:
>
> SELECT LUT_Output_Units.Output_Unit, LUT_Output_Units.Category_Abriv,
> LUT_Output_Units.Output_Unit
> FROM LUT_Output_Units GROUP BY LUT_Output_Units.Output_Unit,
> LUT_Output_Units.Category_Abriv, LUT_Output_Units.Output_Unit
> WHERE (((LUT_Output_Units.Category_Abriv) =
> [Forms]![FRM_Project_Review_2011]![Category]))
> ORDER BY LUT_Output_Units.Category_Abriv, LUT_Output_Units.Output_Unit;
>
> Note that I also changed HAVING to WHERE. There's no reason to make this a
> Totals query.
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of creaturenutt
> Sent: Tuesday, July 19, 2011 9:21 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: How to update combo box list options when record
> changes (automating F9)
>
> Hi John,
> I tried it with both the exact language below, and with entering the subform
> control name where listed (wasn't sure which was correct). I didn't get an
> update, and I am thinking it might be due to the fact that the information in
> the main form does not get updated - it lists information that the subforms are
> set-up to reference (so no edits will occur to data in the main forms, only the
> subforms).
>
> The subform control Row Source reads thus:
> SELECT LUT_Output_Units.Output_Unit, LUT_Output_Units.Category_Abriv,
> LUT_Output_Units.Output_Unit FROM LUT_Output_Units GROUP BY
> LUT_Output_Units.Output_Unit, LUT_Output_Units.Category_Abriv,
> LUT_Output_Units.Output_Unit HAVING (((LUT_Output_Units.Category_Abriv) Like
> forms!FRM_Project_Review_2011!Category)) ORDER BY
> LUT_Output_Units.Category_Abriv, LUT_Output_Units.Output_Unit;
>
> --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> >
> > Heidi-
> >
> > I assume the Row Source of the combo box has a reference to something like
> > [Forms]![MyOuterForm]![Category]. In the AfterUpdate event of the Category
> > control on the outer form, try putting:
> >
> > Me.SubformControlName.Form!OutputUnit.Requery
> >
> >
> > John Viescas, author
> > Microsoft Office Access 2010 Inside Out
> > Microsoft Office Access 2007 Inside Out
> > Building Microsoft Access Applications
> > Microsoft Office Access 2003 Inside Out
> > SQL Queries for Mere Mortals
> > http://www.viescas.com/
> > (Paris, France)
> >
> >
> >
> > -----Original Message-----
> > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of creaturenutt
> > Sent: Tuesday, July 19, 2011 8:54 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: [MS_AccessPros] Re: How to update combo box list options when record
> > changes (automating F9)
> >
> > Hello,
> > I am looking to automatically update values in a subform combo box based on a
> > value in the main form. I'm finding that hitting "F9" will update the values,
> > but I would like to code it to do so automatically. So far I have tried a
> > number of "requery" and "me = []" options, but I either have not found the
> > proper event for the code to make it work, or I have the syntax written
> > incorrectly.
> >
> > Here are the details:
> > The parent and child form are linked by "ID". The Subform has a combo box
> > control called "OutputUnit" which displays a list of values from a look-up
> > table. The control has two columns, the second of which I would like to equal
> a
> > value in the MAIN FORM named "AllOrgs.Category"
> >
> > I am looking for suggestions on how to write this code correctly, AND where to
> > put it. There is a good deal of code already running, so "On Current" or "On
> > Open", etc may have some code that will need to come before or after if the
> > requery/refresh code needs to go in these events.
> >
> > Thank you!
> > Heidi
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>

__._,_.___
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar