linked data in fields

[tectech]

I hae a db that keeps track of classes, insturctors, and the times and days the class is taught. I have a table with all the days of the academic week (M, T, W, R, F, S) set up with an identity field so I can sort them in this fashion. Classes can be held on one day of the week, and most are held twice a week liek MW would be class is on Monday and Wednesday.

What I want to do is have a list, or some type of option that allows me to choose two or more days for a class and have those two selected days joined in one field as a single peice of data.

I would also like to list the classes of a particular semester by selecting an item from a combo box. This combo box is located on the main form of the db, and the classes are listed in the subform. The list of classes is unique to the insturctor listen in the main form. I have tried to make this option work, but it never does. Not sure what I need to do to make any of these work, I have exhausted all my techniques.
Can anyone help me with these problems?

 

[Dave Eyley]

OK Tectech,

There are 2 things here -

1. Making a field up to show the days for the courses

2. Selecting classes by instructor

I'm not sure if you want to hold several semesters data, but I'll ignore that for now.

1. Combining days of the week

Put 6 (UNBOUND) check boxes on the form labelled Mon Tue Wed Thur Fri Sat

The user gets to check one or two relevant days depending on the course duration.

Then use this code to combine the 2 days, if necessary -


Dim Flag As Integer, CourseDays As String
If Not IsNull(Mon) Then
Flag=Flag+1
CourseDays="Mon"
End If

If Not IsNull(Tue) Then
Flag=Flag+1
If IsNull(CourseDays) Then
CourseDays="Tue"
Else
CourseDays=Coursedays & "/Tue"
End If
End If

The above block is repeated for the remaining days of the week substituting the relevant day. Then,

If Flag>2 Then
MsgBox "More than 2 days selected. Please check your course days.",VBExclamation,"Course Days"
Exit Sub

Me!CourseDays=CourseDays

This code could be put into a custom Form's Close button's OnClick Property, and,


DoCmd.Close acForm, "MainForm", acSavePrompt


2. Selecting subform data from a combo box.

The main table will have classID, instructorID, CourseDays and semesters as fields with a primary field of ClassRefNo

Create the combo called 'Instrname' and from a table containing only data relevant to the instructor (called 'Instructors') make the RowSourceType=Table/Query
and the RowSource=Instructors name.
Have a hidden unbound field on the form called InstrID and on the AFTERUPDATE property on the combo use -


InstrID=DLookUp("[InstrID]","[Instructors]","[InstrName]=Forms!MainForm!Instrname")


Now create your subform with the relevant fields, including the InstrID and make this field the Master/Child link

Now when you select an instructor the subform will display all of the records containing the selected Instructor.

You'll notice that I only had a ClassID field in the Main table. This is because I would hold the class data in a separate table called 'Classes' and when I needed to display the class name, substitute the name for the related classID in a query.

If you need to work on the Coursedays field you'll have to split up the field again. This can be done by -

If Len(CourseDays)>3 Then
Day1=Left$(Coursedays,3)
Day2=Right$(CourseDays,3)
Else
Day1=CourseDays


Is this something like you want?

Dave Eyley

 

[tectech]

The first half looks great, that should work just fine. The second half may require reworking the main form. What the main form show is the instuctors name, office location, phone, e-mail, and department. This is data from an instructor table. The subform is the room, class id, day, time, and class name. This is from a class table that also lists the name of the instructor - which is link by a relationship to the instructor table. each time you scroll through the instructors names, the subform shows a list of all the classe taught by that insturcotor. What I have done was create a new ADD CLASS form which will populate the data into the classes table, and the data appears in that subform.

This is data being collected for statistical use in my department, and being able to collect semester to semester data is one of the things I would like to implement. This way I am not reinventing the wheel to start a new semester database each time.

ALSO, where do I put that code for the days of the week?
It seems to give me a -1 in the table field instead of the days, and does not show the days in the check boxes as I scroll through the instructors

 

[Dave Eyley]

Hi again,

I'm a bit concerned that you say you want the combo on the Instructors mainform to select the classes for different instructors in the subform, while displaying all the instructor's details on the same mainform. When you change the combo, the subform will update but the mainform fields won't. Unless you have 2 subforms (1 for the instructor's details and 1 for the classes) you are not going to be able to use a combo to change instructors because the other fields on the instructors form will not automatically update. That would be disastrous.

If the Coursedays are part of the CLASS data, then the code and checkboxes should be on the ADDCLASS form.
The checkboxes shouldn't be in the table at all. I may not have explained myself adequately.
The checkboxes are unbound, i.e. not related to any table. They are there purely to allow the user a convenient way to enter the days of the course. Once they've done that, the code combines the days by joining a couple of string variables and then makes the table field called 'CourseDays' show the constructed string e.g. "Mon/Wed".
I hope this explains it better.

The location for this code could go on a customised EXIT button on the ADDClass form(disable the closebox if necessary) when the data will be saved to the table. The field for Coursedays is a field from the table.
So on the ONCLICK property of the Exit button you would have
the above code followed by -
DoCmd.Close acForm, "ADDCLASS",acSavePrompt

To summarise -

Have a menu form with buttons that open new forms.
Button 1 - Add New Instructor
Button 2 - Add New Class
Button 3 - View/Edit Instructor (could put combo here)

The ADDCLASS form allows the input of new class details to the table CLASS. This is where you would use the checkboxes and code.

The Instructors mainform would display all the info about the selected instructor and should have a subform showing all the info from CLASS relating to that instructor. No combo needed.

The ADDINSTR form allows input of a new instructor and associated details.

The CLASS table would be the main table.
The Instructors Table would be joined to the main table by the field "InstrID"

Some of what I said in the last message is different from above because of what you've told me, but there should be enough here to get you going.

Semester - by adding another field to the CLASS table called semester, you could select which semester you want to see and use this field to sort/filter any reports.

I'll be watching out...

Dave Eyley

 

[tectech]

Wow, that worked great. I have reformated the database based on some of you criteria, and it is working out well. Thank you so much for your help, it is working out well.

 

[Dave Eyley]

I love problems! And solutions are ecstasy!

Good Luck....

Dave Eyley