Rpt based on record displayed 2

[ProtegeV2]

Currently, my report prompts the user to enter the Docket number to select records. What do I need to change so the user can click a button to preview a report based on the currently displayed record on the form? (skip the WHERE below)

Here's my query:
SELECT tblEstimates.Date, tblEstimates.Number, tblEstimates.Revision, tblEstimates.Docket, tblEstimates.Description, tblEstimates.Total, qryEstimates.SumOfTotal
FROM tblEstimates INNER JOIN qryEstimates ON tblEstimates.Docket = qryEstimates.Docket
WHERE (((tblEstimates.Docket)=[Enter Docket]));

Form button:
Report Preview On_Click
stDocName = "rptEstimates"
DoCmd.OpenReport stDocName, acPreview

I hope that is enough information. Thanks.

 

[dhookom]

I would remove the where clause from the query. Then use code like
Dim strWhere as String
strWhere = "Docket=" & Me.txtDocket
stDocName = "rptEstimates"
DoCmd.OpenReport stDocName, acPreview,,strWhere

This assumes Docket is numeric. If it is text, then use:
strWhere = "Docket=""" & Me.txtDocket & """"



Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ProtegeV2]

Thanks for your response. Yes, docket is a numeric field. I tried your code but the report produces all docket records - not the displayed record. Just to clarify, the common field is named [Docket] in the query and [Docket #] in the report. Can you see what is missing? Thanks!

Private Sub Command97_Click()
On Error GoTo Err_Command97_Click

Dim strWhere As String
strWhere = "Docket=" & Me.txtDocket
stDocName = "rptEstimates"
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_Command97_Click:
Exit Sub

Err_Command97_Click:
MsgBox Err.Description
Resume Exit_Command97_Click

End Sub

 

[dhookom]

If the column in the report is [Docket #] then your strWhere should be:
strWhere = "[Docket #]=" & Me.txtDocket


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ProtegeV2]

We forgot:
Dim stDocName as String

It works, thanks!

 

[ProtegeV2]

Can you help me apply this to another report button on this form. With record 3368 displayed, I receive the message: syntax error in date in query expression '(Docket #=3368'. Note that the report worked when I had the user enter the criteria [Enter Docket #] but I've removed this WHERE clause.

Here is the SQL - is the inner join causing the problem?

SELECT [Project Expenses Table].[Invoice ID], [Project Expenses Table].[Invoice Amount], [Project Expenses Table].[Invoice Date], [Project Expenses Table].[Docket #], [Project Expenses Table].[Docket #], [Project Expenses Table].[Description of Invoice], [Project Request Table].[Project Title], [Project Request Table].Status, [Project Request Table].Expenses, [Project Request Table].[Tracking Estimate], [Project Expenses Table].[Person Submitting Invoice], [Project Request Table].[Date of Request], [Project Request Table].[Budget Amount], [Project Request Table].[Cost Centre], [Project Request Table].[Network Number]
FROM [Project Request Table] INNER JOIN [Project Expenses Table] ON [Project Request Table].[Docket #] = [Project Expenses Table].[Docket #]
ORDER BY [Project Expenses Table].[Docket #];

Here's my On_Click:
Private Sub Command98_Click()
On Error GoTo Err_Command98_Click

Dim stDocName As String
Dim strWhere As String

strWhere = "Docket #=" & Me.txtDocket
stDocName = "rptDocketExpenses"
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_Command98_Click:
Exit Sub

Err_Command98_Click:
MsgBox Err.Description
Resume Exit_Command98_Click

End Sub

 

[PHV]

Replace this:
strWhere = "Docket #=" & Me.txtDocket
By this:
strWhere = "[Project Request Table].[Docket #] =" & Me.txtDocket

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ProtegeV2]

Thanks!

 

[dhookom]

Rather than update the code, change your naming convention to remove all spaces and symbols.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.