I am a naive access user. I am developing a database with Access as a front-end and MySQL as back-end (I have linked the tables)
I am stuck in ONE form for a while now, I need your help on this one. This database is a student registration system which must allow to do the following:
In frm Select Classes
1) The system should allow user to select classes in combo boxes (i.e, Retrieve ClassID from dbo_TblClasses). Now the selected class must be saved in dbo_TblStudentClasses only upon hitting "confirm" button.
2) Select Programs, which must be populated in a subform depending on class selection (each class has one/several programs tied to it. example: Class 255 has program "Senior Nutrition Program") - The Selected Program must be saved in dbo_TblStudentPrograms upon hitting "confirm" button.
Upon classes being selected in the combo boxes(Step 1), the following fields must be retrieved in a subform (Subform1):
a) Class ID (from combo box selection/dbo_TblClasses)
b) Organization Name (dbo_TblOrganizations)
e) AvailableSpots(dbo_TblProgramsSections) - This is an auto decrement field
3) Select preferred timing for Program, which is retrieved from dbo_TblProgramsSections (One Program can have several time slots)
Within Subform1 there is another subform (Subform2 new) - This subform retrieves the timings of each program. To view "Subform2 new" click on the tiny "+" icon that you see in subform1. The fields that are retrieved in "Subform2 new" are:
d) Available spots (dbo_TblProgramsSections)
e) Checkbox(dbo_TblProgramsSections) - This check box is where the user to makes his selection!!
4) This selected information is to be saved in a table (dbo_TblProgramsStudents)
What I have done so far:
1) I am able to select classes
2) I am able to retrieve the tied programs in "individual" subforms
3) I am able to retrive timings for these programs
4) I am also able to select the check box.
What I need help with:
1) I have provided 4 combo boxes for selecting classes. I do NOT want individual subforms for each class.
2) I want two subforms:
a) First subform must display ONLY COMMON programs that any of these classes may have (class 1 & 2 may have a common program. Class 3&4 may have a common program, all combinations)
b) Second subform must display REMAINING programs
3) Multiple users are going to use this system at the same time, I'm having issues with checkbox. It remaines checked even after closing the form.
4) Saving data into the appropriate tables on clicking "confirm"
Please do take some time out to help me. Thank you!
Thank you for responding!
But what I am stuck at is, retrieving "common programs" for the classes the user selects on my main form.
I'm trying to display these common programs in a subform. This is the query i have written so far:
SELECT dbo_TblClasses.ClassID, dbo_TblProgramsClasses.ProgramID, dbo_TblPrograms.ProgramName, dbo_TblOrganizations.Organization, dbo_TblProgramsSections.AvailableSpots
FROM (dbo_TblOrganizations INNER JOIN (dbo_TblPrograms INNER JOIN (dbo_TblProgramsClasses INNER JOIN dbo_TblClasses ON dbo_TblProgramsClasses.ClassID = dbo_TblClasses.ClassID) ON dbo_TblPrograms.ProgramID = dbo_TblProgramsClasses.ProgramID) ON dbo_TblOrganizations.OrganizationID = dbo_TblPrograms.OrganizationID) LEFT JOIN dbo_TblProgramsSections ON dbo_TblPrograms.ProgramID = dbo_TblProgramsSections.ProgramID;
The above query displays "all" common programs that classes have. But how to I restrict this to display common programs for only the classes that the user selects on the main form via combo boxes?
Is it a criteria I have to specify in design view? if so can you please guide me?
For example, there are 10 classes in my database. So what the above query does is, it displays programs that are common for all these 10 classes.
But I am trying to display programs that are common for only those 4 classes that the user selects on my main form. (These 4 classes are selected via combo boxes)