code explanation (1 Viewer)

PWG · Sep 6, 2019

I have a fault where it says that there is no supplier for a part.
The code that I think is looking for this is

SupplierID = Inventory.FindProudctSupplier(IT.PartID)

I have looked in the inventory query and there is a Supplier ID.
Could you please tell me this
Is inventory where it is looking
Is it looking for a Product supplier
and what does the (IT.PartID) mean
Thanks for your help

theDBguy · Sep 6, 2019

Hi. The syntax

Code:

SupplierID = Inventory.FindProductSupplier(IT.PartID)

could mean a lot of things. Where is this code placed? How is it executed? Do you have two Standard Modules named Inventory and IT? If so, do you have a function called FindProductSupplier() in one of them? If so, can you post the code for it? Are you getting an error with that code? What was the error message?

PWG · Sep 6, 2019

It is started here

Code:

Private Sub Status_Name_DblClick(Cancel As Integer)
    Select Case Me![Status ID]
    Case NoStock_OrderItemStatus, None_OrderItemStatus
        Quantity_AfterUpdate
    Case OnOrder_OrderItemStatus
        Dim PurchaseOrderID As Long
        PurchaseOrderID = Nz(Me![Purchase Order ID], 0)
        If PurchaseOrderID > 0 Then
            PurchaseOrders.OpenOrder PurchaseOrderID
            Me.Requery
        End If
    Case Invoiced_OrderItemStatus
    End Select
End Sub

Then I think that it triggers this Quanty afterUpdate

Code:

Private Sub Quantity_AfterUpdate()
    On Error GoTo ErrorHandler
    
    Dim IT As InventoryTransaction
    Dim PurchaseOrderID As Long
    Dim SupplierID As Long
    
    IT.PartID = Nz(Me![ID], 0)
    IT.Quantity = Me![Quantity]
    IT.AllOrNothing = True
    IT.InventoryID = Nz(Me![Inventory ID], NewInventoryID)
    
    'Request Hold on specified Inventory
    If Inventory.RequestHold(Me![Job ID], IT) Then
        Me![Inventory ID] = IT.InventoryID
        Me![Status ID] = OnHold_OrderItemStatus
        
    'Insufficient Inventory
    ElseIf Me![Status ID] <> None_OrderItemStatus And Me![Status ID] <> NoStock_OrderItemStatus Then
        MsgBoxOKOnly InsufficientInventory
        Me![Quantity] = Me.Quantity.OldValue
    
    'Attempt to create purchase order for back ordered items
    ElseIf MsgBoxYesNo(NoInventoryCreatePO) Then
     
        SupplierID = Inventory.FindProductSupplier(IT.PartID)
        
        'Create purchase order if we have supplier for this product
        If SupplierID > 0 Then
            If PurchaseOrders.Generate(SupplierID, IT.PartID, Me![Quantity], Me![Job ID], PurchaseOrderID) Then
                PurchaseOrders.OpenOrder PurchaseOrderID
                Me![Status ID] = OnOrder_OrderItemStatus
                Me![Purchase Order ID] = PurchaseOrderID
                eh.TryToSaveRecord
            Else
                Me![Status ID] = NoStock_OrderItemStatus
            End If
            
        'Could not find a supplier for this product
        Else
            MsgBoxOKOnly CannotCreatePO_NoSupplier
            Me![Status ID] = NoStock_OrderItemStatus
        End If
        
    Else
        Me![Status ID] = NoStock_OrderItemStatus
    End If
    
Done:
    Exit Sub

ErrorHandler:
    ' Resume statement will be hit when debugging
    If eh.LogError("Quantity_AfterUpdate") Then Resume
End Sub

theDBguy · Sep 6, 2019

Hi. I see IT is an instance of a Class Module but I don't see what Inventory is to be able to understand what Inventory.FindProductSupplier(IT.PartID) and Inventory.RequestHold(Me![Job ID], IT) are.

PWG · Sep 6, 2019

The only thing that I can find is a Query that is call Inventory This is the SQL for that
SELECT Parts.ID AS [Part ID], Parts.Description, Parts.Location, Parts.[Part Number], Nz([Quantity Purchased],0) AS [Qty Purchased], Nz([Quantity Sold],0) AS [Qty Sold], Nz([Quantity On Hold],0) AS [Qty On Hold], [Qty Purchased]-[Qty Sold] AS [Qty On Hand], [Qty Purchased]-[Qty Sold]-[Qty On Hold] AS [Qty Available], Nz([Quantity On Order],0) AS [Qty On Order], Nz([Quantity On Back Order],0) AS [Qty On Back Order], Parts.[Reorder Level], Parts.[Target Level], [Target Level]-[Current Level] AS [Qty Below Target Level], [Qty Available]+[Qty On Order]-[Qty On Back Order] AS [Current Level], IIf([Qty Below Target Level]>0,IIf([Qty Below Target Level]<[Minimum ReOrder Quantity],[Minimum Reorder Quantity],[Qty Below Target Level]),0) AS [Qty To Reorder], Parts.[Supplier IDs]
FROM ((((Parts LEFT JOIN [Inventory Sold] ON Parts.ID = [Inventory Sold].[Part ID]) LEFT JOIN [Inventory Purchased] ON Parts.ID = [Inventory Purchased].[Part ID]) LEFT JOIN [Inventory On Hold] ON Parts.ID = [Inventory On Hold].[Part ID]) LEFT JOIN [Inventory On Order] ON Parts.ID = [Inventory On Order].[Part ID]) LEFT JOIN [Parts on Back Order] ON Parts.ID = [Parts on Back Order].[Part ID];

JHB · Sep 6, 2019

Could you post your database with some sample data, zip it!

PWG · Sep 6, 2019

I think I have attached it.
So to let you know. The data base is for our store we do not sell any parts they are just consumed by us.
I have taken the Northwind data base and been changing it. So I have changed some table names.
I have been working through the code and getting it working so far.
If you open Job number 126 on the home screen and then click on the No Stock feild and say yes to create purchase order is when I get the problem.
I will add that I dont know a lot about code but are trying to learn fast.

isladogs · Sep 6, 2019

I've opened the Home form and selected Job 126.
There isn't a No Stock field to click.
Do you mean click New Purchase Request where you get asked for the strangely named parameter 'Standard Cost cost'?

For info, I added code tags (# button on toolbar) to post #1 to make it easier to read

EDIT There is also a standard module called Inventory. This contains functions such as GetQtyAvailable

PWG · Sep 6, 2019

sorry its the status field where it says no stock

isladogs · Sep 6, 2019

Maybe we're at cross purposes but the Status field says New.
Anyway if I double click it, an Order ID parameter appears.
I don't get asked about creating a new purchase order.
If I enter a value a new Job Details form opens.
Now what?

PWG · Sep 6, 2019

Ok down the bottem in the active jobs sub form #126 double click that.
Opens Jod 126 status new, In the parts Detail sub form part number 26-15 shows in the status field No Stock. Dubble click the no stock text, Message do you want to create purchase Request. Click Yes, Then I get the message no suppliers listed.

isladogs · Sep 6, 2019

Ah finally...

The message says there is no supplier for that PartNumber which is I think self explanatory.

Open the Parts table and look at the SupplierID field which has a table level lookup. That's a bad idea and leads to all kinds of confusion. See http://www.theaccessweb.com/lookupfields.htm

To make matters worse its a multivalued field which are another very bad idea.
See http://www.mendipdatasystems.co.uk/multivalued-fields/4594468763

Please read both of these links to understand why the table needs a complete redesign. Unfortunately some MS templates include some horrible features which makes them almost impossible to use

JHB · Sep 6, 2019

I agree fully with isladogs, also about the MS templates.

PWG · Sep 6, 2019

Thanks for that I will read them tonight. Thought the template would be good being in the Microsoft templates I will look at a table redesign. and see how I go.

isladogs · Sep 6, 2019

Sorry to be the bearer of bad news

To be honest, I expect there will be issues with other tables as well.

Most of the MS templates are designed to show off some of the newer Access features.
They look good but too often aren't fully functional or contain some of the worst examples of Access design such as table level lookups, MVFs, attachment and calculated fields.

Sometimes its better to start again with a new database app rather than try and fix a supplied template

PWG · Sep 6, 2019

So if I fix the tables will all the code not work any more or do I need to try and right that as well. Sorry but at entry level and trying to learn fast

isladogs · Sep 6, 2019

You should assume that most of the code won't work.
You could try botching what you have now but in the end it will save time to fix it properly

Uncle Gizmo · Sep 6, 2019

isladogs said:
Unfortunately some MS templates include some horrible features which makes them almost impossible to use

What really niggles me about the Microsoft templates is that one template will have a customer table, a supplier table, etc... Then another template will have the same tables but they have different arrangement of Fields! If you want to mix and match templates using part of one template in another you are stuffed, without doing a lot of messing around!

PWG · Sep 6, 2019

Ok after 3 months of working with this template only to find that it is flawed from the start I’m going to start from scratch and build my own . So thank you to you all and I look forward to hearing from you as I progress though this journey of learning

jdraw · Sep 6, 2019

PWG,

I agree with the comments and advice given by the others. Several of the sample database(s) (eg northwind specifically) has been criticized in many posts and articles for some of the shortcomings (as Colin listed + naming convention, lack of data model...) the database(s) expose.
If you are going to start from scratch, then you might want to start with some of the planning and design articles and tutorials found at RogersAccessLibrary.
Since the proposed database you intend to create is for your store/business, your business could/will likely become dependent on it. So you better make sure you understand that business and identify the requirements thoroughly. Test that evolving database repeatedly as you progress (test data, test scenarios, sample outputs.....).
Good luck with your project.

code explanation (1 Viewer)

Registered User.

I’m here to help

Registered User.

I’m here to help

Registered User.

Have been here a while

Registered User.

Attachments

MVP / VIP

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Have been here a while

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Nifty Access Guy

Registered User.

Super Moderator

Similar threads

Users who are viewing this thread