Sub Reports

[Emma]

Hi I was looking at a database that had a subform I believe on the left of
the screen with a file listing, when you click on a file the right hand side
opens that form. I really like this design, is there any way to mimick it or
do I have to use a drop down menu instead of a file list?

 

[Ken Sheridan]

I'm assuming that by 'file' you don't mean a file as such, but rather a form
object in the current database, as its forms you seem to want to open.

This sounds rather like something I once did in the days before Doris Day
was a virgin with a form for editing technical 'glossaries', which were
really just lists of valid terms for a particular category of data. The way
it worked was by having a list box on the left, which listed all the
different categories, and a subform control on the right of a form. When a
category was selected in the list box the source for the subform control was
set to the relevant form which listed the terms for that category. I realize
now I could have done it a lot more efficiently by linking a single subform
based on a query so that it only showed the relevant rows, but I was pretty
green in those days! Nevertheless it serves to answer your question because
it did just what you have in mind.

The list box was based on a query on a table with two columns, Glossary and
Glossary_Form, the first column containing the categories listed in the list
box, the second the name of the form which goes with that category, so if
"Geology – Drift" is selected in the list box the form name is
"fsubGeology_Drift_Gloss". This form is then shown in the subform control
with the following code in the list box's AfterUpdate event procedure:

On Error GoTo Err_Handler

Dim strErrorMessage As String

' Make subform control visible if hidden
If Me!sfcGlossary.Visible = False Then
Me!sfcGlossary.Visible = True
End If

'Set source object for subform control
Me!sfcGlossary.SourceObject = Me!lstFormList.Column(1)

Exit_Here:
Exit Sub

Err_Handler:
strErrorMessage = Err.Description & " (" & Err.Number & ")"
MsgBox strErrorMessage, vbExclamation, "Error"
Resume Exit_Here

where sfcGlossary is the name of the subform control, i.e. the control
which houses the subform, and lstFormList is the name of the list box.

The list box's RowSource is:

SELECT Glossary, Glossary_Form
FROM Glossaries
ORDER BY Glossary;

and its ColumnCount property is 2. Note that the Column property is
zero-based so Column(1) in the above code refers to the second column,
Glossary_Form. Its Column Widths property is 8cm;0cm (Access will convert
this to inches if you are using imperial units).

While in the above example the subforms are all very similar it would work
with subforms which have no connection with each other. All you need is a
table of descriptive terms to appear in the list box and a form name to go
with each one. The only limitation is that they all need to fit into the
same size subform control on the main form; unless you want to be really
fancy, that is, and manipulate its size at runtime de4pending on which source
for the subform control is selected.

Ken Sheridan
Stafford, England

 

[Emma]

Hi Ken,

I'm having trouble with the setup as it keeps telling me that it can not
find TripNameTable, which is a table with two columns the TripName and
FormName. Here's my code based on yours:

Option Compare Database

Private Sub List3_AfterUpdate()
On Error GoTo Err_Handler

Dim strErrorMessage As String

'Make subform control visible if hidden

If Me!PassengerInformation.Visible = False Then
Me!PassengerInformation.Visible = True
End If

'Set Source object for subform control
Me!PassengerInformation.SourceObject = Me!TripNameTable.Column(1)

Exit_Here:
Exit Sub

Err_Handler:
strErrorMessage = Err.Description & " (" & Err.Number & ")"
MsgBox strErrorMessage, vbExclamation, "Emily's Error"
Resume Exit_Here


End Sub

 

[Emma]

Hi Ken I just caught my mistake, it should be Me!List3.Column(1). It's
working, now my big question is how do I create the subforms for each trip?
Do I have to manually go in and create each form or is there some way I can
use my original subform and change it for each trip? I see what you mean by
using a query, just not sure where to start, if the user enters a new
tripname then the formname should populate itself based on something like
tripname_date. Curious to know what you think, this is really cool.

 

[Ken Sheridan]

You probably don't need to change the source object of the subform
control at all in fact. Unfortunately you haven't given us a lot of
information about what you want to do here in terms of the actual
database content, so we are having to second guess things. In you
original post you said you wanted to open a form for each selection in
the list box, but it now sounds like you want to show different sets
of data depending on the selection in the list box, which is an
entirely different kettle of fish.

I think what you need here is a single subform based on a query which
will return the data on all trips. You can then simply link the
subform to the list box by setting the subform control's
LinkMasterFields property to the name of the list box, List3, and the
LinkChildFields property to the name of the field in the subform's
query which corresponds to the values in the list box, e.g. a field
TripName.

The list box will be set up rather differently for this as it will
draw its values from the data in the table, e.g. if you have a Trips
table with one row per Trip the RowSource would be:

SELECT TripName FROM Trips ORDER BY TripName;

If you don't have a separate Trips table, but only have mutiple
entries for eacg Trip in whatever table the subform uses then you can
make the RowSource:

SELECT DISTINCT TripName FROM YourTable ORDER BY TripName;

to return just one row per trip name in the list. In a well designed
database you should have a separate Trips table, however, as that
enables referential integrity to be enforced.

You then don't need to write any code at all. Selecting an item in
the list box will cause the subform to show the rows for that item
only, all done automatically by the linking mechanism.

Ken Sheridan
Stafford, England

 

[Emma]

Hi Ken,

There's not much to the database right now. I have 2 tables one for Trip
Names and the other for Passenger Information like name, address, phone. What
I would like is the sub form (on the right) with the passenger information to
be populated when the Trip Name is selected (on the left). I would like the
first person for the Trip to be displayed and below that a datasheet view of
all the passengers for that trip. I've been playing around with the
subdatasheet but haven't been successful at calculating a total for that
sheet alone as it's been giving me a grand total (you responded to this in
another posting I did). I know this is complicated, sorry.

Thanks Emma

 

[Ken Sheridan]

Emma:

Presumably the Passengers table has a foreign key column (field) to
reference the primary key of the trips table. You use this to link
the list box to a subform of passengers. Exactly how you do this
intends on what the two key columns are, so I'll cover both possible
scenarios:

1. If the primary key and foreign keys are the Trip Name text columns
then the RowSource of the list box would be like this:

SELECT [Trip Name] FROM [Trips] ORDER BY [Trip Name];

Place the list box in an unbound form and in the same form place a
subform, which can be in datasheet view, but datasheets, while cheap
and cheerful, are very limited in what you can do with them, so if you
want to show totals then you'll have to use a continuous form so you
can give it a form footer in which to include an unbound text box
control in which to compute the total . This subform will be based on
the Passengers table or on a query on the passengers table if you want
them sorted by name for instance.

The LinkMasterFields property of the subform control on the main form
will be the name of the list box, e.g. lstTrips or whatever you've
named it (don't just accept the default name Access gives it, change
the Name property to something more meaningful). The LinkChildFields
property will be the name of the foreign key Trip Name column in the
passengers table.

2. If the primary key of the Trips table is a 'surrogate' numeric
column, e.g. an autonumber called TripID or similar then the foreign
key column in passengers will be a number column, e.g. TripID, also.
In this scenario you need to set up the list box's properties
differently like this:

RowSource: SELECT [TripID], [Trip Name] FROM [Trips] ORDER BY
[Trip Name];

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access
will automatically convert the last one. The important thing is that
the first dimension is zero to hide the first column and that the
second is at least as wide as the combo box. You'll se the trip names
in the list box but when you select one its value will be the hidden
TripID.

The LinkMasterFields property of the subform control on the main form
will again be the name of the list box, e.g. lstTrips, but this time
the LinkChildFields property will be TripID, as that's the foreign key
column in the Passengers table.

You don't need to write any code at all for this to work. Simply
selecting a trip in the list box will cause the subform to be
requeried to show only the passengers for the selected trip. If
you've includ3ed a text box for the total in the footer this will also
update for the selected trip.

Ken Sheridan
Stafford, England