Stock Control Query (1 Viewer)

[Seph]

Hi everyone!

Please can you shed some light on my storm of confusion.

I want to create a stock control method wherby when a stock item reflects 0 quantity items then it shouldn't be able to be selected for future allocations.

I have 2 tables "StockT" & JobCostT".

The tables are joined by a One-To-Many relationship.

I created a query that looks as follows:

When I run the query, it works as instructed.

However in a form, I can still select stock items that have a 0 quantity value

Any Ideas would really help.

Thanks!

 

[NauticalGent]

Is your Form bound to your query (JobCostQ)?

 

[Gasman]

So apply the same criteria to the combo?

 

[Seph]

Is your Form bound to your query (JobCostQ)?

Thanks for taking the time to assist. Its a sub form, but yes it is bound to the Query.

 

[NauticalGent]

Ah...I just noticed that it was a ComboBox..

 

[Seph]

So apply the same criteria to the combo?

Thanks for the suggestion, I didn't know that Combo box's could be filtered. I'll look into that!

 

[Seph]

The issue is, as GasMan suggested, the recordsource of the ComboBox. Set it to the query

I really appreciate the advice. I'm going to try this and get back to you.

 

[Gasman]

You will also likely be advised not to store stock quantity, but to calculate it on the fly, with incoming, outgoing, audits etc, but first things first.

 

[Pat Hartman]

If the combo is on a continuous form, you probably don't want to restrict the list this way. It will make the product name "disappear" whenever the quantity goes to zero for an existing item.

A better solution is to add code in the control's BeforeUpdate event to check the quantity there and not allow the item to be selected if the quantity is not >0

If Me.cboStockID.Column(2) < 1 Then
    Msgbox "This item is not currently available.",vbOKOnly
    Cancel = True
    Me.cboStpclID.Undo
    Me.cboStockID.SetFocus
    Exit sub
End If

You also need similar code in the quantity event to ensure that you don't order more than is available.

The .Column property of the combo's RowSource is a zero based array. So the first item is Me.mycombo.Column(0). This is the same as Me.mycombo.Value and Me.mycombo (preferred). The second column of the RowSource is Me.mycombo.Column(1), etc. I selected .Column(2) assuming the quantity was third - ProductID, ProductName, QOH.

PS, Storing the on hand quantity is not recommended. It is better to calculate it on the fly. Create a totals query that totals the transaction values and you can use that query rather than the table as the RowSource for the combo. It may seem like it is simpler to just keep the on hand value in a table, but it really, really isn't. There is too many ways for it to go bad and without an audit trail, you can't figure out what has been affected.

 

[Seph]

Thanks for the suggestion, I didn't know that Combo box's could be filtered. I'll look into that!

Thanks again everyone. For anyone else who would like to know what this look like, below are some screenshots.
FYI this is called a Relational Combo Box:

Create the Query

Add a Combo Box to your Form

Then point the Combo Box to your Query

 

[Seph]

If the combo is on a continuous form, you probably don't want to restrict the list this way. It will make the product name "disappear" whenever the quantity goes to zero for an existing item.

A better solution is to add code in the control's BeforeUpdate event to check the quantity there and not allow the item to be selected if the quantity is not >0

If Me.cboStockID.Column(2) < 1 Then
    Msgbox "This item is not currently available.",vbOKOnly
    Cancel = True
    Me.cboStpclID.Undo
    Me.cboStockID.SetFocus
    Exit sub
End If

You also need similar code in the quantity event to ensure that you don't order more than is available.

The .Column property of the combo's RowSource is a zero based array. So the first item is Me.mycombo.Column(0). This is the same as Me.mycombo.Value and Me.mycombo (preferred). The second column of the RowSource is Me.mycombo.Column(1), etc. I selected .Column(2) assuming the quantity was third - ProductID, ProductName, QOH.

PS, Storing the on hand quantity is not recommended. It is better to calculate it on the fly. Create a totals query that totals the transaction values and you can use that query rather than the table as the RowSource for the combo. It may seem like it is simpler to just keep the on hand value in a table, but it really, really isn't. There is too many ways for it to go bad and without an audit trail, you can't figure out what has been affected.

Thank you for this solution. I like this approach.

Just a quick one, why is it not recommended to store the quantity in a table?

I was thinking of adding a method whereby quantity items are removed at the Invoicing stage.

This way I can keep track of item's allocated to which job, when, which supplier I procured it from and what their serial number is.

This is so in future if the item is faulty, then I know when it was purchased and where.

Is this possible or am I making things too complicated..

 

[Pat Hartman]

Just a quick one, why is it not recommended to store the quantity in a table?

I think you are asking about why you shouldn't store a sum. The answer to that lies in understanding normalization. Each field of a record should be dependent on the PK of that record and no other data. When you store a sum, the data represented by the sum comes from many records and so is not dependent on the PK of the row where it is stored. If you add a row or change a value in the sum, then the sum becomes invalid. A simpler example to understand is storing DOB and age. If my birthday is 8/6/2000 and my age is 22. My age will be wrong tomorrow and no one even changed anything.

People do store sums (you should NEVER store age since that only depends on the single record) when the sums are expensive in time to calculate due to the size of the data. However, it is risky. Today, you know what the app does and you are in control of everything and you KNOW there is a calculated field in a record so you will be careful to ensure that any process that adds/changes/deletes the rows that make up the sum also updates the sum. At some point in the future, someone else will likely be the db owner. If you have not carefully documented this anomaly or your replacement doesn't bother to read your documentation, he could easily make a mistake and let the sum get out of sync with the details. "Just say no".

In large applications, they frequently create a data warehouse that the user can query. In the data warehouse, you will see lookup fields expanded and many tables with summarized data because these things simplify the query process. Including Age. The idea of the warehouse is data is "as of" a specific date so age on that date is valid. However, individual records in the warehouse are NEVER updated. Each week or month depending on the schedule, the data warehouse is completely rebuilt. So, it is always a coherent structure. Nothing can ever get out of sync as it might with a transactional database.