Is there any way of using a form so that users can choose which fields they want to appear in a data entry form.
I have three columns (one for target, one for current level and one for comments) for each of a variety of subjects. What I really need is for the user to be able to select a subject from a list so that a form opens with the three fields for their subject ready for data entry.
Any advice would be much appreciated. I'm pretty new to access though so please spell it out if you can!
The table doesn't actually exist yet, I'm still trying to get my head around the best way to do it. It was suggested to me that I ought to have a seperate table for each subject. I was then going to create a query to bring them all together (meaning that yes I would have 3 fields for each subject) and then base the form on that.
This doesn't feel right to me but I can't think of the best way to do it. Should I have it all in one table with many records per student? But then how do I identify which record is which subject without having a field for subject that the teacher will have to fill in for each student?
This goes all the way back to relation theory. I did something like this for my second degree, Basically you will need a table (entity) called subject, you might have target, etc as fields within the subject.
You shoudn't need a separate table for each subject, especially if the structure of the table remains identical.
In answer to the first question though it looks like it would be easy to do what you suggest if you based the form on a query then either created the query dynamically or passed the name of the table (subject) to the query before the form opens.
I suspect though that you relly need to extrat the requirements for the database to get the table structure correct first.
I agree with Ryker's ideas on using a single table to contain the subject data. This will make things much easier for maintenance and updates. You'll need to know the Student, The Subject assignment, and have a table to contain the results. For this you would have a minimum of three tables, maybe four tables.
The tblSubjectList would be used to fill the list of subjects on a selection form used to initialize a question selection form.
Table 4. tblSubjectFields, would be related to the tblSubjectList to maintain specific text for the questions based on the selected Subject.
Table 3. tblSelectionData would contain the results and also have Foreign key fields, 'studentID', 'subjectID', 'questionFieldID'. In this way you will have a unique listing based on the student/subject/question of each entry. 'studentID', 'subjectID', 'questionFieldID' fields will also be contained in the respective tables 1,2, and 4 as Primary Keys.
Using the IDs (Primary and Foreign Keys) in the tables is a key to the function of a Relational database. If you understand this your database design will be much better and easier to construct and maintain.
I think it is usually better to base your forms on a Query rather than directly to the Table. When you have more complex design this allows you to construct datasets based on multiple tables.
This is complicated to explain. After you have something put together to start with, if you post a copy here I'm sure someone can offer help with specifics on the design and table structure.