ACCESS FEED: RE: [MS_AccessPros] Synchronized Combo Boxes on a Subform

Rabu, 26 Oktober 2011

RE: [MS_AccessPros] Synchronized Combo Boxes on a Subform

Clive-

That would make sense if he were using the junction table in the Record Source
of the subform, but he's not.

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 Clive
Sent: Wednesday, October 26, 2011 1:24 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Synchronized Combo Boxes on a Subform

Hi John, Brad,

I think the EmployeeID in the tblProject is redundant
and that Brad added the tblProjectJunction later so
that he could have many Employees on Many Projects.

Brad, is that what what you wanted?

Regards, Clive.

--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Brad-
>
> First, I don't understand the purpose of the tblProjectJunction table. You
have
> EmployeeID in tblProjects, so it would appear that you have a simple one to
many
> relationship between employees and projects. (An employee can have multiple
> projects, but a project is assigned to only one employee.)
>
> To solve your problem, you need to do:
>
> 1) Change the Row Source of Combo22 on the subform to:
> SELECT tblProduct.ProductID, tblProduct.Product FROM tblProduct WHERE
> [CustomerID] = [Forms]![frmDataEntry]![frmDataEntryTest
> subform].Form![CustomerID] ORDER BY [Product];
>
> 2) Add code to the Current event of the subform:
>
> Private Sub Form_Current()
> Me.Combo22.Requery
> End Sub
>
> 3) Add code to the AfterUpdate event of the CustomerID combo box:
>
> Private Sub dboCustomer_AfterUpdate()
> Me.Combo22.Requery
> End Sub
>
> I suspect what has been tripping you up is the convoluted reference to the
> CustomerID on the subform that you need to filter the product list. Here's a
> breakdown:
>
> [Forms] -- the collection of open forms. Note that the subform is NOT in
this
> collection!
> [frmDataEntry] -- the parent form that is open.
> [frmDataEntryTest subform] -- the name of the subform control on the parent
> form. Note that in your case, the name of the control does NOT match the name
> of the subform inside.
> [Form] -- the form object inside the subform control.
> [CustomerID] -- the name of the control on the subform that contains the
filter
> value.
>
> You need a requery of the filtered combo box to make sure the Row Source is up
> to date when you move to a different record that might have a different
customer
> ID. You also need to requery the combo box when the user picks a different
> customer ID.
>
> 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 Bradley
> Sent: Wednesday, October 26, 2011 4:25 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Synchronized Combo Boxes on a Subform
>
> Hello, I am creating a time tracking database and am having trouble
> synchronizing two combo boxes on a subform. I have tried several suggestions I
> found on the Web, but with no luck. To make things easy to understand, I
placed
> an over-simplified version of my DB (with just the problem form and subform)
in
> the PTS Time Tool folder and named it TimeTool_new. The only combo boxes that
> need to synchronize are the Customer and Product. Previously, the problem was
> caused by flawed tables and relationships. I followed the suggestions from an
> earlier post and revised them. I don't think that is causing the problem now,
> but please let me know if they are not correct. My experience level is
beginner
> and I use access 2007. I appreciate any help you can give me.
>
> Thanks,
>
> Brad
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>

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

Yahoo! Groups Links