customized combo box

[Doug F.]

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.

 

[Allen Browne]

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

 

[strive4peace]

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

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

Thanks Crystal. I learned.

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

 

[strive4peace]

you're welcome, Doug happy to help


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