information from one form to another

[bobc721]

Ok, I am not sure why this is not soaking in, but here's the deal

I have two tables, the first is job_list and has the following fields: job
number, job name, client name, project manager. This table to the end user is
a static table and is linked from another corporate database, which will have
more tables.

The second table is timesheet and has the following fields: job number, task
code, job name, client name, project manager, hours, work description. The
second table needs to be populated by the first when the employee, using a
combo box, or list box to select a correct job number, job name, or client
name.

Graham had given me some information regarding this scenario, but I still
don't get it... how does access know to move the information from the static
table (job_list) to the timesheet? Do I need to make the form go to a new
entry before doing this? Basically all that I am wanting this form to do is
get the user to select a current job (table is about 165 entries, including
admin time) and then the user will have to put in a task code, hours, and a
work description...

Please put your response in layman terms... I am relearning databases after
a very long layoff...

 

[Jeff Boyce]

Bob

It doesn't. Since you are using Access, a relational database, you don't
need multiple copies of the same data (e.g., job name, client name, project
manager) in multiple tables. In fact, duplicating your data across tables
(by the way, Access tables store data, Access forms display it -- you
aren't, technically, trying to move data from one form to another, but from
one table to another ... and DON'T!).

Instead, in your tblTimesheet, you only need to store the primary key of the
tblJobList, as a pointer back to which job (it gets called a foreign key in
tblTimesheet, since it points back...).

Using forms, make your tblJobList the source behind a main form. Make your
tblTimesheet the source behind a second form. In Design mode on the main
form, pull in frmTimesheet as a subform. Be sure to follow the wizard and
connect the two forms by their common field (i.e., the primary key of
tblJobList ... I assume this is JobNumber).

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[bobc721]

Thanks Jeff, things are a little clearer now. I started with the timesheet
and was trying to add back in the joblist. I had two tables with duplicate
info and because these tables were in separate files I thought I would have
to access the information in one and copy it to the other. Some of the
timesheets are dated back nearly 5 years so it looks like I am going to have
to change my plan of attack.

So the way I understand it now.. the primary key will be the job number,
that is the unique key that will drive the timesheet. the other information
will automatically port from the job list as long as there is a field that is
available and is pointing back to the job list.

I'll make an attempt at what you just posted and see if this remedies where
I am trying to go.. I really appreciate the help.

Bob

 

[bobc721]

Jeff,

I was able to make the subform and this isn't exactly what I am needing.
While it does away with the duplication of fields, it doesn't fit the
exercise.

The employee timesheet is a separate record, we are not planning to
including it in the main database at the present time. What I am attempting
to do is get a table entry that will verify the job number, name, client, and
pm. (pull from the job_list and put it in timesheet), the employee can then
enter the date, hours, task code, and work description.

the main form (job_list) and have a subform linked would be a great idea if
it populated a table that would have the information completely filled out.
ie, job no, job name, client, pm, date, hours, task code, work description.
the way I did the subform it was not creating a new table or populating the
existing time table, but creating secondary records (don't have a clue where
they were being sent to) for each individual job... and that's not what I am
looking for.

this has been fun digging, but at the same time frustrating because I didn't
think that Access would be that difficult for a seemingly simple task.