Brad-
You need tblProjects to define the project for the customer - the function,
ProductID, etc. Perhaps it should have a due date and a description. All the
time registration fields belong in tblProjectJunction - maybe rename that
tblEmployeeProjectTime. That might "dummy" project rows for Break, Holiday, and
Vacation.
As far as project details, you could do this:
tblProjects: ProjectID, CustomerID, DueDate, Description
tblProjectDetails: ProjectID, FunctionID (Analysis, Design, Testing),
DatePerformed, etc.
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: Friday, October 28, 2011 9:39 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Synchronized Combo Boxes on a Subform
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
>
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar