Report based on a record

[blanch2010]

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

 

[Duane Hookom]

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.

 

[blanch2010]

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

 

[blanch2010]

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

 

[Duane Hookom]

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

 

[blanch2010]

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

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