purpose of subform?

[mike]

working on form to select records from 2 tbls and create a new record based
on selected items in a 3rd tbl.

tblStudents(list of students)
tblEnglish (list of english courses)
tblMath (list of math courses)
....(one table for each course topic)
tblStudentSubjects (list of students w/ selected subject)

main form (tblStudents)
-cbo to display and select a student from tblStudents
subform(tblStudentSubject or should i be using tblMath, etc.?)
-cbo for each course to display and select from tblMath, tblEnglish, etc.
-is it necessary to use a sub form for this?

-is it appropriate to use a btn to create a new record in tblStudentSubject
when the appropriate student and course is selected?

 

[Gina Whipp]

Mike,

Before you even get to form you need to review your table structure.
Creating seperate tables for each subject is kinda like building a seperate
house for each room. Why not have a table for Subjects and put all the
Course Topics in one table.

Then:

Main form (tblStudents)
Subform(tblStudentSubject ) In the subform use a combo box to look-up
Subjects from tblSubjects

 

[mike]

Why not have a table for Subjects and put all the Course Topics in one table.
i can do that.

then how do i specify that the selected student and subject is added as a
new record in tblStudentSubject

 

[Gina Whipp]

The table tblSubjects

tblSubjects
sSubjectID (PK)
sSubject
sDescription
....add as many fields as you need

tblStudentSubject
ssStudentID (FK - tied to tblStudents PK sStudentID)
ssSubjectID (FK - tied to tblSubjects PK sSubjectID)
ssDescription
....add as many fields as you need

 

[mike]

okay got all that but tblStudentSubject is not adding records correctly, is
the form setup correct?

1. create form using wizard
2. specify name field from tblStudents to show on form
3. then delete name field and add a cbo for list of names, select save
value for later use
4. add subform
5. specify tblStudentSubject and all fields to show on subform
6. delete fields and add cbo for list of subjects, select save value for
later use
*is this the correct setup?

*is it ok/appropriate to use cmdBtn to create new record in
tblStudentSubject for selected student/subject?

 

[Gina Whipp]

Mike,

1. - 3. The combo box on the Main Form should be unbound. You do not need
to save the value for anything.
4. Subform should be in continuous view and attached to the Main Form (Link
Fields) StudentID. Then the field Student Subject should be a combo box
using the tblSubjects as the Record Source and the value should be bound to
the ssStudentSubject.

Why would you use a command button, setting the subform to continuous view
should show you an empty row to add new records.

 

[mike]

Gina,

1. - 3. The combo box on the Main Form should be unbound. You do not need
to save the value for anything.

got it.

4. Subform should be in continuous view and attached to the Main Form (Link
Fields) StudentID. Then the field Student Subject should be a combo box
using the tblSubjects as the Record Source and the value should be bound to
the ssStudentSubject.

-how do i (or when in the wizard do i) link the subform to the main form?
-tried setting "Source object" for subform to main form but when i try to
link child/master fields, get message stating "cant link unbound forms"

tried all above 1)new record is created in tblStudentSubject w/ correct
subject from cbo in subform but 2)studentID is blank.

 

[mike]

fyi: relationships (in case this affects the setup)

-tblStudents.StudentID 1-to-many relationship to tblStudentSubject.StudentID
-tblSubjects.SubjectID 1-to-many relationship to tblStudentSubject.SubjectID

 

[Gina Whipp]

Oops forgot about that...

You can't use the wizard to link them... Go to Properties of the Subform

LinkChildField = NameOfStudentIDField in tblStudentSubjects
LinkMasterFields = NameOfComboBox on Main form

Student ID is blank because the form is not linked AND you need to create a
Relationship between the tables with Cascading Updates set to Yes...

You know I'm starting to get the impression I am doing your classwork for
you. Is this an assignment?

 

[Gina Whipp]

Cascading Updates set to Yes???

 

[mike]

i'm a hs teacher who took on a project over break (not paid). trying to use
access to streamline teacher recommendation process at our school. right now
each teacher has to fill out a paper form for each student to select which
subjects he/she recommends for the coming year. then guidance has to gather
all the paper forms and figure out which student was recommended for which
class(es), all this in a short turnaround timeframe.

needless to say its not very efficient so i volunteered to try and create a
program to do this so. interface to show list of students and subjects,
allow teacher to select student and subject, then run reports for guidance.

its turning out to be a bigger task than i thought (always is but i'm
getting close

 

[Gina Whipp]

Okay, well that explains it... Unfortunately, Access does have a bit of a
'learning curve' that isn't obvious right out of the box. You have few more
days until break is over... you should make it!

 

[mike]

Gina,

thx to you more progress

i ended up putting cboStudent in the subform. when i select a student and
subject, it creates a new record in tblStudentSubject correctly.

interface issue: the cboStudent and cboSubject show up in column format on
the subform. is there a way to format to make it more aesthetically pleasing
i.e. some separation between the two cbo's?

 

[Gina Whipp]

Mike,

Good to know 'cause it's almost midnight here and I need my rest )

Go into design mode of the form (the little blue triangle and move the
fields around/over/up/down...

 

[mike]

Gina,

hope you slept well

Go into design mode of the form (the little blue triangle and move the
fields around/over/up/down...

-when i go into design mode, the cbo's show up as independent boxes inside
the subform which i can move all around (no blue triangles?) but no matter
they still show up as side-by-side columns in form view.

-also i think there will be less incorrect entries if i add a cmdBtn to
allow user(teacher) to confirm selection. else, as soon as a class is
selected its written to tblStudentSubject which i believe will lead to alot
of invalid entries. is that difficult to change?

 

[Gina Whipp]

Mike,

Like a rock!!!

1. Are you using Access 2007? Maybe you said but I missed that. In
earlier versions to get to design mode you clicked a blue triangle looking
button. If Access 2007, then you probably have to UNgroup them to get them
to move independently of each other. I THINK this can be done by
right-clicking on one of the boxes and select 'ungroup'. If that doesn't
work I'll have to open my Access 2007 and have a look. Also, if you are
using Datasheet View then they will always be side-by-side no matter where
you move them.

2. Then by all means add the button to add a new record. You know your
users better then anyone.

 

[mike]

Gina,

i'm using 2003.

2. Then by all means add the button to add a new record. You know your
users better then anyone.

since the cbo's are bound to fields in tblStudentSubject, when i select a
cboStudent and then select a cboSubject, it creates a new record in
tblStudentSubject which is what i want. however, i'd like to add a
confirmation step. after user selects subject use a messageDialogBox to
confirm entry.

-i imagine i have to use VBA something like

Private Sub cboSubject_click
MessageDialogBox.show("Confirm entry")
if yes, then add record to table w/ selected item in both cbo's
else, reset cbo's w/out writing a new record
End Sub

will that do it?

 

[Gina Whipp]

Yes, that will work...

 

[John W. Vinson]

-i imagine i have to use VBA something like

Private Sub cboSubject_click
MessageDialogBox.show("Confirm entry")
if yes, then add record to table w/ selected item in both cbo's
else, reset cbo's w/out writing a new record
End Sub

I'd actually use the Form's BeforeUpdate event.

HOWEVER...

in my experience this kind of confirmation really ends up being an annoyance.
Users will get to the point that they thoughtlessly just choose OK (and then
ask you to fix up the damage they've done), and it will just waste their time.
"Yes, I wanted to make an entry, that's why I opened the blasted form!!!"

 

[mike]

Gina, John (Vinson), and Allen (Browne):

wanted to say thank you for your gracious help. thx to Allen and John i was
able to "properly" construct a DB using relationships, and thx to Gina for
helping me bring it all together.

john: i agree with your point about the confirmation button. going to omit
it for now.

as a teacher, i have an appreciation for people who are willing to share
his/her knowledge for the sake of helping

all in all i've got what i set out the create. could use a few bells and
whistles but i can fiddle with it now.

Regards.
Mike