Roy,

Wow, you have some project ahead of you. Both in Access and in physical
inventory work. Access has a steep learning curve, but there are a lot of
people on this forum who are they to help you so don’t let that intimidate
you.

Questions: Where to begin?
Answer: I always like to begin at the end. That is, the first thing I
do is figure out the objective of the project which you have done pretty
well. I would recommend that you write them down. The main purpose for
putting them on paper is it makes you think about everything you want to do.
Realize that this might take a couple of days or weeks to create a complete
list.

Secondly, you should sketch out ALL of the reports and inquiry screens
(including the fields) you would like to have someday. You will use the
inquiry and reports fields as a check list to insure you have captured all of
the required fields. If you did not enter the field or you can not calculate
the field, you can not have it on a report.

Also, once you have all of you reports and inquiry screens, you can then
decide how to make them all work together. If you don’t know the final
output, it is very hard to design the front end.

Also, once you have all of you reports and inquiry screens laid out, you can
check to make sure that you have met all of your objectives. If the reports
do not meet the requirements, they you have some more work to do.

Question 1) I am not sure how to build the relationships, such that some
receptacles have 'parents' or hierarchy and some do not. For example, a
drawer within a chest of 20, or a single large cardboard box.

Answer: There are a couple of ways to do this. Here is one way.
1. Given a Receptacle table of:

Key: Receptacle No:
Data Description
Location
Position
Multiple Drawer (Y/N)

I would have a Receptacle Drawer table. This table would have a two part
key: Receptacle No/Name, and drawer number. By definition, all receptacles
have one “drawer” and that is itself. So there will always be a drawer no 1
for all receptacles. You could write you VBA code to automatically create
drawer number 1 for all receptacles that do not have multiple drawers.


Receptacle Drawer table
Key: Receptacle No
Drawer No
Drawer Type (this is the key to the Drawer Type Desc Table)
Drawer size

The top half of the Receptacle data entry form would have the receptacle
information that is stored in the Receptacle table and the bottom half of the
form would be a sub-form (a form within a form) that is in continuous view
(display one line after the other – like excel). The sub-form would have all
of the drawers assigned to this receptacle.

If there is not enough room, you could put tabs on the form. The first tab
would have the receptacle information and the second tab would have the
drawer information.

In either case, you could make the sub-form or the second tab invisible if
the receptacle had no drawers. Yet another reason for the Multiple Drawer
question.

On the parts table, you would have both a receptacle number and a drawer
number fields. If the receptacle did not have multiple drawers, you could
have your code automatically set drawer number to 1 on the inventory form.



2) How to construct forms that will change 'downstream' fields, dependent
upon a certain choice made in a combo box. Say if I select a chest drawer, a
field will open to describe which chest

Yes you can do this. There are multiple ways to accomplish this. In the
combo box, you can return multiple fields from the query / sql statement.
You can then access those fields via the statement:
Assuming a SQL stmt like
Select InventoryTable PartNo, PartName, LocNo, RecNo, DrawerNo for the
combobox, your VBA code to access that addition data would be:

strPartName = me.cboPartNo.column(1)
strLocNo = me.cboPartNo.column(2)
strRecpName = me.cboPartNo.column(3)
strDrawerNo = me.cboPartNo.column(4)

You can then use that data to do whatever you wanted to do.



3) How to set up forms for the most efficient data entry.

There are a LOT of different opinions on this. The best is keep it simple,
don’t go crazy with different colors, adopt a standard and keep to that
standard (ie. The save button is always green, the Delete button is always
Red, etc.)

However, think about how you are going to be entering the data when you are
going from receptacle to receptacle. The main thing is enter the information
is a sequent that makes sense.

One thing, on you might wants to have a Part Genres table. That way you can
have as many genres for a given part as you want.

The table would look something like:

Part Genres table:
Key: Auto assigned number by access.
Data Part No (index - duplicates ok)
Genres code (the description to this code is in another table)


I have a question. You said the same part could be in different
receptacles. If that is the case, how will you handle that? Will you have
different part numbers for the same part? Or will you use the same part
number? If you use the same part number, then you will need a master part
tables that holds the description of the part and another table – Part Loc
table – that has the different locations of the part of the quantity on hand
in each location.

You might have to have a parts table that has a main part and two sub-forms.
The first sub-form would be the Parts Genres sub-form. The second sub-form
would be the Parts Location sub-form.

So much to think about! It’s late and I need some sleep.

Dennis