Results 1 to 2 of 2
  1. #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.

    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


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 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
    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

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts