A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

purpose of subform?



 
 
Thread Tools Display Modes
  #1  
Old December 30th, 2008, 09:31 PM posted to microsoft.public.access.forms
mike
external usenet poster
 
Posts: 3,942
Default purpose of subform?

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?
  #2  
Old December 30th, 2008, 09:48 PM posted to microsoft.public.access.forms
Gina Whipp
external usenet poster
 
Posts: 3,500
Default purpose of subform?

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

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

"mike" wrote in message
...
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?



  #3  
Old December 30th, 2008, 10:42 PM posted to microsoft.public.access.forms
mike
external usenet poster
 
Posts: 3,942
Default purpose of subform?

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
  #4  
Old December 30th, 2008, 11:00 PM posted to microsoft.public.access.forms
Gina Whipp
external usenet poster
 
Posts: 3,500
Default purpose of subform?

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

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

"mike" wrote in message
...
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



  #5  
Old December 31st, 2008, 12:22 AM posted to microsoft.public.access.forms
mike
external usenet poster
 
Posts: 3,942
Default purpose of subform?

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" wrote:

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

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

"mike" wrote in message
...
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




  #6  
Old December 31st, 2008, 12:55 AM posted to microsoft.public.access.forms
Gina Whipp
external usenet poster
 
Posts: 3,500
Default purpose of subform?

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.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II
"mike" wrote in message
...
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" wrote:

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

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

"mike" wrote in message
...
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






  #7  
Old December 31st, 2008, 02:34 AM posted to microsoft.public.access.forms
mike
external usenet poster
 
Posts: 3,942
Default purpose of subform?

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.

  #8  
Old December 31st, 2008, 02:44 AM posted to microsoft.public.access.forms
mike
external usenet poster
 
Posts: 3,942
Default purpose of subform?

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

"mike" wrote:

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.

  #9  
Old December 31st, 2008, 02:45 AM posted to microsoft.public.access.forms
Gina Whipp
external usenet poster
 
Posts: 3,500
Default purpose of subform?

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

"I feel I have been denied critical, need to know, information!" - Tremors
II
"mike" wrote in message
...
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.



  #10  
Old December 31st, 2008, 02:47 AM posted to microsoft.public.access.forms
Gina Whipp
external usenet poster
 
Posts: 3,500
Default purpose of subform?

Cascading Updates set to Yes???

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II
"mike" wrote in message
...
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

"mike" wrote:

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.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:29 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.