How can I handle this inventory problem?
Richmond Dyes
I am trying to solve a simple inventory control issue. Here is the
problem. I have built a simple ordering system for our supply
department in the hospital I work at. I have a table of all the items
in inventory. This table consists of itemname and category the items
in. There is no quantities of the items on the list kept. Just the
name of the item. In my supply order form, I have a drop down list that
is populated from the inventory table. My problem is this. We want to
be able to remove an item that is not in stock anymore. That becomes an
issue because if we remove the item, it does not show up on the drop
down list in the order form. This of course is not good because then
the historical orders cannot be looked up because say the item red pen
is no longer in the inventory table so it can't be referenced. Also,
when I print the monthly summary reports, this item doesn't exist in
the inventory so it can't be referenced either. I thought I had a
workaround for this by putting in a logical field called instock. I
then queried for my drop down list to get only the items that instock
was true. This again caused an issue if I looked at an old order with
that red pen on it. Now red pen was not in the drop down list so of
course, you could not advance past that order because the item red pen
no longer was in the drop down list. Here is my data model. with the
pertinent fields. I hope what I wrote makes sense. Any suggestions?
order.db orderdetail.db Inventory.db
ordernum ------>ordernum
dept itemname <-------itemname
date quantity category
Instock
Carolyn Wendover
I use something similar to keep track of current doctors. That is, I have a
list of doctors and a field in there to indicate if they are active in the
program. My drop down list only shows active doctors, but if I am looking
at a record which references an inactive one the name and information still
shows up - I just cannot select that doctor when entering a new record.
Do you have some code on your canDepart method for that drop down field
which is checking to be sure the item is on the drop down list? If so, I
would remove that. If you need to ensure they only pick from your list then
you could do something like I have implemented. I have two fields on top of
each other for doctor. One is just the doctor name displayed from the
linked full list of doctors. The other is my drop down list field. The
name field is visible and on top and the dropdown field is behind that and
invisible. If the user starts to type in the name field I make that
invisible and show the drop down list. Once they pick a name, I look it up
in the underlying table and store that ID in another invisible field on the
form. That causes the name field to now reflect the current selected
doctor. I then make the name field visible and hide the drop down field.
This is kind of a pain but the user really wanted a drop down field. My
preference would have been to have the name field non editable and capture
the key char or key physical event and use that to display my own small
lookup form or pop-up menu (if the list was small enough). Then I fill the
name field from code.
In both of these the result is the same - I can display any value whether it
is currently available for selection or not, and I restrict their data entry
to only valid selections.
HTH,
Carolyn
"Richmond Dyes" <rd...@monroehosp.org> wrote in message
news:4163e096$1_2@cnews...
Dennis Santoro
You have simply mixed your inventory list with your in stock list. Inventory
should be the comprehensive list of what might be available and stock should
be the list of how many are on hand. When looking up the list and creating
orders you simply link the stock table to the inventory table, show the
inventory for the lookup call and the quantity available will show.
Inventory
IID long int key
SKU
Description
etc.
Stock
IID long int key
qty
Denn Santoro
President
Resource Development Associates
http://www.RDAWorldWide.Com
Offices in the United States and Germany
Providing solutions to health care, business, governments and non-profits
since 1982