I have a 1:M:M relationship between customerrder headerrder details, with
a main form with customer name, then a subform A for order header, and a
subform B for details where I have a combo box of product possibilities. This
part works OK.
I now want to customize this combo box in B according to a choice in subform
A. Currently, I have the rowsource for subform B set to a query which has a
criteria Forms!..OrderNo which links A and B and pulls product name from a
lookup table. This doesn't work, so any thoughts please.

--
Doug F.

Use the AfterUpdate event procedure of the first combo to set the RowSource
of the 2nd one.

Details in:
Limit content of combo/list boxes
at:
http://www.mvps.org/access/forms/frm0028.htm

--
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.

"Doug F." <(E-Mail Removed)> wrote in message
news:1A0FB50A-077A-4BB2-A291-(E-Mail Removed)...
>I have a 1:M:M relationship between customerrder headerrder details,
>with
> a main form with customer name, then a subform A for order header, and a
> subform B for details where I have a combo box of product possibilities.
> This
> part works OK.
> I now want to customize this combo box in B according to a choice in
> subform
> A. Currently, I have the rowsource for subform B set to a query which has
> a
> criteria Forms!..OrderNo which links A and B and pulls product name from a
> lookup table. This doesn't work, so any thoughts please.
>
> --
> Doug F.

Hi Doug,

here is an example you can pattern after

limit the combobox to specific records when it gets the focus, show all
records when you leave it

on the gotFocus event of the combobox, assign this:

=SetRowSource(true)


on the lostFocus event of the combobox, assign this:

=SetRowSource(false)


put this code behind the form/subform with the combobox -- and compile
it before testing

'~~~~~~~~~~~

private function SetRowSource( _
pBooCriteria as boolean)

on error goto Err_proc

dim strSQL as string

strSQL = "SELECT SomeID, SomeName" _
& " FROM Tablename"

if pBooCriteria then

strSQL = strSQL _
& " WHERE (Active_YN = true)"

end if

strSQL = strSQL & "ORDER BY SomeName;"

debug.print strSQL

me.combobox_controlname.RowSource = strSQL
me.combobox_controlname.Requery

Exit_proc:
exit function

Err_proc:
msgbox err.description,, _
"ERROR " & err.number & " SetRowSource"
'press F8 to step through code and fix problem
'comment next line after debugged
Stop: Resume

resume Exit_proc
End function

'~~~~~~~~

** debug.print ***

debug.print strSQL

--> this prints a copy of the SQL statement to the debug window (CTRL-G)

After you execute your code, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement

If the SQL statement has an error

1. Make a new query (design view)

2. choose View, SQL from the menu
(or SQL from the toolbar, first icon)

3. cut the SQL statement from the debug window
(select, CTRL-X)

4. paste into the SQL window of the Query
(CTRL-V)

5. run ! from the SQL window
-- Access will tell you where the problem is in the SQL

'~~~~~~~~~~~~~~

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Doug F. wrote:
> I have a 1:M:M relationship between customerrder headerrder details, with
> a main form with customer name, then a subform A for order header, and a
> subform B for details where I have a combo box of product possibilities. This
> part works OK.
> I now want to customize this combo box in B according to a choice in subform
> A. Currently, I have the rowsource for subform B set to a query which has a
> criteria Forms!..OrderNo which links A and B and pulls product name from a
> lookup table. This doesn't work, so any thoughts please.
>

Thanks Allen.
It's now working, I put a requery (Me.Combo7.Requery) in the Form_Current in
subform B as the combo in subform A is not always updated and hence no event,
eg if one is only browsing.

--
Doug F.


"Allen Browne" wrote:

> Use the AfterUpdate event procedure of the first combo to set the RowSource
> of the 2nd one.
>
> Details in:
> Limit content of combo/list boxes
> at:
> http://www.mvps.org/access/forms/frm0028.htm
>
> --
> 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.
>
> "Doug F." <(E-Mail Removed)> wrote in message
> news:1A0FB50A-077A-4BB2-A291-(E-Mail Removed)...
> >I have a 1:M:M relationship between customerrder headerrder details,
> >with
> > a main form with customer name, then a subform A for order header, and a
> > subform B for details where I have a combo box of product possibilities.
> > This
> > part works OK.
> > I now want to customize this combo box in B according to a choice in
> > subform
> > A. Currently, I have the rowsource for subform B set to a query which has
> > a
> > criteria Forms!..OrderNo which links A and B and pulls product name from a
> > lookup table. This doesn't work, so any thoughts please.
> >
> > --
> > Doug F.
>
>
>

Thanks Crystal. I learned.

--
Doug F.


"strive4peace" wrote:

> Hi Doug,
>
> here is an example you can pattern after
>
> limit the combobox to specific records when it gets the focus, show all
> records when you leave it
>
> on the gotFocus event of the combobox, assign this:
>
> =SetRowSource(true)
>
>
> on the lostFocus event of the combobox, assign this:
>
> =SetRowSource(false)
>
>
> put this code behind the form/subform with the combobox -- and compile
> it before testing
>
> '~~~~~~~~~~~
>
> private function SetRowSource( _
> pBooCriteria as boolean)
>
> on error goto Err_proc
>
> dim strSQL as string
>
> strSQL = "SELECT SomeID, SomeName" _
> & " FROM Tablename"
>
> if pBooCriteria then
>
> strSQL = strSQL _
> & " WHERE (Active_YN = true)"
>
> end if
>
> strSQL = strSQL & "ORDER BY SomeName;"
>
> debug.print strSQL
>
> me.combobox_controlname.RowSource = strSQL
> me.combobox_controlname.Requery
>
> Exit_proc:
> exit function
>
> Err_proc:
> msgbox err.description,, _
> "ERROR " & err.number & " SetRowSource"
> 'press F8 to step through code and fix problem
> 'comment next line after debugged
> Stop: Resume
>
> resume Exit_proc
> End function
>
> '~~~~~~~~
>
> ** debug.print ***
>
> debug.print strSQL
>
> --> this prints a copy of the SQL statement to the debug window (CTRL-G)
>
> After you execute your code, open the Debug window
> CTRL-G to Goto the debuG window -- look at the SQL statement
>
> If the SQL statement has an error
>
> 1. Make a new query (design view)
>
> 2. choose View, SQL from the menu
> (or SQL from the toolbar, first icon)
>
> 3. cut the SQL statement from the debug window
> (select, CTRL-X)
>
> 4. paste into the SQL window of the Query
> (CTRL-V)
>
> 5. run ! from the SQL window
> -- Access will tell you where the problem is in the SQL
>
> '~~~~~~~~~~~~~~
>
> Warm Regards,
> Crystal
> *
> (: have an awesome day
> *
> MVP Access
> Remote Programming and Training
> strive4peace2006 at yahoo.com
> *
>
>
>
> Doug F. wrote:
> > I have a 1:M:M relationship between customerrder headerrder details, with
> > a main form with customer name, then a subform A for order header, and a
> > subform B for details where I have a combo box of product possibilities. This
> > part works OK.
> > I now want to customize this combo box in B according to a choice in subform
> > A. Currently, I have the rowsource for subform B set to a query which has a
> > criteria Forms!..OrderNo which links A and B and pulls product name from a
> > lookup table. This doesn't work, so any thoughts please.
> >
>

you're welcome, Doug happy to help


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Doug F. wrote:
> Thanks Crystal. I learned.
>