Opening report based on current Order (1 Viewer)

Leo_Coroneos · Nov 27, 2017

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.

Code:

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

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

- LC

arnelgp · Nov 27, 2017

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 · Nov 27, 2017

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 · Nov 27, 2017

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

"[Order ID]=" & Me.OrderID

Leo_Coroneos · Nov 27, 2017

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

Code:

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 · Nov 27, 2017

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 · Nov 27, 2017

(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 · Nov 27, 2017

then use that as the Critera:

"[OID] = " & Me.tblOrder_OID

Leo_Coroneos · Nov 27, 2017

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 · Nov 27, 2017

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

Leo_Coroneos · Nov 27, 2017

Code:

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 · Nov 27, 2017

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 · Nov 27, 2017

The textbox is called tblOrder_OID. My code is:

Code:

"tblOrder_OID = " & Me.tblOrder_OID

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

arnelgp · Nov 27, 2017

"tblProductOrder_OID = " & Me. tblOrder_OID

Leo_Coroneos · Nov 27, 2017

No, still not working

arnelgp · Nov 27, 2017

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

Leo_Coroneos · Nov 27, 2017

Good idea. I can bring this up:

Code:

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

What should I put in there?

arnelgp · Nov 27, 2017

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 · Nov 27, 2017

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:

Code:

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:

Code:

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 · Nov 27, 2017

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

View attachment WakesMusicDB_Backup.zip

Opening report based on current Order (1 Viewer)

Registered User.

Attachments

..forever waiting... waiting for jellybean!

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

..forever waiting... waiting for jellybean!

MVP / VIP

Registered User.

Similar threads

Users who are viewing this thread