sorting and grouping in linked forms (2000)

Topic

New Reply

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.

The situation:
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

Diederik

Reply | Quote

Replies

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

If Not IsNull(Me.cboTransport) Then
strFilter = strFilter & ” And TransportID = ” & Me.cboTransport
End If

‘ Get rid of first ” And ”
If Not (strFilter = “”) Then
strFilter = Mid(strFilter, 6)
End If

DoCmd.OpenReport “rptMyReport”, acViewPreview, , strFilter
Exit Sub

ErrHandler:
‘ Ignore error 2501 = report canceled
If Not (Err = 2501) Then
MsgBox Err.Description, vbExclamation
End If
End Sub

Reply | Quote