Automatically Populate Field

[Guest]

Our department uses an Access database to track each employee's time. We
currently have two major tables. The first is a listing of all projects,
each of which is identified by both a Project Number and a Project Name. The
2nd table is a listing of all detailed time. This is populated through the
use of a form where an employee selects the project name from a drop down
list and enters the date and the number of hours.

Here is my problem. The employees do not know the Project Number, so that
is why they enter their time by choosing the Project Name. However, when we
run reports for management, they want to see both the Project Number and
Project Name. What is the best way to do this? Is there a way to set up the
form so that when an employee chooses a Project Name, there would be a
Project Number field that is automatically populated? Otherwise, maybe there
is a way to set up a query that pulls the time entries and matches them up
with the Project Number?

Any help would be appreciated. I would prefer to have a field in the form
that automatically shows the Number, but let me know whatever you think would
work best.

Thanks!
Candace

 

[StrayBullet via AccessMonster.com]

If the dropdown includes the Project Number, you can refer to and display it
in a textbox by creating an unbound textbox and making it's source be: =
Forms!formName.dropdownName.Column(#). Substitute the # with the column
position keeping in mind that the count starts at 0 (first column would be .
Column(0))
OnChange of the dropdown, the textbox should automatically update.
For the report (unless it relies on the form) you can add a field to the
underlying query which would display the Project Number which could then be
displayed easily on the report.

 

[Jason Lopez]

You could try (and this is only a suggestion of what I would do) changing
the field that you want to be auto-populated to a combo box. Once you do
that, create a query on the Row Source line of the Data tab of the field
properties that reads something like this:

SELECT tblProject.ID, tblProject.Name, tblProject.Number FROM tblProject
WHERE (((tblProject.Name)=Forms![CurrentForm]!ProjectName));

What this should do (at least on my forms) is allow you to end up with only
one project number. Normally I have multiple selections so my combo box
remains empty. But I am sure there is a way that you can have the first
value of the list (which should be the only value) displayed automatically
in the box.

If someone else can fill in the holes or improve what I have suggested to
make it easier, please do. This is only something that I have worked with
and what works for me.

Jason Lopez

 

[UpRider]

However, when we run reports for management, they want to see both the

Project Number and
Project Name.

Reports are independent of the forms. If you are going to run a 'report' for
management, data comes from the tables, not the forms (except for perhaps
parameters, which don't seem to be indicated here). Create a query that
included the required fields from each table that the report needs.
Link the tables on Project Number (It has to be in both tables). Save the
query, give it a name; e.g., qMgmtRpt01
Use this query as the recordsource for the report and everything's there.

HTH, UpRider