Report based on a record [Reports]

Prev: Any way to force multiple details on a label?
Next: Report with Subreports

From: blanch2010 on 8 Feb 2010 13:45

How can I make a report, when it's opened, ask for user input?

I need to have the user select a name from a list & report on that name's
record.

Is this done in the query for the report or the report itself?

Thank You
Don

From: Duane Hookom on 8 Feb 2010 14:02

I almost always open a form first that has a control for the user to select
or enter the criteria value(s). Then I add a button to open the report. There
is sample code for this at http://www.mvps.org/access/reports/rpt0002.htm.
--
Duane Hookom
Microsoft Access MVP

"blanch2010" wrote:

> How can I make a report, when it's opened, ask for user input?
>
> I need to have the user select a name from a list & report on that name's
> record.
>
> Is this done in the query for the report or the report itself?
>
> Thank You
> Don

From: blanch2010 on 8 Feb 2010 15:44

Private Sub cmdRunClientInvoice_Click()
Dim strDocName As String
Dim strWhere As String
strDocName = "rptBillingByClient"
strWhere = "[ClientID]=" & Me!ClientID
DoCmd.OpenReport strDocName, acPreview, , strWhere
End Sub

This gets me an error stating:

Run-time error '3079'
The specified field '[Client]' could refer to more than one table listed in
the From clause of your SQL statement.

I'm not sure what I'm doing wrong but I did, instead of [ClientID] use
ClientLastName and I get a parameter box that pops up asking for the client
lastname. Which, when I type in the client lastname, the reports opens
correctly.

All of this is being done on a form that is showing clientid &
client1lastname.

Any thoughts Duane, other then I'm all messed up?

Thanks again
Don
"Duane Hookom" wrote:

> I almost always open a form first that has a control for the user to select
> or enter the criteria value(s). Then I add a button to open the report. There
> is sample code for this at http://www.mvps.org/access/reports/rpt0002.htm.
> --
> Duane Hookom
> Microsoft Access MVP
>
>
> "blanch2010" wrote:
>
> > How can I make a report, when it's opened, ask for user input?
> >
> > I need to have the user select a name from a list & report on that name's
> > record.
> >
> > Is this done in the query for the report or the report itself?
> >
> > Thank You
> > Don

From: blanch2010 on 8 Feb 2010 16:29

From: Duane Hookom on 8 Feb 2010 16:34

I expect you have a report record source that might use the "*" to select all
fields. This is generally a bad idea. You should explicitly identify/select
individual fields from at least some of the tables.

Also, if you use a criteria agains a text field, you must add some quotes:
strWhere = "[TextFieldName]=""" & Me!txtTextField & """ "

--
Duane Hookom
Microsoft Access MVP

"blanch2010" wrote:

> Private Sub cmdRunClientInvoice_Click()
> Dim strDocName As String
> Dim strWhere As String
> strDocName = "rptBillingByClient"
> strWhere = "[ClientID]=" & Me!ClientID
> DoCmd.OpenReport strDocName, acPreview, , strWhere
> End Sub
>
> This gets me an error stating:
>
> Run-time error '3079'
> The specified field '[ClientID]' could refer to more than one table listed in
> the From clause of your SQL statement.
>
> I'm not sure what I'm doing wrong but I did, instead of [ClientID] use
> ClientLastName and I get a parameter box that pops up asking for a specific
> client
> lastname. Which, when I type in the client lastname, the reports opens
> correctly.
>
> All of this is being done on a form that is showing two combo boxes:
> clientid &
> client1lastname.
>
> Any thoughts Duane, other then I'm all messed up?
>
> Thanks again
> Don

| Next | Last
Pages: 1 2
Prev: Any way to force multiple details on a label?
Next: Report with Subreports