Hi,

How can I save data from a form into a table, but then this data comes from another tables?

I only work with the following tables:

1. ClassMember

2. Class

3. Course

4. Module

5. Section

6. Question

I have a problem with the Class and the ClassMember tables.

Look at following two scenarios.

Scenario 1

The class table stores details about a particular course e.g. INFO200 or ECON100, etc. being run in a particular year e.g. 2002, 2003, 2004. It also stores the course coordinator for that course, for that year.

        e.g. INFO200 in 2003 coordinator Lyrice Cohen
             INFO200 in 2002 coordinator Suzanne Sackstein

To allow a user to create a class you essentially need to allow them to pick from the available courses, and lecturers and assign a year.

Now the problem is: On a form I want to have like 3 combo boxes with Courses, Lecturer and Year respectively. After choosing all the three then click Create class and the information gets stored into the table Class. I can't do this bcos I cannot take information from three different tables in a form and save it to another table.

Scenario 2

You then have to enable your user to add members (students) to that class. This essentially requires picking from the available students and adding them to the ClassMember table (for a particular class - see above).

For this one I want to have a form again with 3 combo boxes. This time with Student names from Student Table, Course codes from Couse table and Year. (NB This 3 fields are Primary Keys in the ClassMember table.).When I click the button ADD TO CLASS MEMBER on the form, it should take the student number that corresponds to that student name appearing in the first combo box, the course code appearing in the second combo box and the year appearing the third combo box and together store them in the ClassMember table.

I hope this now clarifies my question.

Thanks.



OK here are the tables:

[u]tblClassMember</u>
StudentNo
CourseCode
Year
Repeat

[u]tblClass</u>
CourseCode
Year
Coordinator

[u]tblStudent</u>
StudentNo
Surnane
FirstName
DOB
Gender

[u]tblCourse</u>
CourseCode
CourseName
Description
SchoolOrDept
Type ----->Either the type is ENRICHMENT OR ACADEMIC

[u]tblModule</u>
ModCode
ModName
Lecturer
CourseCode

[u]tblSection</u>
SectCode
SectName
ModCode

[u]tblQuestion</u>
QuestNo
QuestText
Ans1
ans2
ans3
ans4
ans5
CorrectAns
DegOfDiff ------>This is degree Of Difficulty
SectNo



NB: THE WHOLE SCENARIO IS THAT THERE IS A COURSE. THIS COURSE IS DIVIDED INTO MANY MODULES. EACH MODULE IS DIVIDED INTO MAY SECTIONS, LASTLY A LECTURER CAN CREATE QUESTIONS BASED ON THE SECTIONS MEANING EACH SECTION HAS ONE OR MANY QUESTIONS. THE SCHEMA IS FULLY NORMALISED AND TABLES HAVE/ARE LINKED WITH FOREIGN AND PRIMARY KEYS.

GIVE ME LIGHT ON HOW TO APPROACH THIS PROBLEM. IF U CANNOT: A SIMPLER VERSION OF MY PROBLEM IS:

1. SAY I HAVE CREATED A TABLE USING ACCESS.

2. I THEN CREATE A FORM WITH TEXTBOXES EXACTLY THE SAME AS THE TABLE FIELDS. NB: THIS FORM IS NOT CREATED USING AUTOFORM.

3. HOW WILL I LINK THE INFORMATION I CAPTURE IN THE FORM TO THE TABLE. I WANT TO CLICK THE SAVE BUTTON AND THEN THE INFORMATION GETS STORED IN THAT TABLE.

THANKS...










TK, Novice VBA Programmer, RSA

Hi takawera,

If you have access to an instance of SQL Server, or can install MSDE,
Microsoft already built this database for you. It's an Access Data Project, though. You can download it at:

http://msdn.microsoft.com/library/de...l/apgintro.asp

All you'd need to do is attach the .mdf file (the SQL Server database file) to an instance of SQL Server, and point the .adp file at it. The interface is done, and sample data is included. It's a student enrollment app that does exactly what you want. If I get a chance latter, I'll at least post the table schema. Your tale design could use a little tweaking, and you should get that squared away before thinking about the user interface.

HTH,

Bob