John and Clive, I tested your code and it works great! You are correct about the Junction table. I added it later after I learned about many-to-many relationships. I need to allow many employees to work on many projects. As it is, the junction table seems to do what it is supposed to do. I am able to enter identical projects (time records) for multiple employees. Do I need to do something else? Does the Junction Table need to be referenced in the Record Source of the subform like you mentioned?
I can see how the EmployeeID in tblProjects may be redundant. But if I delete it and then open the DB I get a parameter prompt. Is that field necessary for some reason?
The convoluted reference to CustomerID is caused by a few "Test" forms I was experimenting with. When I simplified the DB for the post I didn't realize there were references to those forms I should have changed. I corrected that name (and a few other object names) and put an updated DB in the folder and named it TimeTool_new(2).
Brad
--- 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
>
Tidak ada komentar:
Posting Komentar