Using Variable data from a Form as Field Names

[AirDavidADP]

Using Access - I have a table where there is one field for each Weekend (field names are Week 1, week 2 and so on till Week 52), so I have 52 fields. I have a form that has 4 text boxes. Depending on the current weekend date, the boxes are for the current week and the next 3 weeks. Work percentages are added to each of the 4 boxes and need to be saved to the Weekend field in the table. So this week we would be saving data in Week01,Week02,Week03 & Week04. Next week we would be saving data in Week02,Week03,Week04 & Week05. How do I pass the data to the correct field each week. Is there VBA code that will let you pass the field name as a variable.

[Seth Schrock]

The Recordset.Fields() property allows you to pass the field name as a string and then assign or return a value.

However, you should look into Database Normalization and Table Structures. A properly designed table would have each week as a separate record instead of a separate field. You would want a primary key field (it could be an auto number field), a week number field, the value field, and if you need it, a foreign key field to link to another table. You would then just have a query return the weeks that you need and then input the values in each record and then the problem that you are currently having will no longer be an issue.

[NeoPa]

Seth's answer is almost perfect. I almost replied with a simple quote of the title with a comment of :
"That seems like a very bad idea."

Obviously I'd have included something more helpful too, but it looks like Seth's got you fully covered already.

[AirDavidADP]

Seth, Thank you for your comments. I like the ideal. There is a key field for each record. If I only use one field each in the table for Week No, Weekend Date and Percent then how would the user add in current week info plus est percent for the next three weeks. Each entry would get it's own records. Would they have to do 4 new records one at a time. The table also have fields for Project Name,Status, Start Date and Go-live Date. Would the user have to key in the other info each time also. Thank you for your help.

[Seth Schrock]

It is hard to really be specific without knowing exactly what you are trying to track, but let me give you an example.

Let's say that you are a teacher and every week you give your students (there just happens to be four of them) an assignment. Each assignment will have a name, a status (open, closed, etc.), a start date and a due date. Each student will be given a grade, a date completed, and a status (not started, completed, etc.). So in this example, you have two parts: many students' grades to one assignment. This is known as a one-to-many relationship and involves two tables.

tblAssignments
AssignmentID
AssignmentName
Status
StartDate
DueDate

tblGrades
GradeID
AssignmentID
StudentName
CompletionDate
Status
Grade

You will notice that tblGrades has a field called AssignmentID. That is a foreign key field that links to the primary key field in tblAssignments. That is what ties the grades to the specific assignment. Now, the idea is to take away duplicated information. For example, you wouldn't put the assignment due date in the tblGrades because it would be the same for each student for that assignment, so you put it in the "one" side of the relationship.

Now to get back to your situation, you can think of each week percent as a grade and your group of four weeks as an assignment. Your job would then be to decipher which fields need to be on the "one" side and which need to be in the "many" side of the relationship. Make sure that you read the article that I linked to in my previous post.

Also keep in mind that there is often room for more tables than you originally think. For example, going back to the grades situation, you would probably want to not use the student's name in the tblGrades, but another foreign key field tied to a table with all the student information. Again, not knowing your data, I can't say that this would be the situation for you, but just keep your eyes open for the possibility.

Let us know if you have any more questions.

[AirDavidADP]

Seth, Thank you for your reply. I am working on a db that tracks projects. For each project there can be up to 5 different team members working on different parts of the project. Each week, each team members updates the per of time they spent on the proj that week and an est of how much time they will be spending over the next three weeks. The management team uses the info to make sure the workload is spread out to everyone and uses the est time to see if they need to bring in more staff or cut back staff.

I understand what you are talking about having two tables one for the project data and one for just the time update. I will go back and make the changes to my db. Thank you for your time.