Subform of a Subform


A

Alan

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
 
Ad

Advertisements

W

Wolfgang Kais

Hello Alan.

Alan said:
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

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.
 
A

Alan

Hello Alan.



Alan said:
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

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
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 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 thesubform
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 to
change the controlsource property to a reference to the StuID textbox
in the suform (that you already created for ClassesStudents). Then,
create a secondsubformin 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
thesubformcontrol 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
subformto 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
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 thesubform
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.

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
 
W

Wolfgang Kais

Hello Alan.
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
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
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
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 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
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 secondsubform.
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.
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.

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.
 
A

Alan

Hello Alan.


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
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
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
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
subformcontrol (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
to change the controlsource property to a reference to the StuID
textbox in the suform (that you already created for ClassesStudents).
Then, create a secondsubformin 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 thesubformcontrol 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
subformto 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
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 thesubform
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.
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.

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 ClassesStudentssubformis 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 Eventssubform, 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.

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
 
W

Wolfgang Kais

Hello Alan.
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
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 subformin datasheet view.
Create a subform in the main form that is based on the
ClassesStudents table mentioned above, displaying the StuID field.
If you followed 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 subform (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.
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.
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.

I have one final request. I changed the default view of the second
subform based on Events to be Continuous Forms rather than Datasheet.
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.

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 & "'"
 
Ad

Advertisements

A

Alan

Hello Alan.


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
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 subformin datasheet view.
Create asubformin the main form that is based on the
ClassesStudents table mentioned above, displaying the StuID field.
If you followed 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 thesubformcontrol (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 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
to change the controlsource property to a reference to the StuID
textbox in thesubform(that you already created for
ClassesStudents). Then, create a secondsubformin 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 thesubformcontrol 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
subformto 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 thesubform
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.
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.
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 ClassesStudentssubformis
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 Eventssubform, 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.
I have one final request. I changed the default view of the second
subformbased on Events to be Continuous Forms rather than Datasheet.
I have the secondsubformdisplaying 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 secondsubform'sheader. 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.

So you did not like the EventCode linking main form and secondsubform,
all right. You want an optional filter and placed a combo box in the
header of that secondsubformthat 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 & "'"

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top