Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filter Form (2000)

    Attached is a sample database that contains a form that I would like to copy into another database. The form is designed (when opened) to show all the reports in a database. My question is - Where in the code behind the form can I indicate the reports I want to show in the form. I just want to list the reports I want to appear when the form is opened not all the reports. How can I (and where can I) adjust the code. Thanks for any help.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filter Form (2000)

    If you open the form in design view, select the reports list box and activate the Data tab of the Properties window, you will see that the Row Source Type property is set to Value List and that the Row Source property lists the available reports. This list has been created in the On Load event of the form:
    <pre> Set rpts = CurrentProject.AllReports
    For Each rpt In rpts
    strList = rpt.Name & ";" & strList
    Next
    Me!lstReports.RowSourceType = "Value List"
    Me!lstReports.RowSource = strList
    </pre>

    If you do not want to list ALL reports, you must either remove the Form_Load code and type in the list of reports (separated by semicolons) yourself, or you must have some way of distinguishing the reports you want to list from those you do not want to list.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter Form (2000)

    I took out the Form_Load code and entered the report names in manually. That works.
    When I click on a name of a report, a list of fields in the report appears. I click on a field and a list of values for that field appears.
    The problem:
    Why would the number fields values appear (they are defined as text) but the values for a field like Rep Name do not appear. All fields that have numbers have values showing but when I click on a field that doesn't have numbers for values, nothing appears in the values text box. Any ideas why?

    This happens in the database that I copy the form into. The demo database attached shows all values for all fields.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filter Form (2000)

    The problem is not specifically for text fields, but for fields whose name contains spaces (or unusual characters). The code should ensure that field names are enclosed in square brackets [ ] to avoid problems with spaces in field names. Replace the lstFields_Click procedure by this:

    Private Sub lstFields_Click()
    Dim rpt As New Report
    Dim rptString As String
    Dim strRecSource As String
    Dim strSQL As String

    On Error GoTo errHandler

    If lstReports.Value = False Then
    MsgBox "Please select a report", vbOKOnly, "Error"
    Else
    rptString = Me!lstReports.Value
    DoCmd.OpenReport rptString, acViewPreview

    Set rpt = Reports(rptString)
    strRecSource = rpt.RecordSource
    DoCmd.Close acReport, rptString

    If Left(strRecSource, 6) = "SELECT" Then
    strSQL = "SELECT DISTINCT [" & Me!lstFields.Value & _
    "] FROM (" & Mid(strRecSource, 1, Len(strRecSource) - 2) & _
    ") ORDER BY [" & lstFields & "]"
    Else
    strSQL = "SELECT DISTINCT [" & Me!lstFields.Value & _
    "] FROM [" & strRecSource & "] ORDER BY [" & lstFields & "]"
    End If
    Me!lstValues.RowSource = strSQL
    Me!lstValues.Enabled = True
    End If

    ExitProc:
    Set rpt = Nothing
    Exit Sub

    errHandler:
    MsgBox Err.Number & ": " & Err.Description, , "Error"
    Resume ExitProc
    End Sub

    You will have to adapt cmdPrintPreview_Click in a similar way.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter Form (2000)

    Code removed by HansV - was already available in attachment.

    I am sorry Hans but I don't know where to adjust the cmdPrintPreview_click procedure. It doesn't look similar (to me) to the 1stFields_Click procedure.
    I don't know where to put the brackets??

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filter Form (2000)

    Replace this:

    If blnContinue Then
    strSQL = Me!lstFields.Value & " = " & _
    strDelimiter & lstValues.Value & _
    strDelimiter

    by this:

    If blnContinue Then
    strSQL = "[" & Me!lstFields.Value & "] = " & _
    strDelimiter & lstValues.Value & _
    strDelimiter

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter Form (2000)

    Thanks Hans - works perfectly.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filter Form (2000)

    I'm glad it works. The idea in each of these cases is to enclose the field name from lstFields in square brackets: "[" & Me!lstFields.Value & "]"

Posting Permissions

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