Report based on a record

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

blanch2010 - 08 Feb 2010 18:45 GMT

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

Reply to this Message

Duane Hookom - 08 Feb 2010 19:02 GMT

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.

Signature

Duane Hookom
Microsoft Access MVP

> How can I make a report, when it's opened, ask for user input?
>
[quoted text clipped - 5 lines]
> Thank You
> Don

Reply to this Message

blanch2010 - 08 Feb 2010 20:44 GMT

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

> 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
[quoted text clipped - 9 lines]
> > Thank You
> > Don

Reply to this Message

blanch2010 - 08 Feb 2010 21:29 GMT

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

Reply to this Message

Duane Hookom - 08 Feb 2010 21:34 GMT

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 & """ "

Signature

Duane Hookom
Microsoft Access MVP

> Private Sub cmdRunClientInvoice_Click()
> Dim strDocName As String
[quoted text clipped - 24 lines]
> Thanks again
> Don

Reply to this Message

blanch2010 - 08 Feb 2010 23:15 GMT

Thank you very much Duane. It's working perfectly.

Don

> 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
[quoted text clipped - 31 lines]
> > Thanks again
> > Don

Reply to this Message