John, now that I think about it, I should have called "Projects" something different, like "Time Records" or "Activities." The concept is very simple: an employee enters several time records (projects) every day based on what they do that day. The total minutes for all the time records for a day will equal 480 minutes (8 hours). Each project record consists of a Function, Customer, Product, Date, Quantity, Minutes, and Comment. Some time records will have all of those fields, and some will not. For instance, a project record for a break would only need "Break" selected from the Function dropdown and the minutes entered in the Minutes field (no need for a Customer or Product).
Perhaps you are right in that the linking table also needs to have the ProjectDate, Quantity, Minutes, and Comment fields. I have noticed that other project or time tracker databases have a "Project Details" table. I don't quite understand what fields are needed in such a table, or what it does other than allow for the many-to-many relationship between employees and projects (time records). Please let me know your thoughts on how my tables should be changed. Thanks.
Brad
--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Brad-
>
> I changed your subform so that it is editing the linking table and removed
> EmployeeID from tblProjects. Should Quantity and Minutes and Comment be in the
> linking table? That is, are these fields related to what a particular employee
> does on the project, or are they totals for the project as a whole?
>
> File is in your folder in Assistance Needed.
>
> 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: Thursday, October 27, 2011 11:12 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Synchronized Combo Boxes on a Subform
>
>
>
>
> 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
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
Tidak ada komentar:
Posting Komentar