Problems with Inventory Management Template

[vandango05]

Hello.



I hope someone can help or point me in the right direction. My knowledge of Access isn't great, but I'm keen to learn.

I have an Inventory Management template that a colleague started to set up for our business but never completed. The database will be used to log stock and create purchase orders.

The products table already has many items that my colleague inputted, but I have been trying to add more. I've got no problem adding products to the table or through the form, but the problem is that the Unit Price's are not being calculated for any other products I'm adding after the originals in the table.

This may have something to do with the template being made in 2003 and i'm using Access 2010 or that the productID is now being auto-generated as a very long number (see screenshot in attachment)

I've attached the database and a couple of screenshots, you will see that I have added product Test001 to the table and that on the purchase order the UnitPrice is not calculated, but does calculate for existing products.

Hope someone can please help?

Thank you

[Robeen]

I looked at your DB.
In the Products Table - Test001 has a UnitPrice of 10.00 quid.

I'm not sure I understand your problem.
You say the Unit Price is not being 'calculated' . . .
I don't think the Unit Price NEEDS to be calculated.
You just input the Unit Price for each product - no?

Perhaps if you explain your problem a little differently - with a few step-by-step examples, I may be able to understand your problem and help.

[vandango05]

Thank you for your reply. The unit price in the products table is a gross price, a discount is applied to this price based on the discount category stored in the table. The result is then the Unit Price which is generated on the purchase orders form. I have seen the VB code for this calculation in the purchase orders form.

If you put any items that were already in the products table, the Unit Price self generates a price on the purchase orders form. Any new items (from test001) are not generated.

See attached marked up screenshot, hope this makes a bit more sense?

Thank you.

[Robeen]

I'm not able to understand why numbers that already existed are being processed - and numbers YOU put in are NOT.

Any chance that you can post the code you're talking about on here?

[vandango05]

I don't understand either!

Here is the Code:


Option Compare Database

Private Sub ProductID_AfterUpdate()

'Dimension variables
Dim Price
Dim ProdDiscID
Dim DiscountPercent

'Find product base price
Price = DLookup("[UnitPrice]", "Products", "ProductID =" & Me.ProductID)
'Find product discount band
ProdDiscID = DLookup("[DiscountID]", "Products", "ProductID =" & Me.ProductID)
'Find percentage discount for band
DiscountPercent = DLookup("[DiscountPC]", "Discount", "DiscountID =" & "'" & ProdDiscID & "'")

'Calculate unit price with discount for item
Me.UnitPrice = ((100 - DiscountPercent) / 100 * Price)

End Sub

[Robeen]

Sorry . . . still nothing.
Can you post a copy of your DB?

[vandango05]

The database is an attachment in the OP

[Robeen]

Of course! Sorry - I forgot that I already have it . . .

[Robeen]

Ok. Let me get this straight.

You're looking at the 'Purchase Orders' Form - right?
Then - you're looking at the numbers in the sub form on that form [it is labelled 'Products Ordered' and is the 'Purchase Orders Subform'] - right?

When I run the Purchase Orders form - the first record on the main form is:
PO ID 12
PO Number J2323

right?

In the Products Ordered there are 3 products.
ACIN 013
ACIN 017
Test001

The first two have a Unit Price of 0.94.
Test001 does NOT have a Unit Price.

Am I looking in the correct place?

Here's what I did:
1.
I put a Break Point in the code [Brown dot in screenshots]
Are you familiar with debugging?
2.
I ran the Form and then changed the Product in the Sub Form to another product and then i changed it BACK to 'Test001'.
3.
Then I stepped through the code and found that your ProdDiscID was '40%'.
As a result - when you look for 'DiscountPercent' - using 'DiscountID = 40%' - your DiscountPercent was coming out Null.
And as a result of THAT - Me.UnitPrice is also Null.

Finally - I copied one of the rows of data that has an existing product - and I changed the price and called it TEST 002 . . . it works fine.

I'm not sure what the problem is.
I even tried entering a new record - and when I do - it still gets 'DiscountID = 40%' instead of "A" . . . or "B" . . .

The mystifying thing is - like you said - it will get the correct value [A, B . . .] for existing records - but the percent Value for new records . . .
unless you copy an existing record and modify it . ..

I'm sorry I don't know what the problem is - but I do know that whatever new value is added - when the code runs - it is picking up the %age - rather than the Discount ID - for any new records added.

See screenshots.

If you can't figure it out - you might try posting again - with this information.

I'm sure someone else will be able to figure it out.

Sorry I can't spend more time on this!!

I'd like to know the solution.

[vandango05]

Thank you for your efforts, they are very much appreciated I was going mad staring at it confused why it wasn't working.

Unfortunately I think the solution is for me to edit all of the existing products in the table to suit the list I have as none of the current products are going to be needed although it will take a lot longer rather than pasting in all the records from an existing spreadsheet I have.

Thanks again Robeen. If anyone else is reading this post, maybe they can help?

[Robeen]

I have a feeling that once you are being helped by someone - most times other programmers don't get involved.
A couple of times, I have had others step in when I've been helping someone.
I think the best thing is to try and understand what is happening in that code - and then create a new thread.
Another thing you might try is to create a new version of that table - and then build it step-by-step - and add a new form and add features of the existing form till you discover where it is the problem crops up [if it does].
All the best!!

[Robeen]

Do you HAVE to have "First Cut" and "Final Cut" in the Cut_No field?