Solved Price History

[asteropi]

Me.OrderPrice = Nz(DLookup("Price", "OrderDetailsQ", "ProductDetailsID=" & ProductCmb & " AND CustomerType = '" & Me.Parent.CustomerType & "'"), 0)

That still relies on previous values being correct, which they will not be at this moment.

That doesn't even give me 0. It stays blank.
Why is this so hard
The logic is pretty simple, but it seems my brain is not braining at the moment

 

[asteropi]

check (if correct) the code on the "Add" button of your Order form.

this one gives me this error when I try to run it

 

[Gasman]

Well I tested it and it worked for me.
Walk your code. Use that line in the immediate window

 

[Gasman]

this one gives me this error when I try to run it

View attachment 116101

View attachment 116102

We have already established, just the product id is not going to work
No point pursuing that.

 

[asteropi]

Well I tested it and it worked for me.
Walk your code. Use that line in the immediate window

can you please send me your file? it will help me see the differences and understand where I was wrong than trying to find it blind. I don't know what you mean by immediate window

 

[Gasman]

Looks in post #14.
Use Ctrl and G to get to it.
That is where Debug.Print output is shown and you can test code there.

 

[asteropi]

Looks in post #14.
Use Ctrl and G to get to it.
That is where Debug.Print output is shown and you can test code there.

I deleted all the fake records and rerun it again. It seems I get the same errors with your file as I get with mine.
I run it in immediate window too and I also get error (Variable not yet created in this context), while your output says 0.45
I'm honestly buffled

 

[Gasman]

How is it meant to work, when there are no records in the query, which should be the case when you start from scratch, surely?
You are going to get 0 with the NZ()? I think your logic is a little off?

You would need to set a breakpoint at some point to stop the code and then be able to test anything in the Immediate window.

Have a look on Youtube for soem debugging tips. See the link in my signature at the bottom of each of my posts.

Leave it for the night and come back to it tomorrow, as now you are frustrated and will likely make more mistakes.

 

[asteropi]

How is it meant to work, when there are no records in the query, which should be the case when you start from scratch, surely?
You are going to get 0 with the NZ()? I think your logic is a little off?

You would need to set a breakpoint at some point to stop the code and then be able to test anything in the Immediate window.

Have a look on Youtube for soem debugging tips. See the link in my signature at the bottom of each of my posts.

Leave it for the night and come back to it tomorrow, as now you are frustrated and will likely make more mistakes.

I realized how it works. So basically in order to get anything other than 0 there should already be at least one record before of the same type
Not quite what I was aiming for, but it will do for now

Thank you

 

[Gasman]

Yes, what comes first?, the chicken or the egg
As I have mentioned, I think your logic is flawed.

 

[asteropi]

No I made no changes to the DLookUP, it still find just the first record for that product, which will always be the last one you used.
Make a mistake, and you will always pick up that mistake.

I would probably have a ProductpriceHistory table.
That would have at minimum
AutoNumber
ProductID
CustomerType
Price
DateFrom
DateTo

Then you just lookup what the price should be on a certain date. Perhaps have the discounts as well if they can change periodically.

Your DLookup() with what you have now should look like

Me.OrderPrice = Nz(DLookup("Price", "OrderDetailsQ", "ProductDetailsID=" & ProductCmb & " AND CustomerType = '" & Me.Parent.CustomerType & "'"), 0)

That still relies on previous values being correct, which they will not be at this moment.

Well I'll try this one next and see how it goes. Rest well for now

 

[Pat Hartman]

Do Not use a domain function to set the value of OrderPrice. That just leads to potential anomolies.

Change the RowSource query of the product combo to include the current price. In the AfterUpdate event of the product combo, copy the price from the RowSource to the OrderPrice field.

Me.OrderPrice = Me.cboProduct.Column(2) -- the RowSource is a zero-based array. So assuming the first column is ProductID, the second Is ProductName, and the third is Price, then Column(2) - which is the third column, is the price. If the price is the fourth column, it is .Column(3).

 

[arnelgp]

this one gives me this error when I try to run it

if you just happen to copy the code over to your original db, chances are that the Price textbox has different name than yours.
view your form on design view and click on the "price" textbox and check it's correct name.
now, replace Me.Price, on the code, with the correct textbox name:

Me.TheNameOfYourPriceTextboxHere = dblPrice

 

[arnelgp]

i change the code (please see the code on "Add" button if it is correct), to use Wholesale or retail price.

 

[sonic8]

it seems my brain is not braining at the moment

Lovely choice of words.

I struggle to follow the course of this thread, so I've got no specific advice to solve the current problem.

However, I made two videos on time dependent data with examples similar to your changing prices. My approaches are different than those discussed here, but viewing a problem from a different angle might be helpful when being stuck.

Time Based Data - Simple Approach

Time Based Data - Normalized

 

[Pat Hartman]

Storing the price with the sale is normalized since the price is valid for that order on that day. Most order entry applications need to have the ability to override the price. That is the requirement that should push you over the edge to storing the price with the order rather than always looking it up. In fact, I don't think I've ever run into a situation where the client didn't find the need to have a custom price either at the start of the project or by the end so I ALWAYS go with storing the price because changing later is a lot of work.

 

[asteropi]

i change the code (please see the code on "Add" button if it is correct), to use Wholesale or retail price.

I tried this. It doesn't work properly

So the first time you add a product it recognises if it's wholesale or retail and it adds that price
But every time after you add it it takes that first price. Which is a mess if you change orders from retail to wholesale and vice versa.
Try adding orders #1 and #4 and you will see what I mean

 

[arnelgp]

so what you want is actually fetch the price from your ProductDetailT?

 

[asteropi]

either ProductDeatilT but recognise what price it is
or get Price from OrderDetailsQ which runs an IIF command

 

[asteropi]

so what you want is actually fetch the price from your ProductDetailT?

you made it!! it works!! wow you are a magician! Now I just need to understand what you did.
what is this dbl command?