Subform of a Subform

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alan - 08 May 2008 04:58 GMT

I have several years experience with Access queries and other
reporting uses but very little experience with forms designed for
inexperienced users. I am using Access 2002 with a database with the
following:

Tables Fields
Classes ClassNo
ClassName
StuID
StartDate
EndDate
Students StuID
Name
Address
Events EventNo
StuID
EventDate
EventCode
Comments

I want to set up a form that allows inexperienced users to review
related data from the above three tables. I want the main form to be
tied to the Classes table and allow the user to select from a combo
box the ClassNo to be viewed. Then, I want all the students in that
ClassNo to be presented in a subform in datasheet view. When the user
is on a particular student's record in that subform, I want another
subform below it to present a subform of events for that student in a
datasheet view. I also want the user to be able to filter the events
in the last subform to only show those with particular EventCodes
which the user selects with a combo box.

Is it possible to do what I want? Can someone outline the steps I need
to take? Will any coding be required to do this because my
understanding of Access modules and code is extremely limited.

Alan

Reply to this Message

Wolfgang Kais - 08 May 2008 13:09 GMT

Hello Alan.

> I have several years experience with Access queries and other
> reporting uses but very little experience with forms designed
[quoted text clipped - 15 lines]
> EventCode
> Comments

As an also experienced database developer, let me ask you this:
Instead of storing a StuID in the Classes table, wouldn't it be much
better to create an additional table ClassesStudents for a many-to-many
relationship between Classes and Students that contains the two columns
ClassNo and StuID?
For convienience, I suggest (although many people and MVPs hate this)
to make the StuID in that table a lookup field that displays the Name
column from the Students table (and stores StuID in a hidden column).

> I want to set up a form that allows inexperienced users to review
> related data from the above three tables. I want the main form to be
> tied to the Classes table and allow the user to select from a combo
> box the ClassNo to be viewed.

Create the main form based on the Classes table displaying all the
information that is desired (ClassName, STartDate, EndDate).
The create a ComboBox in the form header that allows the user to go to
the selected class (Third option in the ComboBox wizard).

> Then, I want all the students in that ClassNo to be presented in a
> subform in datasheet view.

Create a subform in the main form that is based on the ClassesStudents
table mentioned above, disüplaying the StuID field. If you followd my
advice from above, this field will automatically be displayed in a
combo box that lists all the names of students. Enter ClassNo in both
the LinkChildFields and LinkMasterFields properties of the subform
control (if it is not entered automatically) and specify datasheet
view as the default view of this form.

> When the user is on a particular student's record in that subform,
> I want another subform below it to present a subform of events for
> that student in a datasheet view.

Create a textbox in the main form (txtStuID), make it invisible (set
it's visible property to false) and use the Expression Builder to
change the controlsource property to a reference to the StuID textbox
in the suform (that you already created for ClassesStudents). Then,
create a second subform in the main form based on the Events table
that displays all relevant information (EventDate and Comments).
Set the default view to Datasheet. Set the LinkChildFields property of
the subform control to StuID and set the LinkMasterFields property to
[txtStuID].[Value]. This will filter all the Events for the selected
student.

> I also want the user to be able to filter the events in the last
> subform to only show those with particular EventCodes which the
> user selects with a combo box.

Create a combo box in the main form, maybe above the second subform.
Make it an unbound combo box (value stored in the control for later
reference, not stored in the database), make it display all possible
event codes. Assume you name it cboEventCode.
Then, add additional information in the two properties of the subform
control, so that they read:
LinkChildFields: StuID;EventCode
LinkMasterFields: [txtStuID].[Value];[cboEventCode].[Value]

> Is it possible to do what I want? Can someone outline the steps I
> need to take? Will any coding be required to do this because my
> understanding of Access modules and code is extremely limited.

I hope that this was helful, and until here, there's no code involved
that you had to create yourself. The ComboBox wizard (for the Class
selection in the form header) created a small VBA procedure for you,
and it is important, that you do not rename that combo box.

Signature

Regards,
Wolfgang

Reply to this Message

Alan - 08 May 2008 15:07 GMT

On May 8, 7:09 am, "Wolfgang Kais" <w.k...@discussions.microsoft.com>
wrote:

> Hello Alan.
>
[quoted text clipped - 88 lines]
> Regards,
> Wolfgang

Wolfgang, thanks so much for your most helpful reply. I have the
following additional comments and questions:

1. Your comments about the poor design of my Classes table were spot
on. To shorten my post, I combined two actual tables in my database,
Classes and Class Rolls (what you called ClassesStudents), and called
it Classes. I apologize for the confusion and appreciate your pointing
out the apparent design flaw.
2. My StuID field is comprised of the first 3 letters of the last
name, the first 3 letters of the first name and a numeric "tie-
breaker." Although I don't have that field set up as a defined lookup
field, I do use a combo box based on a sorted query to allow the user
to pick the appropriate StuID based on name on any forms involving
adding or editing students.
3. Does it matter whether the combo box on the main form where the
user selects the ClassNo to be viewed is bound or unbound? Are there
advantages or disadvantages of each?
4. Concerning the invisible textbox on the main form named txtStuID,
if the StuID combo box on my ClassesStudents subform is named
cboStuID, should the controlsource property of this textbox be
something like =cboStuID?
5. Concerning the Events subform, you say I should set the
LinkMasterFields property to
[txtStuID].[Value]. I assume I literally type in the word Value inside
the square brackets? Is that one of those special words used to
represent the underlying control's value? I didn't know you could do
that.

Thanks again for your help. I would never have been able to work out
what I needed to accomplish my goals.

Alan

Reply to this Message

Wolfgang Kais - 08 May 2008 21:48 GMT

Hello Alan.

>>> I have several years experience with Access queries and other
>>> reporting uses but very little experience with forms designed
[quoted text clipped - 15 lines]
>>> EventCode
>>> Comments

>> As an also experienced database developer, let me ask you this:
>> Instead of storing a StuID in the Classes table, wouldn't it be
[quoted text clipped - 5 lines]
>> the Name column from the Students table (and stores StuID in a
>> hidden column).

>>> I want to set up a form that allows inexperienced users to review
>>> related data from the above three tables. I want the main form to
>>> be tied to the Classes table and allow the user to select from a
>>> combo box the ClassNo to be viewed.

>> Create the main form based on the Classes table displaying all the
>> information that is desired (ClassName, STartDate, EndDate).
>> The create a ComboBox in the form header that allows the user to go
>> to the selected class (Third option in the ComboBox wizard).

>>> Then, I want all the students in that ClassNo to be presented in a
>>> subformin datasheet view.

>> Create asubformin the main form that is based on the ClassesStudents
>> table mentioned above, disüplaying the StuID field. If you followd
[quoted text clipped - 3 lines]
>> subform control (if it is not entered automatically) and specify
>> datasheet view as the default view of this form.

>>> When the user is on a particular student's record in thatsubform,
>>> I want anothersubformbelow it to present asubformof events for
>>> that student in a datasheet view.

>> Create a textbox in the main form (txtStuID), make it invisible
>> (set it's visible property to false) and use the Expression Builder
[quoted text clipped - 6 lines]
>> to [txtStuID].[Value]. This will filter all the Events for the
>> selected student.

>>> I also want the user to be able to filter the events in the last
>>> subform to only show those with particular EventCodes which the
>>> user selects with a combo box.

>> Create a combo box in the main form, maybe above the secondsubform.
>> Make it an unbound combo box (value stored in the control for later
[quoted text clipped - 4 lines]
>> LinkChildFields: StuID;EventCode
>> LinkMasterFields: [txtStuID].[Value];[cboEventCode].[Value]

>>> Is it possible to do what I want? Can someone outline the steps I
>>> need to take? Will any coding be required to do this because my
>>> understanding of Access modules and code is extremely limited.

>> I hope that this was helful, and until here, there's no code involved
>> that you had to create yourself. The ComboBox wizard (for the Class
>> selection in the form header) created a small VBA procedure for you,
>> and it is important, that you do not rename that combo box.

> Wolfgang, thanks so much for your most helpful reply. I have the
> following additional comments and questions:
[quoted text clipped - 4 lines]
> it Classes. I apologize for the confusion and appreciate your pointing
> out the apparent design flaw.

Uh, I see. Ok.

> 2. My StuID field is comprised of the first 3 letters of the last
> name, the first 3 letters of the first name and a numeric "tie-
> breaker." Although I don't have that field set up as a defined lookup
> field, I do use a combo box based on a sorted query to allow the user
> to pick the appropriate StuID based on name on any forms involving
> adding or editing students.

Good.

> 3. Does it matter whether the combo box on the main form where the
> user selects the ClassNo to be viewed is bound or unbound? Are there
> advantages or disadvantages of each?

You mean the ClassNo in the form header. This should be unbound and
named differently from ClassNo (for example cboClassNo). This combo is
used solely for navigation and not for changing the ClassNo of a Class
(which should not work if ClassNo was the primary key), therefore
this combo box has to be unbound. Don't worry, the wizard knows that.

> 4. Concerning the invisible textbox on the main form named txtStuID,
> if the StuID combo box on my ClassesStudents subform is named
> cboStuID, should the controlsource property of this textbox be
> something like =cboStuID?

No, it should be something like
=Forms!NameOfMainForm!NameOfSubformControl.Form!cboStuID

> 5. Concerning the Events subform, you say I should set the
> LinkMasterFields property to
> [txtStuID].[Value]. I assume I literally type in the word Value
> inside the square brackets? Is that one of those special words used
> to represent the underlying control's value? I didn't know you
> could do that.

Sorry, I have to revise this, the Value property did not work. Just
use the name of the control, LinkMasterFields: txtStuID
So finally:
LinkChildFields: StuID;EventCode
LinkMasterFields: txtStuID;cboEventCode

> Thanks again for your help. I would never have been able to work out
> what I needed to accomplish my goals.

You are wellcome.

Signature

Regards,
Wolfgang

Reply to this Message

Alan - 11 May 2008 02:19 GMT

On May 8, 3:48 pm, "Wolfgang Kais" <w.k...@discussions.microsoft.com>
wrote:

> Hello Alan.
>
[quoted text clipped - 133 lines]
> Regards,
> Wolfgang

Wolfgang, thanks so much for your assistance with this project. I've
learned a great deal about forms using your suggestions.

I have one final request. I changed the default view of the second
subform based on Events to be Continuous Forms rather than Datasheert.
I have the second subform displaying all Event records that match the
invisible txtStuID control on the main form as you suggested. I want
to give the user the option to filter those displayed records based on
a selected EventCode value from a combo box named CboEventCode that I
put in that second subform's header. However, the combo box wizard
does not provide a filtering option. I figure this can be done via
some code in the After Update event of the combo but I've been unable
to figure out how to do it with either code or macros. Is what I want
to do possible? Can you help? Thanks.

Alan

Reply to this Message

Wolfgang Kais - 11 May 2008 22:10 GMT

Hello Alan.

>>>>> I have several years experience with Access queries and other
>>>>> reporting uses but very little experience with forms designed
[quoted text clipped - 15 lines]
>>>>> EventCode
>>>>> Comments

>>>> As an also experienced database developer, let me ask you this:
>>>> Instead of storing a StuID in the Classes table, wouldn't it be
[quoted text clipped - 5 lines]
>>>> the Name column from the Students table (and stores StuID in a
>>>> hidden column).

>>>>> I want to set up a form that allows inexperienced users to review
>>>>> related data from the above three tables. I want the main form to
>>>>> be tied to the Classes table and allow the user to select from a
>>>>> combo box the ClassNo to be viewed.

>>>> Create the main form based on the Classes table displaying all
>>>> the information that is desired (ClassName, STartDate, EndDate).
>>>> The create a ComboBox in the form header that allows the user to
>>>> go to the selected class (Third option in the ComboBox wizard).

>>>>> Then, I want all the students in that ClassNo to be presented
>>>>> in a subformin datasheet view.

>>>> Create a subform in the main form that is based on the
>>>> ClassesStudents table mentioned above, displaying the StuID field.
[quoted text clipped - 4 lines]
>>>> entered automatically) and specify datasheet view as the default
>>>> view of this form.

>>>>> When the user is on a particular student's record in that
>>>>> subform, I want another subform below it to present a subform of
>>>>> events for that student in a datasheet view.

>>>> Create a textbox in the main form (txtStuID), make it invisible
>>>> (set it's visible property to false) and use the Expression Builder
[quoted text clipped - 6 lines]
>>>> set the LinkMasterFields property to [txtStuID].[Value]. This will
>>>> filter all the Events for the selected student.

>>>>> I also want the user to be able to filter the events in the last
>>>>> subform to only show those with particular EventCodes which the
>>>>> user selects with a combo box.

>>>> Create a combo box in the main form, maybe above the second
>>>> subform. Make it an unbound combo box (value stored in the control
[quoted text clipped - 4 lines]
>>>> LinkChildFields: StuID;EventCode
>>>> LinkMasterFields: [txtStuID].[Value];[cboEventCode].[Value]

>>>>> Is it possible to do what I want? Can someone outline the steps I
>>>>> need to take? Will any coding be required to do this because my
>>>>> understanding of Access modules and code is extremely limited.

>>>> I hope that this was helful, and until here, there's no code involved
>>>> that you had to create yourself. The ComboBox wizard (for the Class
>>>> selection in the form header) created a small VBA procedure for you,
>>>> and it is important that you do not rename that combo box.

>>> Wolfgang, thanks so much for your most helpful reply. I have the
>>> following additional comments and questions:
[quoted text clipped - 5 lines]
>>> confusion and appreciate your pointing out the apparent design
>>> flaw.

>> Uh, I see. Ok.

>>> 2. My StuID field is comprised of the first 3 letters of the last
>>> name, the first 3 letters of the first name and a numeric "tie-
>>> breaker." Although I don't have that field set up as a defined
>>> lookup field, I do use a combo box based on a sorted query to
>>> allow the user to pick the appropriate StuID based on name on any
>>> forms involving adding or editing students.

> Good.

>>> 3. Does it matter whether the combo box on the main form where the
>>> user selects the ClassNo to be viewed is bound or unbound? Are
>>> there advantages or disadvantages of each?

>> You mean the ClassNo in the form header. This should be unbound and
>> named differently from ClassNo (for example cboClassNo). This combo
>> is used solely for navigation and not for changing the ClassNo of a
>> Class (which should not work if ClassNo was the primary key),
>> therefore this combo box has to be unbound.
>> Don't worry, the wizard knows that.

>>> 4. Concerning the invisible textbox on the main form named
>>> txtStuID, if the StuID combo box on my ClassesStudents subform is
>>> named cboStuID, should the controlsource property of this textbox
>>> be something like =cboStuID?

>> No, it should be something like
>> =Forms!NameOfMainForm!NameOfSubformControl.Form!cboStuID

>>> 5. Concerning the Events subform, you say I should set the
>>> LinkMasterFields property to
>>> [txtStuID].[Value]. I assume I literally type in the word Value
>>> inside the square brackets? Is that one of those special words
>>> used to represent the underlying control's value? I didn't know
>>> you could do that.

>> Sorry, I have to revise this, the Value property did not work.
>> Just use the name of the control, LinkMasterFields: txtStuID
>> So finally:
>> LinkChildFields: StuID;EventCode
>> LinkMasterFields: txtStuID;cboEventCode

>>> Thanks again for your help. I would never have been able to work
>>> out what I needed to accomplish my goals.

>> You are wellcome.

> Wolfgang, thanks so much for your assistance with this project. I've
> learned a great deal about forms using your suggestions.
[quoted text clipped - 10 lines]
> to figure out how to do it with either code or macros. Is what I want
> to do possible? Can you help? Thanks.

So you did not like the EventCode linking main form and second subform,
all right. You want an optional filter and placed a combo box in the
header of that second subform that is displayed as continuous form now,
that's perfect. To make the filter work, we indeed have to use some
VBA code or a macro, where I prefer VBA.
In the properties window of that cboEventCode (it should be unbound!),
look for the AfterUpdate event property, click in that property and
click on the period button beside the property. From the dialog box
that appears, select "Code-Generator" and click "Ok". Edit the event
procedure to look like this:

Private Sub cboEventCode_AfterUpdate()
If IsNull(Me.cboEventCode) Then
Me.FilterOn = False
Me.Filter = ""
Else
Me.Filter = "EventCode=" & Me.cboEventCode
Me.FilterOn = True
End If
End Sub

In case that EventCode was not numeric but text, the one line must be:
Me.Filter = "EventCode='" & Me.cboEventCode & "'"

Signature

Regards,
Wolfgang

Reply to this Message

Alan - 11 May 2008 23:22 GMT

On May 11, 4:10 pm, "Wolfgang Kais" <w.k...@discussions.microsoft.com>
wrote:

> Hello Alan.
>
[quoted text clipped - 163 lines]
> Regards,
> Wolfgang

Wolfgang, I don't mean to gush but you're amazing. The form and
subforms are working perfectly and I feel as if I've made substantial
progress in climbing the form's learning curve. Thanks so much for all
your help.

Alan

Reply to this Message