Opening report based on current Order

[Leo_Coroneos]

Opening report based on current Order [SOLVED]

Guys,

I could do with some help with the final touches to my music store database, and it should come across as a very easy question to answer. My VBA programming primer book just arrived in the mail and I've searched the event procedure section for answers. I have also referred to links at MSDN and Bytes, but I am unable to post them due to my too-low post count on these forums.

My problem here is as follows:

I'm trying to open a report in Access that only shows the data from one order when the Review button is pressed (see picture Snip1).

Access is instead giving me the whole catalogue of orders (see picture Snip2).


This is no good, and it is occurring even after I implemented the following code into the Review button (see picture Snip3):

I.e.

DoCmd.OpenReport "rptOrder", acViewPreview, , , , "OrderID = " & Me.OrderID

Can anyone help? What might I be doing wrong? Thanks in advance!

- LC

 

[arnelgp]

if you are using WhereClause of the OpenReport method, you have 2 extra commas there. what you are currently doing is passing an OpenArgs parameter. to fix:


DoCmd.OpenReport "rptOrder", acViewPreview, , "OrderID = " & Me.OrderID

 

[Leo_Coroneos]

Ok, thanks for your input. However, I actually gave that a try not long ago, and I get this message when I press Review:

"Syntax error (missing operator) in query expression 'Order ID = '."

I know that I am playing around with the arguments for this code, not the where clause, which--should it be SQL language? I'm not quite clear on this.

How about I zip my database and post it on here?

 

[arnelgp]

if you have Space on your fieldname, you must enclosed it in Square bracket:


"[Order ID]=" & Me.OrderID

 

[Leo_Coroneos]

The field name's actually tblOrder_OID... I changed it to this:

DoCmd.OpenReport "rptOrder", acViewPreview, , "[tblOrder_OID] = " & Me.tblOrder_OID

But I still get the same "missing operator" error. What kind of operator would I need in here? Is the ampersand not the correct one for what I'm trying to do?

 

[arnelgp]

do you have tblOrder_OID on your report?
your syntax there is for tblOrder_OID field to be Numeric, if it is string:


"[tblOrder_OID] = " & """" & Me.tblOrder_OID & """"


you are calling this somewhere from a Form's code is it?

 

[Leo_Coroneos]

(I can't upload the database due to its [zipped] filesize exceeding the limit of 2MB.)

The report only has "OID" on it. Shall I create a new report that refers specifically to tblOrder_OID?

 

[arnelgp]

then use that as the Critera:


"[OID] = " & Me.tblOrder_OID

 

[Leo_Coroneos]

Hmm. Seems like whatever I do, the report still comes up with ALL the orders. Maybe I should just create a new Order form and connect it to the report with the criterion as OID.

N.b. I have a subform that contains the products (see Snip1 in the attachment to my first post in this thread), and, as I have indicated, I want the report to contain only the products in the current order.

This is getting frustrating and I've been stuck on it for a couple of days now. I appreciate your help and I recommend that you take a look at my ZIP file to get an idea of what's involved in this, er, challenge.

Ideally, we will be able to solve this problem without too much trouble, and I can change the status of this thread to [SOLVED].

 

[arnelgp]

whar is the recordsiuce if the reoort. if query, show me the sql.

 

[Leo_Coroneos]

SELECT [tblProductOrder].[OID] AS tblProductOrder_OID, [tblProductOrder].[Quantity], [tblProduct].[Article], [tblProduct].[PID], [tblOrder].[OID] AS tblOrder_OID, [tblOrder].[CID], [tblOrder].[OrderDate] FROM tblProduct INNER JOIN (tblOrder INNER JOIN tblProductOrder ON [tblOrder].[OID] =[tblProductOrder].[OID]) ON [tblProduct].[PID] =[tblProductOrder].[PID];

 

[arnelgp]

is the name of textbox for OrderID is [OID]
or OrderID or tblOrder_OID?

substitute the correct textboxname to
yourOrderID.

"tblProductOrder_OID = " & Me.yourOrderID

 

[Leo_Coroneos]

The textbox is called tblOrder_OID. My code is:

"tblOrder_OID = " & Me.tblOrder_OID

And it still brings up ALL the orders. I can't figure out what I'm doing wrong.

 

[arnelgp]

"tblProductOrder_OID = " & Me. tblOrder_OID

 

[Leo_Coroneos]

No, still not working

 

[arnelgp]

do u hace any code in the report, losd open event.

 

[Leo_Coroneos]

Good idea. I can bring this up:

Private Sub Report_Load()
[...]
End Sub

What should I put in there?

 

[arnelgp]

make a copy of ur db, leave 2 different order on order table and del the rest from the copy. zip ur copy db and post it. just remove those unrelated data from the 2 orders to reduce the size. compact and repair.

 

[isladogs]

PMFBI

I think there are 2 issues here:
1. You are using a text field so you need text delimiters
2. Lack of clarity over the field name & the form control name that you are referencing

As I understand it,
a) the field is called OID but in your report record source you are using the alias tblOrder_OID.
b) the form textbox control used for the filter is called tblOrder_OID

Therefore your code should be:

DoCmd.OpenReport "rptOrder", acViewPreview, , "[tblOrder_OID] = [COLOR="Red"][B]'"[/B][/COLOR] & Me.tblOrder_OID & [COLOR="red"][B]"'"[/B][/COLOR]

Alternatively if not using an alias for the field name, it should be:

DoCmd.OpenReport "rptOrder", acViewPreview, , "OID = [COLOR="Red"][B]'"[/B][/COLOR] & Me.tblOrder_OID & [COLOR="red"][B]"'"[/B][/COLOR]

Note the text delimiters used in RED
HTH

 

[Leo_Coroneos]

Ok, here's the database, stripped down to the very basics.


View attachment WakesMusicDB_Backup.zip