Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Rpt based on record displayed 2

Status
Not open for further replies.

ProtegeV2

Technical User
Joined
Sep 9, 2004
Messages
40
Location
CA
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.
 
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.
 
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
 
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.
 
We forgot:
Dim stDocName as String

It works, thanks!
 
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
 
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
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top