How Do I Get a Dynamic "Single Person" Report in Access 2003?

[Guest]

I have a database in Access 2003 with a number of tables, queries and
reports.

One of the reports gives a listing of all people in the database and what
functions they participate in.

How do I set up a special report (or query) so that I can enter an
individual's name and then get this report only for that person and not for
everyone in the database?

I'm sorry if this seems overly simple, but I'm stumped.

Thanks,
Kit Marty

 

[Rick Brandt]

I have a database in Access 2003 with a number of tables, queries and
reports.

One of the reports gives a listing of all people in the database and
what functions they participate in.

How do I set up a special report (or query) so that I can enter an
individual's name and then get this report only for that person and
not for everyone in the database?

I'm sorry if this seems overly simple, but I'm stumped.

How do you identify a single person? Do you use first and last name or do you
have some sort of ID value assigned to each one? For the puprose of this
example I will assume that you have a number assigned to each person. I will
call this field [PersonID].

On a form I would build a ComboBox that displayed the names but actually
contained the value of the PersonID after making a selection. Then I would have
a button on the form that would open the report with this code...

DoCmd.OpenReport "ReportName", acViewPreview,,"PersonID = " & Me!PersonID

The statement above opens the report while simultaneously applying a filter on
the PersonID value that was chosen in the ComboBox.

 

[tina]

you can create a form, with a combo box that lists all the people in the
database (presumably from a People table, or an Employees table, or a
Clients table, whatever), so the user can select a specific person. i'll
call it cboPeople. add a command button to run the report. in the command
button's Click event, add the followin code, as

If IsNull(Me!cboPeople) Then
DoCmd.OpenReport "ReportName"
Else
DoCmd.OpenReport "ReportName", , , "PersonIDField = " _
& Me!cboPeople
End If

if the PersonIDField is text rather than numeric data type, then the syntax
will be

DoCmd.OpenReport "ReportName", , , "PersonIDField = '" _
& Me!cboPeople & "'"

substitute the correct name of the report, as well as the correct name of
the primary key field in your "People" table. if the user selects a person
from the combo box droplist, then the report will open for that person only;
otherwise, the report will display all the people.

hth


"Kit Marty (CHM Consulting)"

 

[Guest]

Hi Kit Marty,

Try out this tip that I wrote on this subject:

How to print only one page of a multipage report
http://www.access.qbuilt.com/html/reports.html#PrintOnePgOfRpt

So, you just need to have a form that displays your people. The form's
recordsource must include a primary key field for the people table. Then, you
add a command button to the form that will open the same report, except that
you include the optional WhereCondition argument. Something like this
(example shown below is for a numeric primary key):

Private Sub cmdPrint_Click()
On Error GoTo ProcError

DoCmd.OpenReport "rptPeople", acPreview, _
WhereCondition:="pkPersonID = " & Me.pkPersonID

ExitProc:
Exit Sub
ProcError:
Select Case Err.Number
Case 2501 'Report open cancelled
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdPrint_Click..."
End Select
Resume ExitProc
End Sub


where rptPeople is the name of the report, and pkPersonID is the name of the
field that is the primary key. Make the appropriate substitutions.

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

I have a database in Access 2003 with a number of tables, queries and
reports.

One of the reports gives a listing of all people in the database and what
functions they participate in.

How do I set up a special report (or query) so that I can enter an
individual's name and then get this report only for that person and not for
everyone in the database?

I'm sorry if this seems overly simple, but I'm stumped.

Thanks,
Kit Marty