ACCESS FEED: RE: [MS_AccessPros] Help Please - Linked Combo Boxes on a Datasheet sub form

Kamis, 20 Juni 2013

RE: [MS_AccessPros] Help Please - Linked Combo Boxes on a Datasheet sub form

Grant-

See the file I just uploaded.

For starters, you can never make this work in a Datasheet subform. If you
get the second combo filtered on the first one, the current list value will
show ONLY on the current row and any rows that are in the same stock
category. The reason is there's really only one copy of each control even
though you can see multiple rows.

The database I uploaded uses a Continuous Form instead and a little trick I
learned a long time ago. Here's what I did:

1) Fixed the Row Source and settings of the StockTypeID combo to display the
type name, not just the number.
2) Fixed the Row Source and settings of the StockID combo to display the
stock name and added a filter pointing to StockTypeID.
3) Added code to Requery StockID whenever StockTypeID is updated.
4) Added code to Requery StockID when moving to a new row - so the row list
matches the current row's StockTypeID.
5) Changed the Record Source of the subform to include the Stock field from
tbl_Stock.
6) Added a text box overlaid on the combo StockID so that all rows always
show the right name even when the StockTypeID currently being used to filter
doesn't match.
7) Removed the overlay text box from the tab order and set the Enter event
to move the focus to the StockID combo behind should the user click in it.

Have fun...

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
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 beinkeboy
Sent: Thursday, June 20, 2013 7:24 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Help Please - Linked Combo Boxes on a Datasheet sub
form

Hi,

I have two combo boxes on a datasheet sub form that I can not get to work.

I want the cboStock to update to show the relevant Stock when the
cboStockType is changed.

I can get it to half work but when I move to the next row in the datasheet
it goes all wrong.

I have stripped the database of all my bad efforts and excess tables and
uploaded it to Files\2 Assistance\ComboBoxes on Subform.mdb.

If you load the frmEvent you will see what I am trying to do.

Any assistance would be greatly appreciated.

Thank you

Grant

------------------------------------

Yahoo! Groups Links