Results 1 to 2 of 2
2003-08-04, 20:52 #1
- Join Date
- Aug 2003
- amsterdam, Netherlands
- Thanked 0 Times in 0 Posts
sorting and grouping in linked forms (2000)
I am trying to find a way to create a feature i need for my users in the DB
Hope anyone can help me out.
DB with three tables:
A table with info about people
a table with info about travels
a table to combine both (who did which travel and when)
I want to create a way for the user to get a list of people by sorting on one of more fields from travels
E.G. I want to see al the people who have had a trip to russia (a value in field "destination" )
or I want to see al the people that had a trip with an airplane (a value in field "transportation")
and preferably I want to be able to make combinations.
the problem is that I could make a REPORT for every situation and then create a long list of possible combinations but that is not what I want.
what I want is a FORM where I can use one ore more comboboxes to get an instant list of all the people that have had a trip that meets the selected criteriumcriterea.
does anyone have any idea how to solve this?
Thanks in advance
2003-08-04, 21:33 #2
- Join Date
- Mar 2002
- Thanked 28 Times in 28 Posts
Re: sorting and grouping in linked forms (2000)
You could create a report based on a query that joins the three tables:
SELECT tblPersons.*, tblTravels.* FROM tblTravels INNER JOIN (tblPersons INNER JOIN tblPersonTravels ON tblPersons.PersonID = tblPersonTravels.PersonID) ON tblTravels.TravelID = tblPersonTravels.TravelID
Since one person can occur more than once in this query, group the report by PersonID (or by the person's name), and put all relevant info about the person in the group header.
Put combo boxes or list boxes on a form that present available destinations, means of transport etc., and a command button to open the report. Assemble a filter string for the report in the On Click code of the command button. Example (to be adapted to your situation (all ID's are assumed to be numeric):
Private Sub cmdOpenReport_Click()
Dim strFilter As String
On Error GoTo ErrHandler
If Not IsNull(Me.cboDestination) Then
strFilter = " And DestinationID = " & Me.cboDestination
If Not IsNull(Me.cboTransport) Then
strFilter = strFilter & " And TransportID = " & Me.cboTransport
' Get rid of first " And "
If Not (strFilter = "") Then
strFilter = Mid(strFilter, 6)
DoCmd.OpenReport "rptMyReport", acViewPreview, , strFilter
' Ignore error 2501 = report canceled
If Not (Err = 2501) Then
MsgBox Err.Description, vbExclamation