UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Display Report Containing *single* Record, Access 2016    
 
   
Art_Of_War
post Dec 6 2017, 03:04 AM
Post#1



Posts: 12
Joined: 4-December 17



Hi, my question is similar to this one: Print One Record Of Report, Access 2016

I am trying to create a report that shows the specific order I am making within my Order form (see attachment:)
Attached File  frmOrder.JPG ( 60.96K )Number of downloads: 4


For some reason I am prompted to enter the Order ID before the report pops up (see attachment:)
Attached File  Prompt.JPG ( 15.83K )Number of downloads: 0


The report, unfortunately, shows ALL records in tblOrder (see attachment:)
Attached File  rptOrder.JPG ( 75.6K )Number of downloads: 5


This is not what I want. I am wondering if it would be OK to apply a filter on the report's OnLoad property, and if so, how and with what syntax? Would I be better off creating a procedure as a module that operates on the report somehow?

Access novice here, sorry about the lack of know-how; I am struggling hard to educate myself but in this case I really do need the help.

Thanks for any and all replies,
- Art_Of_War.
Go to the top of the page
 
moke123
post Dec 6 2017, 03:41 AM
Post#2



Posts: 1,148
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



Are you using the Docmd.openreport method?

something like
CODE
Docmd.OpenReport "rptOrder", , , "OID = " & me.OID
Go to the top of the page
 
BuzyG
post Dec 6 2017, 05:10 AM
Post#3



Posts: 337
Joined: 20-September 12
From: Cornwall UK


There are a number of ways to do this. One simple method is to create a separate query for your report, so that the query just shows the data you wish to see.

--------------------
Live to Surf
Go to the top of the page
 
GroverParkGeorge
post Dec 6 2017, 07:13 AM
Post#4


UA Admin
Posts: 31,197
Joined: 20-June 02
From: Newcastle, WA


We need to see the query that provides records for the report. We also need to see how you are opening the report. Screen shots show the interface, but the problem is coming from the SQL and/or VBA.

--------------------
Go to the top of the page
 
Art_Of_War
post Dec 6 2017, 10:00 AM
Post#5



Posts: 12
Joined: 4-December 17



Yes. It's:

CODE
Private Sub btnReview_Click()
DoCmd.OpenReport "rptOrder", acViewPreview, , "[tblOrder_OID] = '" & Me.tblOrder_OID & "'"
End Sub


Might be wrong, actually, if I need to be referring to the OID record instead of the text box.
Go to the top of the page
 
Art_Of_War
post Dec 6 2017, 10:01 AM
Post#6



Posts: 12
Joined: 4-December 17



Record source is as follows:

CODE
SELECT [tblOrder].[OID], [tblOrder].[CID], [tblOrder].[OrderDate], [tblProductOrder].[PID], [tblProductOrder].[Quantity], [tblProduct].[Article] FROM tblProduct INNER JOIN (tblOrder INNER JOIN tblProductOrder ON [tblOrder].[OID] =[tblProductOrder].[OID]) ON [tblProduct].[PID] =[tblProductOrder].[PID];
Go to the top of the page
 
GroverParkGeorge
post Dec 6 2017, 10:24 AM
Post#7


UA Admin
Posts: 31,197
Joined: 20-June 02
From: Newcastle, WA


It would appear to be the case that you are trying to apply a filter to the report's recordsource when it opens, but using the apostrophes as delimiters suggests a problem with the way it's written. I would imagine it's a long Integer, not a text string.

Also, the SQL shows: tblOrder].[OID]

But the WHERE clause in the filter shows: "[tblOrder_OID] = '" & Me.tblOrder_OID & "'"

Those are DIFFERENT things.

Try this instead: "[tblOrder].[OID] = " & Me.tblOrder.OID or probably, if you have a textbox bound to OID, "[tblOrder].[OID] = " & Me.txtOID

--------------------
Go to the top of the page
 
Art_Of_War
post Dec 7 2017, 01:46 AM
Post#8



Posts: 12
Joined: 4-December 17



It works, by George! Thanks so much GroverParkGeorge!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    13th December 2017 - 02:10 AM