Mar 7 2009, 03:00 AM
First, let me say that I am a beginner-intermediate.
I have a form "Roster2" that allows me to scroll (combobox) through a primary key field "ClassID"and display student information in a subform "Roster2SubForm"
Form "Roster2" record source is "classtable"
subform "Roster2SubForm" record source is a query from "studenttable" and "studentsandclassestable" which are joined (classtable is joined with studentsandclassestable also)
My question is this: I want to be able to select all the records displayed in the subform, and copy them into a new subform (as in adding new records) then go to that new record's form to select the data that will join them through the links already established.
I don't want the user to use the mouse and "copy/paste", as it leaves too much room for error.
Is it possible? is there an easier way to do it?
Any help would be appreciated
Mar 7 2009, 03:11 AM
The short answer to your question is that you would usde an Append Query.
The not so short but more to the point answer is that the need to do something like this in a database is almost non-existent, so your request would very probably indicate a design misapprehension. If you would care to explore that aspect, perhaps if you post back with more specific details of the data you are working with, including examples, someone may be able to advise.
Mar 7 2009, 04:02 AM
Thank you for the reply. Very likely a design misapprehension. For some reason, I can't seem to get my head around this.
I have a DB that tracks student coming through training, usually for multiple courses. Each course is assigned one of four Course ID numbers "CIN", as well as start and grad dates, etc.
I have it set up so that when a group of student arrive, I start a new class using the above data (CIN, dates, etc.). "ClassTable", primary key "classID"
Then I enter student information: names, address, phone, etc. in "studenttable" primary key "SSN"
This records are displayed on a form/subform and data is linked through table relationships.
My intent is to be able to assign the same group of students that were in one class, say classID = 1, to another class with a different classID number.
I want to make this as idiot-proof as possible, as there will be multiple users.
I hope this helps explain my predicament. I appreciate any help. If I'm going about this the wrong way, feel free to call me an idiot.
Mar 7 2009, 05:47 AM
You have your students recorded in the Studenttable table.
You have a master list of classes in the ClassTable table.
You do not say how you identify which students are enrolled in which classes. But there is a one-to-many relationship in the real world between students and enrolments, and because of this you need to have enrolments recorded in a separate related table. This table would have foreign key fields to relate to the primary key fields of both the Studenttable table and the ClassTable table.
I assume the need to record student attendance at each of the 4 courses is because there are exceptions to the rule... not all students attend all 4 at the same time?
As such, yes you do need to write this to the enrolment table. And you would, as I suggested before, use an Append Query for this. You will be able to automate the execution of the append query. But all you are "copying" is the Primary Key of the students.
By the way, I have found out what a SSN means. The general wisdom is that this does not make an ideal primary key. It is not universal, nor is it unique (even though theoretically it is supposed to be).
Ok, not a complete answer, but hopefully enough to move you forward.
Mar 7 2009, 11:01 AM
Although it is improbable in your case for this use, there has been cases of re-use of the SSN here in the States.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here