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

Hi John,

Thanks for your help. It is fantastic.

The fact that what I was trying to do could never work makes me feel a bit better after trying it for some time.

I had seen on some sites people talking about the text box trick and to see it implemented so well by you is great. It works perfectly.

Now all I have to do is go through what you have done and try and learn from it.

I thank you for sharing both your time and expertise.

Grant Beinke

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> 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
>