Should I used Cascading Combo Boxes or individual tick boxes? Most effective?

[Sophie Mess]

Hi,

I've been tasked to designing a form for 'students' to use to submit which courses to take at school. The courses are split into those for Semester 1 and Semester 2 and 3(max) can be chosen each semester but it does depend on the weighting (could be 2 courses).
However certain courses in S1 and cannot be chosen together, certain courses in S2 cannot be chosen together and certain courses in S2 cannot be chosen if certain ones aren't chosen in S2.

The idea and the coding should be fairly ok...I think however I am trying to determine how to layout the available options:

- Should I use individual tick boxes (as an option group won't allow multiple selection) and then create the coding within the'submit' button

-Or is is possible to use 3 different combo boxes (per semester)and get them to each filter according to which other courses have been selected in the other 1/2 combo boxes????? Or would this be too much work??

Any ideas would be greatly appreciated,

Thanks

[ADezii]

Generally speaking, I prefer the idea of Cascading Combo Boxes as opposed to Tick Boxes in Option Groups, for the following reasons:

1. Cascading Combo Boxes would list only viable Options based on selections made on previous Combo Boxes. Code would be placed in the AfterUpdate() Event of each Combo Box in order to provide the correct choices for the next Combo.
2. Tick Boxes would take up more real estate on a Form and would probably be more confusing to the User.
3. Carefully coded Cascading Combo Boxes could avoid the possibility of having the User click the Submit Button only to have his/her choice(s) rejected due to conflicts. A Submit Button also gives the false impression that all choices are acceptable and that it is only a matter of 'Submitting' them.
4. This is only an opinion based on limited information and no actual data to go by, so please view it in this context.

[Sophie Mess]

Yes I agree with you, I think it would look much neater as well.

The real issue is that I have no idea where to begin with the coding and where to write the coding as well...can you help?

I should add I am a pretty much a beginner on all of this and therefore a combo box does seem slightly daunting!!

[ADezii]

I will need much more detailed information to get started, so kindly provide the Parameters as well as some Sample Data, what can and can not coexist, etc.

[Sophie Mess]

Ok thanks,
This is the information that we have been given:


In your role as an independent IT consultant, you have been asked to tender for a large-scale programming contract. To establish your programming credentials, you have been asked to design and develop an information system, which will be primarily used to capture students’ option choices, for use in a Business School. The system will run on Microsoft Access supported by programmes written in Visual Basic. The requirements specification for the system is as follows:

The system should be designed so that students can automatically select their options from a screen which will indicate to them whether they have broken any of the following selection constraints:

• Each student must select exactly 30 credits worth of modules in both the first and second semesters. The descriptions, credit weightings, and time-tabling arrangements for all the option modules are presented in the table below.

•International Business 2 cannot be selected unless International Business 1 has also been chosen.
•Business Programming 2 cannot be selected unless Business Programming 1 has also been chosen.
•Decision-making and Decision Analysis share some common material so students cannot choose both these options.
•Business Finance and Corporate Finance share some common material so students cannot choose both these options.

•Business Planning and Corporate Strategy share some common material so students cannot choose both these options.

Semester 1:
International Business 1 20
Business Programming 1 20
Decision-making 20
Change Management 10
Business Planning 10
Small Business Issues 10
Decision Analysis 10

[Sophie Mess]

Semester 2:

International Business 2 20
Business Programming 2 20
Business Finance 20
Corporate Strategy 10
Career Management 10
Business Ethics 10
Corporate Finance 10

• In addition to the modules, highlighted above, there is also an optional Management Dissertation, which runs across both semesters, and is weighted 10 credits per semester, and therefore 20 credits in total

[ADezii]

1. Sophie, this appears to me to be more and more like a School Assignment, is it?
2. After carefully reading Post# 5, this is much more then just designing a Form to use to Submit which Courses Students take at school. It is actually a complete Information System used to capture Student's Course selections for use in a Business School, along with a series of Constraints both within Semesters and among Semesters.
3. I do not mind assisting you in the basics, but we will not do the Project for you.
4. I created a Sample Demo for you which reflects how I feel this System can be developed.
5. Look closely at the Tables,their structures, the Relationships that exist among them, and the data contained within them.
6. You will also see, that to the best of my knowledge, no Constraints have been violated as far as the sample data in them.
7. Look also at the Querys: qryCourseSelectionByStudent/Semester and qryTotalCreditsByStudents/Semester.
8. From the guidelines listed in Post# 5, as well as the limited number of actual Courses, your Form should be divided into 2 Logical Sections representing Semesters 1 and 2. For each Semester, Check Boxes should be contained within a Logical Group but not in an Option Group where they would be mutually exclusive. They can be placed within a Rectangle Control which can closely mimic an Option Group, but allow for Multiple Selections within the Grouping.
9. Code in the AfterUpdate() Event of each Check Box can Call a Procedure which will take care of the necessary Validations/Constraints, and make any notifications and adjustments if required.
10. For now, look at the Attachment, and if you have any questions, feel free to ask.

[Sophie Mess]

Ok great, actually it looks like I am on the right track already then...yes it is for school and don't worry I never thought that you would be doing it for me!! Though obviously that would have been great!!!!

I have already split the two module sections into option groups, and though there is no linkage between them, there are individual grouped within a rectangle.

Thanks for all your help thus far, it's been very useful!

Just to prove that I am doing it on my own I've attached a screen shot of my database to show you!

Sophie

[ADezii]

Looks great so far, Sophie. Just a couple of little pointers that you may/may not wish to use:

1. Name your Check Boxes so that you can easily identify to which Semester they belong. You could use a naming System such as chkS1... for Semester 1 Courses, and chkS2... for Semester 2 Courses.
2. Plcae the exact Course Name in the Tag Property of the corresponding Control, as in:
   

   Check Box Name    Tag Property of Control
   chkS1CngMnt       Change Management
   chkS1BusPln       Business Planning
   chkS2BusEth       Business Ethics

3. Now, I'll illustrate the advantages of such an approach. To retrieve a listing of all 'Selected' Semester 1 Courses, along with the Control Name, actual Course Name, and the Total Number of Courses selected:
   

   Dim ctl As Control
   Dim intCtr As Integer
    
   Debug.Print "Control Name", "Course Name"
   Debug.Print "------------------------------------------------"
    
   For Each ctl In Me.Controls
     If ctl.ControlType = acCheckBox Then          'Is the Control a Check Box?
       If Mid$(ctl.Name, 4, 2) = "S1" Then         'Semester 1 Courses only
         If ctl.Value Then                         'Semester 1 Courses Selected
           intCtr = intCtr + 1                     'Increment Course Counter
             Debug.Print ctl.Name, ctl.Tag
         End If
       End If
     End If
   Next
    
   Debug.Print
   Debug.Print "Number of Semester 1 Courses Selected: " & intCtr

4. Sample Output based on 3 Courses Selected for a Total Credit of 30, with no Constraints being violated:
   

   Control Name  Course Name
   ------------------------------------------------
   chkS1DecMak   Decision-making
   chkS1CngMnt   Change Management
   chkS1SmBusIsu Small Business Issues
    
   Number of Semester 1 Courses Selected: 3

5. Let's now say that 1 or more Semester 1 Constraints have been violated. To Clear all Semester 1 Check Boxes only:
   

   Dim ctl As Control
   Dim intCtr As Integer
    
   For Each ctl In Me.Controls
     If ctl.ControlType = acCheckBox Then
       If Mid$(ctl.Name, 4, 2) = "S1" Then         'Semester 1 Courses only
         ctl.Value = False                         'Clear Semester 1 Courses
       End If
     End If
   Next

[Sophie Mess]

Ok thanks.
I have been attempting the coding this afternoon..I expect I will get stuck somewhere so if I do I might post if on here...if you have any feedback for me!!

Many thanks

[ADezii]

Not a problem. One additional point is that you may wish to provide some Visual Cue as to how many Credits each Course is worth. You can actually add this info into the Labels for the Check Boxes, as in:

International Business 2 (20)
Business Planning (10)
etc...

[munkee]

Did I notice a Loughborough logo there sophie?

[Sophie Mess]

Yes I'm already on that!!

Codes going ok (surprisingly!!). But what I would like to do is create a running total under each semester list to show either how many credits are left, or how many are being used and to update itself each time an update is made............any ideas?

I've written the code to calculate the current total, but i've no idea how to get the result to feed into the form or to constantly update?

Thanks!!

[munkee]

Sophie,

You should be able to use the after update event of the option control to produce a calculation which will then update the running total at the bottom.

This will basically fire the code you place in the event, as the name suggests after each update.

Edit:

After reading up a bit. With your after update event you would need to have it check whether the other checkboxes within the "fake" option group are selected or not. If they are or are not (depending how you want to calc it) selected you can then use this to take values to perform the summation at the bottom.

I expect Adezii will produce a nice function for this though as I am quite basic :p

[Sophie Mess]

also i've got to add security features to the database, I've already started on a password section...do you have any other ideas?
Is it possible to turn off the record search at the bottom of the form
is it possible to only allow students access to certain forms (and not tables)
is it possible to allow teachers different access?

[munkee]

Here is a quick example of the summing I was talking about (attached).

By record search do you mean the record navigation buttons? The ones that let you click through each record using the arrows.

[Sophie Mess]

Yes the record Navigation buttons...

[munkee]

In the form properties area go to "Format" and there should be "Navigation Buttons" in the options, set this to No.

[Sophie Mess]

ahh fab thank you!

[ADezii]

Assuming you have been using my suggestions relating to Coding, Naming Conventions, Structure, etc...

1. Create a Text Box under the Semester 1 Group and Name it txtS1Credits.
2. Label it appropriately.
3. Set the Default Value to 0.
4. Create the following Private Function in your Form's Class Module:
   

   Private Function fCalcS1Credits(bytSemester As Byte)
   Dim ctl As Control
   Dim intS1Credits As Integer
    
   For Each ctl In Me.Controls
     If ctl.ControlType = acCheckBox Then
       If Mid$(ctl.Name, 4, 2) = "S" & CStr(bytSemester) Then     'Semester 1 only
         If ctl.Value Then                     'Must be selected
           'Let's create a Running Total by retrieving the Credits based on
           'a Course as retrieved from the Check Box's Tag Property
           intS1Credits = intS1Credits + DLookup("[Credits]", "tblCourses", "[Name] = '" & ctl.Tag & "'")
         End If
       End If
     End If
   Next
    
   'Assign Total S1 Credits to the txtS1Credits Text Box
   Me![txtS1Credits] = intS1Credits

5. In the AfterUpdate() Event of each Semester 1 Check Box, place the following Code:
   

   Private Sub chkS1IntBus_AfterUpdate()
     Call fCalcS1Credits(1)
   End Sub

6. Now, each and every time you Select/Deselect a Semester 1 Course, the Text Box will be dynamically updated.
7. I'll leave Semester 2 up to you, since it now can be easily implemented.

[munkee]

Sigh! Adezii you make my solutions look so stoneage good work

[Sophie Mess]

Amazing! Will try it in a second!!

Just finished the bulk of coding...and it seems to work fine! so that's good, only thing i'm slightly stuck on is when I want the record to save and move to a new one, what coding is correct, I used:

docmd.gotorecord , , acNext

which saves the record, but then say's 'delete' on the fields on the new record?!

Thanks again!!

[ADezii]

Thanks munkee, just years and years of practice, and a lot of mistakes along the way! (LOL).

[ADezii]

The Check Boxes are not 'Bound', so you cannot simply Save the Record.

[Sophie Mess]

ignore that last post!!

[Sophie Mess]

Adezii which is the Form's Class Module?...the table with each the the different modules listed or the form to enter module selection?

also if by naming conventions you mean chks1/2 then yes I have!

[ADezii]

Adezii which is the Form's Class Module?...the table with each the the different modules listed or the form to enter module selection?

In Form Design View ==> View ==> Code

[Sophie Mess]

Function isn't working yet but will keep trying..

Also I am trying to populate certain text boxes from the student details table depending on the 'Student ID' inputed from the drop down box. I'm using the following but it doesn't work????

studentfirstname = DLookup("First Name", "Student Details", "StudentID= " & Forms![Option Form]!StudentID)

[Sophie Mess]

Also -

Is it possible to populate a field on a form using a value entered at an earlier stage...i.e. at the log in stage?

[ADezii]

studentfirstname = DLookup("First Name", "Student Details", "StudentID= " & Forms![Option Form]!StudentID)

1. Is the Table named Student Details?
2. Is the First name Field actually named First Name, if it is then it must be enclosed in Brackets because of the Space, as in:
   

   studentfirstname = DLookup("[First Name]", "Student Details", "StudentID= " & Forms![Option Form]!StudentID)

3. Is the StudentID a Numeric Value?

[Sophie Mess]

1.) yes the table is called student details
2.)yes called first name, in the student details table
3.)student ID looks something like A763828

[ADezii]

Since StudentID is NOT Numeric, then a change in Syntax should do the trick:

studentfirstname = DLookup("[First Name]", "Student Details", "StudentID= '" & Forms![Option Form]!StudentID & "'")