On May 11, 4:10 pm, "Wolfgang Kais"
wrote:
Hello Alan.



Alan wrote:
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!cb oStuID
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 & "'"

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