Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Search function and reports

Discussion in 'Information Technology' started by Frustrated, Jul 28, 2006.

  1. Frustrated

    Frustrated
    Expand Collapse
    Guest

    I have a group of reports that provide data on 618 payers and most of the
    reports require one page per payer. I created a form that opens immediately
    at start-up and there is command button for all of the available reports.
    The problem is that the end user must scroll through all the payers to find
    the one they want and sometimes this is in a meeting while the DBase is on
    the projector. I offered to create a combo box, but they do not want to have
    to scroll through all the payers either. Is there any way to use the search
    function on a report in Access? I would appreciate any help.
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Indian American Couple Donates For Sikh, Punjabi Culture Research In US Breaking News Aug 19, 2016
    Soul Search By Swarn Singh Bains Book Reviews & Editorials May 25, 2016
    Harpal Singh Kumar Cancer Researcher Knighted By Queen Elizabeth II Sikh Personalities Jan 5, 2016
    Movies Ashdoc's Movie Review---dozakh In Search Of Heaven ( 2015 ) Theatre, Movies & Cinema Apr 6, 2015
    Research Shows NFL Quarterbacks Play Better With Beards Business, Lifestyle & Leisure Nov 23, 2014

  3. Michael H

    Michael H
    Expand Collapse
    Guest

    Hi.

    I could be wrong, but I don't believe there is any way to search the data in
    a previewed report.

    I think your idea of a ComboBox containing Payer names is a good one. A
    user would not have to scroll through all the items in the ComboBox to find
    the desired one. As long as the items in the ComboBox are sorted in some
    way, a user can type in the first few letters of the Payer's name, and come
    reasonable close to the desired Payer, if not directly to it.

    You can then direct your report to show only the selected payer by doing
    something like what is suggested here:
    http://www.mvps.org/access/reports/rpt0002.htm
    Just modify the strWhere line so that it gets criteria from the Payer
    ComboBox.

    I hope this helps to alleviate your frustration.

    -Michael



    "Frustrated" wrote:

    > I have a group of reports that provide data on 618 payers and most of the
    > reports require one page per payer. I created a form that opens immediately
    > at start-up and there is command button for all of the available reports.
    > The problem is that the end user must scroll through all the payers to find
    > the one they want and sometimes this is in a meeting while the DBase is on
    > the projector. I offered to create a combo box, but they do not want to have
    > to scroll through all the payers either. Is there any way to use the search
    > function on a report in Access? I would appreciate any help.
     
  4. xRoachx

    xRoachx
    Expand Collapse
    Guest

    Hi -- THis is more of question regarding the end user but how do they expect
    to find the payer without using criteria? Are you to will the right report
    open? Anyhow, a combo box has an auto complete feature that will search
    through the combo box and select items as you type. Not much to it.

    You can use this combo box on a form to filter the report for the desired
    criteria. For example, I use the following code to open and filter a form or
    report based on the user selections (Watch for word wrap!):

    Private Sub cmdLookup_Click()

    Dim strProjectID As String
    Dim strDesignSR As String
    Dim strDeploySR As String
    Dim strServerID As String
    Dim strWhere As String

    'Populate the project name lookup
    '
    If IsNull(cboProjectName) Then
    strProjectID = "Like '*'"
    Else
    strProjectID = "= " & Me.cboProjectName.Value & ""
    End If

    'Populate the design SR lookup
    '
    If IsNull(cboDesignSR) Then
    strDesignSR = "Like '*'"
    Else
    strDesignSR = "='" & Me.cboDesignSR.Value & "'"
    End If

    'Populate the deploy SR
    '
    If IsNull(cboDeploySR) Then
    strDeploySR = "Like '*'"
    Else
    strDeploySR = "='" & Me.cboDeploySR.Value & "'"
    End If

    'Populate the where clause
    '
    If IsNull(cboServerName) Then
    strWhere = "lngProjectID " & strProjectID & _
    " AND strDesignSR " & strDesignSR & _
    " AND strDeploySR " & strDeploySR
    Else
    strServerID = "= " & cboServerName.Value & ""

    strWhere = "SELECT strServerName " & _
    "FROM tblServerInformation " & _
    "WHERE lngServerID " & strServerID & " " & _
    "AND lngProjectID " & strProjectID
    End If

    Debug.Print strWhere

    Select Case fraOptions.Value
    Case 1
    'Open the form with the WHERE clause populated
    '
    DoCmd.OpenForm "frmProjectInformation", acNormal, , strWhere,
    acFormEdit, acWindowNormal

    'Close the lookup form
    '
    DoCmd.Close acForm, "frmProjectServerLookup", acSaveNo
    Case 2
    'Open the report with the WHERE clause populated
    '
    DoCmd.OpenReport "rptProjects", acViewPreview, , strWhere,
    acWindowNormal
    DoCmd.RunCommand acCmdZoom100

    'Close the lookup form
    '
    DoCmd.Close acForm, "frmProjectServerLookup", acSaveNo
    End Select

    End Sub

    "Frustrated" wrote:

    > I have a group of reports that provide data on 618 payers and most of the
    > reports require one page per payer. I created a form that opens immediately
    > at start-up and there is command button for all of the available reports.
    > The problem is that the end user must scroll through all the payers to find
    > the one they want and sometimes this is in a meeting while the DBase is on
    > the projector. I offered to create a combo box, but they do not want to have
    > to scroll through all the payers either. Is there any way to use the search
    > function on a report in Access? I would appreciate any help.
     
  5. Frustrated

    Frustrated
    Expand Collapse
    Guest

    You are right, I do not have a magic wand although I wish I did. I thought
    the combo box was the only solution and that has been confirmed. So, THANKS
    for the assistance. I am not sure what I do without this discussion group!!!!

    "xRoachx" wrote:

    > Hi -- THis is more of question regarding the end user but how do they expect
    > to find the payer without using criteria? Are you to will the right report
    > open? Anyhow, a combo box has an auto complete feature that will search
    > through the combo box and select items as you type. Not much to it.
    >
    > You can use this combo box on a form to filter the report for the desired
    > criteria. For example, I use the following code to open and filter a form or
    > report based on the user selections (Watch for word wrap!):
    >
    > Private Sub cmdLookup_Click()
    >
    > Dim strProjectID As String
    > Dim strDesignSR As String
    > Dim strDeploySR As String
    > Dim strServerID As String
    > Dim strWhere As String
    >
    > 'Populate the project name lookup
    > '
    > If IsNull(cboProjectName) Then
    > strProjectID = "Like '*'"
    > Else
    > strProjectID = "= " & Me.cboProjectName.Value & ""
    > End If
    >
    > 'Populate the design SR lookup
    > '
    > If IsNull(cboDesignSR) Then
    > strDesignSR = "Like '*'"
    > Else
    > strDesignSR = "='" & Me.cboDesignSR.Value & "'"
    > End If
    >
    > 'Populate the deploy SR
    > '
    > If IsNull(cboDeploySR) Then
    > strDeploySR = "Like '*'"
    > Else
    > strDeploySR = "='" & Me.cboDeploySR.Value & "'"
    > End If
    >
    > 'Populate the where clause
    > '
    > If IsNull(cboServerName) Then
    > strWhere = "lngProjectID " & strProjectID & _
    > " AND strDesignSR " & strDesignSR & _
    > " AND strDeploySR " & strDeploySR
    > Else
    > strServerID = "= " & cboServerName.Value & ""
    >
    > strWhere = "SELECT strServerName " & _
    > "FROM tblServerInformation " & _
    > "WHERE lngServerID " & strServerID & " " & _
    > "AND lngProjectID " & strProjectID
    > End If
    >
    > Debug.Print strWhere
    >
    > Select Case fraOptions.Value
    > Case 1
    > 'Open the form with the WHERE clause populated
    > '
    > DoCmd.OpenForm "frmProjectInformation", acNormal, , strWhere,
    > acFormEdit, acWindowNormal
    >
    > 'Close the lookup form
    > '
    > DoCmd.Close acForm, "frmProjectServerLookup", acSaveNo
    > Case 2
    > 'Open the report with the WHERE clause populated
    > '
    > DoCmd.OpenReport "rptProjects", acViewPreview, , strWhere,
    > acWindowNormal
    > DoCmd.RunCommand acCmdZoom100
    >
    > 'Close the lookup form
    > '
    > DoCmd.Close acForm, "frmProjectServerLookup", acSaveNo
    > End Select
    >
    > End Sub
    >
    > "Frustrated" wrote:
    >
    > > I have a group of reports that provide data on 618 payers and most of the
    > > reports require one page per payer. I created a form that opens immediately
    > > at start-up and there is command button for all of the available reports.
    > > The problem is that the end user must scroll through all the payers to find
    > > the one they want and sometimes this is in a meeting while the DBase is on
    > > the projector. I offered to create a combo box, but they do not want to have
    > > to scroll through all the payers either. Is there any way to use the search
    > > function on a report in Access? I would appreciate any help.
     
  6. xRoachx

    xRoachx
    Expand Collapse
    Guest

    These boards are great and have got me through several DBs and applications!

    "Frustrated" wrote:

    > You are right, I do not have a magic wand although I wish I did. I thought
    > the combo box was the only solution and that has been confirmed. So, THANKS
    > for the assistance. I am not sure what I do without this discussion group!!!!
    >
    > "xRoachx" wrote:
    >
    > > Hi -- THis is more of question regarding the end user but how do they expect
    > > to find the payer without using criteria? Are you to will the right report
    > > open? Anyhow, a combo box has an auto complete feature that will search
    > > through the combo box and select items as you type. Not much to it.
    > >
    > > You can use this combo box on a form to filter the report for the desired
    > > criteria. For example, I use the following code to open and filter a form or
    > > report based on the user selections (Watch for word wrap!):
    > >
    > > Private Sub cmdLookup_Click()
    > >
    > > Dim strProjectID As String
    > > Dim strDesignSR As String
    > > Dim strDeploySR As String
    > > Dim strServerID As String
    > > Dim strWhere As String
    > >
    > > 'Populate the project name lookup
    > > '
    > > If IsNull(cboProjectName) Then
    > > strProjectID = "Like '*'"
    > > Else
    > > strProjectID = "= " & Me.cboProjectName.Value & ""
    > > End If
    > >
    > > 'Populate the design SR lookup
    > > '
    > > If IsNull(cboDesignSR) Then
    > > strDesignSR = "Like '*'"
    > > Else
    > > strDesignSR = "='" & Me.cboDesignSR.Value & "'"
    > > End If
    > >
    > > 'Populate the deploy SR
    > > '
    > > If IsNull(cboDeploySR) Then
    > > strDeploySR = "Like '*'"
    > > Else
    > > strDeploySR = "='" & Me.cboDeploySR.Value & "'"
    > > End If
    > >
    > > 'Populate the where clause
    > > '
    > > If IsNull(cboServerName) Then
    > > strWhere = "lngProjectID " & strProjectID & _
    > > " AND strDesignSR " & strDesignSR & _
    > > " AND strDeploySR " & strDeploySR
    > > Else
    > > strServerID = "= " & cboServerName.Value & ""
    > >
    > > strWhere = "SELECT strServerName " & _
    > > "FROM tblServerInformation " & _
    > > "WHERE lngServerID " & strServerID & " " & _
    > > "AND lngProjectID " & strProjectID
    > > End If
    > >
    > > Debug.Print strWhere
    > >
    > > Select Case fraOptions.Value
    > > Case 1
    > > 'Open the form with the WHERE clause populated
    > > '
    > > DoCmd.OpenForm "frmProjectInformation", acNormal, , strWhere,
    > > acFormEdit, acWindowNormal
    > >
    > > 'Close the lookup form
    > > '
    > > DoCmd.Close acForm, "frmProjectServerLookup", acSaveNo
    > > Case 2
    > > 'Open the report with the WHERE clause populated
    > > '
    > > DoCmd.OpenReport "rptProjects", acViewPreview, , strWhere,
    > > acWindowNormal
    > > DoCmd.RunCommand acCmdZoom100
    > >
    > > 'Close the lookup form
    > > '
    > > DoCmd.Close acForm, "frmProjectServerLookup", acSaveNo
    > > End Select
    > >
    > > End Sub
    > >
    > > "Frustrated" wrote:
    > >
    > > > I have a group of reports that provide data on 618 payers and most of the
    > > > reports require one page per payer. I created a form that opens immediately
    > > > at start-up and there is command button for all of the available reports.
    > > > The problem is that the end user must scroll through all the payers to find
    > > > the one they want and sometimes this is in a meeting while the DBase is on
    > > > the projector. I offered to create a combo box, but they do not want to have
    > > > to scroll through all the payers either. Is there any way to use the search
    > > > function on a report in Access? I would appreciate any help.
     

Share This Page