Opening report based on current Order (1 Viewer)

Leo_Coroneos · Nov 27, 2017

Hi ridders, thanks for contributing.

I tried the first bit of code. Before the report comes up, the DB asks for the order ID in a message box. (See picture: Snip4.jpg) I don't know if this is good or bad...

The second bit of code doesn't work for me.

Struggling here. If someone could look at the database I've cited in my previous post, that would probably help a bit.

:banghead:

isladogs · Nov 27, 2017

i had a quick look at your db and you seem to be making your life unnecessarily difficult. I'm just going out so no time to review properly now

As arnelgp has been involved from the start he may well reply before i return.
If not, I'll have a proper look later

arnelgp · Nov 27, 2017

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

isladogs · Nov 27, 2017

OK I've had another look at this db.

You do need to be consistent with field naming throughout your db
OrderID, OID, tblOrder_OID etc - recipe for confusion

The fact that you start with this message also says a lot:

If you design your forms properly:
1. the next available order number would appear automatically based on autonumber. As it is, I need to look up the last number then add 1
2. the date would default to today's date (though it should be be editable)
3. use a value list for quantity
3. Controls would normally be combo boxes giving all available options

Anyway, I modified the report criteria as below:

Code:

Private Sub btnReview_Click()
DoCmd.OpenReport "rptOrder", acViewPreview, , , , "OrderID = '" & Me.OrderID & "'"

HTH
End Sub

Added a new order ... with difficulty as explained above.
Here is the form & report on the same screenshot.
As you can see, its correctly filtered

EDIT:
I've attached your db with that one minor change.
It just scrapes in under the 2MB limit. If you need to re-post, you'll need to remove unnecessary items

Leo_Coroneos · Nov 27, 2017

ridders said:
OK I've had another look at this db.

You do need to be consistent with field naming throughout your db
OrderID, OID, tblOrder_OID etc - recipe for confusion

The fact that you start with this message also says a lot:

If you design your forms properly:
1. the next available order number would appear automatically based on autonumber. As it is, I need to look up the last number then add 1
2. the date would default to today's date (though it should be be editable)
3. use a value list for quantity
3. Controls would normally be combo boxes giving all available options

Anyway, I modified the report criteria as below:

Code:

Private Sub btnReview_Click() DoCmd.OpenReport "rptOrder", acViewPreview, , , , "OrderID = '" & Me.OrderID & "'" HTH End Sub

Added a new order ... with difficulty as explained above.
Here is the form & report on the same screenshot.
As you can see, its correctly filtered

EDIT:
I've attached your db with that one minor change.
It just scrapes in under the 2MB limit. If you need to re-post, you'll need to remove unnecessary items

Great, thanks so much--I'll check it out.

The reason the Order ID is the way it is, is because my client needs to manually enter his Order ID. It must not, according to my client's requirements, be an Auto-Number field.

Thanks again everyone!

isladogs · Nov 27, 2017

Leo_Coroneos said:
Great, thanks so much--I'll check it out.

The reason the Order ID is the way it is, is because my client needs to manually enter his Order ID. It must not, according to my client's requirements, be an Auto-Number field.

Thanks again everyone!

If your client insists on this, then you can use a formula like this to obtain the new OrderID:

OrderID = DMax("OrderID","tblProductOrder")+1

Leo_Coroneos · Nov 28, 2017

Ta @ridders, I'll give it a try.

@Uncle Gizmo suggested I look at this video:

http://www.niftyaccess.com/generate-multiple-reports/

Phew! Seems awfully complicated for what I want to do, but I could certainly give it a shot.

Anyone else have any other advice before I get stuck into reimplementing this Order form and associated report?

Leo_Coroneos · Nov 29, 2017

OK, I had an idea. What if I delete all the order records that have been sent to the supplier via the database as soon as I create a new order? That would work, and my client won't need to access the previous orders anyway. If he did, perchance, he could just look at his emails.

Here's a few screen snips of what I'm up to so far. Note that my "Send" button on the Order form used a Macro, i.e. Email Database Object, which refers to the form itself. The pictures explain it better.

Gasman · Nov 29, 2017

I'd say that would be a terrible idea.

You might want to set a new field on the record as processed or something, but they would not have any history of what was created?
Looking through emails is hardly a solution.

I created a small DB for employee hours in work. I have a report that shows me the hours before I create the output for the payroll people. That way any errors can be corrected before generating the data for output.

I soon realised that the bosses were going to come in and ask 'What did xxx work in October this year'.

So I created a form to take name from combo , start and end dates and used the same report to generate the result.

The general consensus on this site is do not delete, but mark in some way to indicate to ignore in normal processing.

Do it from that start as it is quite hard to implement if the app is already created.
Speaking from experience there.

Minty · Nov 29, 2017

I'll echo Gasman - what an awful idea. The whole point of a database is to record data, and allow you to report and manipulate that data.

Deleting it and relying on an email copy is like buying a reference book, reading a few pages and and copying them out onto post-it notes, then tearing the pages out and eating them.

You should be creating a professional looking report from your orders and emailing that as a pdf, rather than just a screen dump of the form.

And I'd politely ask why the auto number PO number is so verboten ? Is there a sound business reason for the objection to it?

Leo_Coroneos · Nov 29, 2017

Thanks guys, your brutal honesty is appreciated... sure, it's a bad idea, but it was--incredibly--the best I could come up with at the time. Frankly, that was the only thing that worked for me at all!

My client wants to be able to enter the Order ID manually. That is his requirement, not my own hare-brained idea. He needs to enter a four-digit ID for each one that would normally go on a hand-written order slip (see attachment).

My whole problem is, I just CANNOT get a report up that shows only ONE order at a time. Hate to sound like a broken loopy record here, but that's where I'm at, at the moment. What to do, what to do?

Gasman · Nov 29, 2017

Unless I am missing something here?
Your client can use whatever he wants as an order number/ID etc.
You would use the Autonumbers as your keys. His are for display purposes only?

Minty · Nov 29, 2017

Well you can present a four digit number that follows on from the last one he used, I have no idea why he'd want the possibility of being able to use the same number twice. This is where your ability to present a solution and explanation to the fix becomes key.

Personally I would present the PO in the report as the unique ID prefixed with the suppliers code within the system, makes it easy to see who it's for and equally easy to find in the system.

Your report issue should be a simple fix, I can't open your database though.

Gasman · Nov 29, 2017

I thought Ridders had fixed that for you in a previous post and uploaded the DB?

Leo_Coroneos said:
My whole problem is, I just CANNOT get a report up that shows only ONE order at a time. Hate to sound like a broken loopy record here, but that's where I'm at, at the moment. What to do, what to do?

isladogs · Nov 29, 2017

Gasman said:
I thought Ridders had fixed that for you in a previous post and uploaded the DB?

And now you mention it, so did I. See post #24

Leo_Coroneos · Nov 30, 2017

Yeah, I didn't want to hurt his feelings by complaining that it still didn't work out for me...

isladogs · Nov 30, 2017

You are more likely to 'hurt' someone by appearing to ignore a solution he/she provides.
I know the version I uploaded worked or I wouldn't have posted it.
What was the issue when you tried it?

Leo_Coroneos · Nov 30, 2017

Ok, sorry. I did try it out, the orders kept piling up in the database, and I faced the same problem I did before: that my report based on the Order form displayed all the orders at once.

I can't help but think that I'm getting a bit stuck in the loop in the middle of this iterative dev cycle:

Minty · Nov 30, 2017

I'm going to be blunt here and spot the elephant in the room, if you can't make a report show the desired results, you probably shouldn't be even considering thinking about trying to sell your databases.

In your diagram above you are still in the alpha design /prototyping stage, and you are still learning the basics. (We are all still learning to be honest, it just varying levels of knowledge).

Back to your original problem. Can you upload your or Ridders current version, I can't open either of them? What version of access are you using?

isladogs · Nov 30, 2017

I was about to make the same point as Minty

The solution I provided was the standard method. It works.
The only thing that made it difficult for me was some basic design flaws which I referred to earlier.

You really need to do a significant rewrite of this app to make it truly fit for purpose

At the risk of offending you, here are a few words of advice.
1. Before you upload a database, make sure it doesn't contain real data or real company info such as company name.
2. Don't refer to potential customers as 'poor suckers' on a public forum. One of them might read it and also recognise you from your photo
3. Don't even think of selling your product until it is fit for purpose even if it the client is a sucker.
All you will do is give yourself a lot of support work and more importantly damage your reputation
4. Don't run before you can walk...
You might wish to reflect on why I suggested you read the 2 threads I gave links to. They weren't randomly chosen.

And here's another bit of light reading for you. This time its one of my posts
https://www.access-programmers.co.uk/forums/showthread.php?t=296747

@Minty
My minor modification was done in A2010 32 bit. What issue did you have opening it?

Opening report based on current Order (1 Viewer)

Registered User.

Attachments

MVP / VIP

..forever waiting... waiting for jellybean!

MVP / VIP

Attachments

Registered User.

MVP / VIP

Registered User.

Registered User.

Attachments

Enthusiastic Amateur

AWF VIP

Registered User.

Attachments

Enthusiastic Amateur

AWF VIP

Enthusiastic Amateur

MVP / VIP

Registered User.

MVP / VIP

Registered User.

AWF VIP

MVP / VIP

Similar threads

Users who are viewing this thread