form vs query vs table

[bobc721]

I am in the middle of a rather simple task.. or at least that is what I
thought.

The situation is that I am building timesheets based upon an old design that
is very basic. I am trying to update the information so that the employees
when inserting their time into the table (yeah using the table instead of
form, it's easier to copy an old entry and bring it forward than it is do
retype the whole thing). So now I have ported the project (job) list into a
table and want to create a form that will locate active jobs and
automatically put the information into the form and it can be based either on
job number, job name, or client name. the first two are pretty much a
no-brainer.. the last may have more than 3 or 4 jobs open at any one time.

So the question is... can I build this functionality into a form or do I
have to write some code to sit behind the scenes and allow a lookup and then
paste the information automatically into the form? Can I do this with list
and/or combo boxes?

As a final step, I want the program to verify that the correct code was
entered, or can I limit the input field to just what is in the project
table.. nothing more.. nothing less...

by the way.. right now the employee time sheet is a separate database and I
am linking the main database to the employee timesheets and vice versa....
this will keep confidential information away from prying eyes...

 

[Graham Mandeno]

Hi Bob

Put simply:
Tables are for storing stuff
Queries are for selecting and collating stuff
Forms are for viewing and editing stuff
Reports are for printing stuff

There is nothing that you can do in a table with regards to editing data
that you cannot do (usually with less hassle) in a form. And there is a
whole world of functionality that is available to you in a form that is
impossible in a table.

My advice is: never allow users access to tables or queries - they are the
"behind the scenes" stuff.

You haven't given any information about the structure of your tables, but
I'll assume you have a table for Clients and another for Jobs. These should
be related on a one-to-many basis. Let's assume that the primary key of the
Clients table is an autonumber field named "ClientID". The ONLY field in
the Jobs table that refers to a client should be a "foreign key" (a long
integer field that holds the ClientID value of the related Clients record).
Let's call this field "JobClient".

If you want a list of clients in order of name, you can create a query:
Select ClientID, ClientName from Clients order by ClientName;

If you use this query as the RowSource of a combo box on a form, you then
have a way to select a particular client's ClientID by selecting their name.
Let's call this combo box "cboSelectClient".

If you want a list of jobs that are assigned to the selected client, you can
either filter a form based on your Jobs table:
Me.Filter = "JobClient=" & cboSelectClient
Me.FilterOn = True

or you can create another query string and use that for the RowSource of
another combo box or listbox:
lstClientJobs.RowSource = "Select JobNumber, JobName from Jobs " _
& "where JobClient=" & cboSelectClient

Hope this gets you going. Shout back if you need more help

 

[bobc721]

Thanks Graham,

what I have now is something similar to what you just posted.

I have tables for contacts, jobs, clients, employees, and timesheets. The
timesheets are individual databases that employees access to put in their
time. My goal for the short term is to take the timesheets and limit the
input for available job numbers into a current job list, this is based upon
the job list table. Eventually all the jobs will be put into one database
whether active, inactive, or closed. That will be a bear for a later date...

the main thing that you answered is where I want the end user to input
pertinent information, and that is a form. Now I can take all the reference
material that I have obtained and start digging to get the parts that I want
to be available to the employee.

thanks again for your response

--
bob

Hi Bob

Put simply:
Tables are for storing stuff
Queries are for selecting and collating stuff
Forms are for viewing and editing stuff
Reports are for printing stuff

There is nothing that you can do in a table with regards to editing data
that you cannot do (usually with less hassle) in a form. And there is a
whole world of functionality that is available to you in a form that is
impossible in a table.

My advice is: never allow users access to tables or queries - they are the
"behind the scenes" stuff.

You haven't given any information about the structure of your tables, but
I'll assume you have a table for Clients and another for Jobs. These should
be related on a one-to-many basis. Let's assume that the primary key of the
Clients table is an autonumber field named "ClientID". The ONLY field in
the Jobs table that refers to a client should be a "foreign key" (a long
integer field that holds the ClientID value of the related Clients record).
Let's call this field "JobClient".

If you want a list of clients in order of name, you can create a query:
Select ClientID, ClientName from Clients order by ClientName;

If you use this query as the RowSource of a combo box on a form, you then
have a way to select a particular client's ClientID by selecting their name.
Let's call this combo box "cboSelectClient".

If you want a list of jobs that are assigned to the selected client, you can
either filter a form based on your Jobs table:
Me.Filter = "JobClient=" & cboSelectClient
Me.FilterOn = True

or you can create another query string and use that for the RowSource of
another combo box or listbox:
lstClientJobs.RowSource = "Select JobNumber, JobName from Jobs " _
& "where JobClient=" & cboSelectClient

Hope this gets you going. Shout back if you need more help
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am in the middle of a rather simple task.. or at least that is what I
thought.

The situation is that I am building timesheets based upon an old design
that
is very basic. I am trying to update the information so that the employees
when inserting their time into the table (yeah using the table instead of
form, it's easier to copy an old entry and bring it forward than it is do
retype the whole thing). So now I have ported the project (job) list into
a
table and want to create a form that will locate active jobs and
automatically put the information into the form and it can be based either
on
job number, job name, or client name. the first two are pretty much a
no-brainer.. the last may have more than 3 or 4 jobs open at any one time.

So the question is... can I build this functionality into a form or do I
have to write some code to sit behind the scenes and allow a lookup and
then
paste the information automatically into the form? Can I do this with list
and/or combo boxes?

As a final step, I want the program to verify that the correct code was
entered, or can I limit the input field to just what is in the project
table.. nothing more.. nothing less...

by the way.. right now the employee time sheet is a separate database and
I
am linking the main database to the employee timesheets and vice versa....
this will keep confidential information away from prying eyes...