If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
form vs query vs table
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... -- bob |
#2
|
|||
|
|||
form vs query vs table
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 "bobc721" wrote in message ... 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... -- bob |
#3
|
|||
|
|||
form vs query vs table
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 "Graham Mandeno" wrote: 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 "bobc721" wrote in message ... 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... -- bob |
Thread Tools | |
Display Modes | |
|
|