Multiple columns of data, how to use data control or dao?

I'm very new to VB, and using VB5. I'm an IT guy in a small company, so that means does anything related to computers. I've been called upon to write a time sheet/project tracking program.

I've concluded coding to check the user input. Now I want to save the data to a database.

I will outline the names for the different textboxes and how I envision the fields to be in the database. The form displays an Employee ID, their first name, last name and a corresponding week (1-52) for which the time sheet applies. Under this are columns (10) which include:

Job Number | Cost Code | Monday | Tuesday | Wednesday | Thursday | Friday |Saturday | Sunday | Totals

Each column has 12 rows that can be used for the different of Job Numbers someone has time to charge against and the corresponding Cost Codes and days of the week. Here is an example: If John Doe worked on two Job Numbers 0290 and 0291, he would put 0290 in Row1 under "Job Number", assign a proper "Cost Code" from a scroll list and assign time for the days he works on that Job Number. He would do the same in Row2 for Job Number 0291. If the person was sick one day of the week, say Friday, they would pick "Sick" from
a scroll list on Row3 under "Job Number" and put 8 under Friday on Row3 for hours. Other choices that persons can charge time to besides Job Numbers and Sick days would be vacation, holidays, overhead, personal, jury duty,
Bereavement, approved leave.

The fields in the Table "Weeks" for which the textboxes apply would be:

ID (Linked to ID in Employee Table)
Job # (Linked to Job # in Customer Table)
Wk #
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday
Current Year
For Approval (If YES, Supervisor can check time sheet for approval)
Approved (If YES, supervisor approved, sent to acctng)

For each Row (12) on the form I want to be able to save or check for existence of a record. When the user clicks on the update button it Totals each Row on the far right of the form and puts the Grand Total at the bottom. If record doesn't exist save record, if it does prompt user "Do you
want to overwrite record?" If nothing resides in a Row then there is no record to save for that Row.

When the user logs into the Time Sheet it should check for existence of records for "ID", "First Name", "Last Name" for that "Week" of the "Current Year". And display these in some order.

I thought of putting a hidden field Row#, so that records would be displayed in same order the user placed them on the form.

I've tried to add enough detail so that you could understand what I am trying to accomplish, but not bore you.

My questions are:

Is it possible to save a record for each Row on the form instead of having to create 12 fields for Job Number, 12 for Cost Code, 12 for Monday, i.e. Monday1 thru Monday12 ..... Sunday1 thru Sunday12?

Do I add a data control for each Row of Data, or do I use coding in DAO to save information that doesn't reside in its own TextBox, i.e. "Current Year", "For Approval",
"Approved"?

Since I only have one TextBox with "ID", "First Name", "Last Name" and "Week
#", how can I tie these to different records for each Row of data?

Any suggestions on how to accomplish these tasks would be much appreciated.

Quote:

---

Originally posted by smartin


My questions are:

Is it possible to save a record for each Row on the form instead of having to create 12 fields for Job Number, 12 for Cost Code, 12 for Monday, i.e. Monday1 thru Monday12 ..... Sunday1 thru Sunday12?

Do I add a data control for each Row of Data, or do I use coding in DAO to save information that doesn't reside in its own TextBox, i.e. "Current Year", "For Approval",
"Approved"?

Since I only have one TextBox with "ID", "First Name", "Last Name" and "Week
#", how can I tie these to different records for each Row of data?

Any suggestions on how to accomplish these tasks would be much appreciated.

---

1)
Your Table "weeks" has all the information you need. To fill the screen, you'd issue the following SQL;

sql= "select * from weeks where ID=" & form.ID & " and Wk#=" & form.WK & " order by Job#"

The query will retrieve 0 to 12 records (more if, at some point you add more jobs). You just make sure that the jobs represent a distinct location on the form. If you use enumeration, then it becomes relatively simple to tie a job to an location value.


2) forget DAO, code in ADO. I tend to avoid datacontrols because they're often more trouble than they're worth. Sure you have to do more work to code it yourself but you have a lot more control for that effort.

3) any time you change an employeeID you'd want to refresh your boxes with, potentially, changed data. Other than that, you only need to change the value of week# when the person using the system does...