Search function and reports

[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.

 

[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

 

[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

 

[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!!!!

 

[Guest]

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