Microsoft Access - I want to eliminate the item that was selected in a list box. Would you show me how

Asked By Mick Kobayashi on 17-Apr-17 01:35 PM

I am building a simple database for my cross-dock warehouse. That is, unlike the warehouse of a retailer, my warehouse don't store the same products over and over. Instead, each time we receive a shipment, we assign a dock receipt number to it, and we use the dock receipt number as the ID of the shipment. Once we ship it out from our warehouse, we never use the number again. Thus, I want to eliminate the dock receipt number from the list box on my 'shipping' form once it is selected. My database is working alright, except the list of of dock receipt number gets bigger and bigger, by showing unnecessary candidates.

Robbe Morris replied to Mick Kobayashi on 18-Apr-17 10:38 AM

Why wouldn't you just add a column for IsActive and set that flag to false once the item has shipped. Then, adjust your UI query to only display those items with active items?

The other alternative is to create an Inactive table and copy that record into it when shipped. Upon confirmation that the record was successfully inserted, you delete it from the active table.

As a general rule, you don't want to delete data because someday you are likely to need it for history reporting purposes.