Hi
I have never done this - so may be totally wrong - but if were me starting
this project I think I would simplify the whole thing from what you describe
in your post.
Looking at your post I think you may be looking at this the wrong way round.
When creating an access process you should first look at what you want the
end result to be and then build the process to cope with that and any
"add-ons". I also tend to allow options for the client "may@ want to add.
Your process - I think - requires that you can look at a number of storage
units and display, report, etc on the contents. I assume the location does
not move unless you have multiple storage areas like 2 factories, warehouses,
???.
This means that you will have a number of storage locations within a set area.
A bit like drawing squares on the floor and then putting metal boxes into
these shapes - with the option of moving the metal boxes. But of course the
squares you have drawn on the floor don't move. ????
So - if this is correct - it seems quite simple (forget the form for now).
Create a table with the location of each area (that’s the drawn squares on
the floor)
tblLocation
LocationID = Autonumber
LocationNumber = Text
LocationRef = Text (this could be a description of exactly where the
square is or, better still a grid reference on a plan of the area)
LocationSize = Number (this would be usefully for reports if you want to
show the total amount of storage "used" and the total "available", etc, etc)
Add other rows as needed to this table
You will also need a table to containing the details of the actual storage
units (the metal boxes). As these can be moved this will need a relationship
with tblLocation.
Oh – if the “shelf – life†of the unit contents are limited you could add
this to the table. it will important to know if the apples only last for 2
weeks or the car oil needs certain fire precautions, etc. So you could add a
few field to the table to hold this data.
tblUnits
UnitID = AutoNumber
UnitID = Number (this is the linking field to the primary of tblLocation)
UnitSize = number (this is useful incase you need to view, report, etc
units that would fit into available empty locations, and also if you need to
see simply what’s free, if a customer calls)
Plus add other field that refer to the units such as age, cost, truckable,
hoistable, etc.
You could add another table if the contents of the units may change – if not
then add a field to tblUnits – it my be the one week a unit will contain
apples and then later you may put in car part, etc. If you do create a new
table tie it to tblUnits with a relationship with UnitID.
Next the form
Create a main form from a query based on tblLoction.
Create a popup (just one for now) form from a query based on tblUnits.
In the main form place (if you want) a “Plan†which could be a map of the
location with text boxes in places. You MUST have the option (normally) in a
combo of changing the contents and positions of the units with the location.
So you should have a combo showing all units, with column 2 showing the
current location. Also have a combo showing all the locations – poss. with a
column showing which units are in this location. You could add a filter
button for this combo to show which location are full or empty.
I would also create the popup. As you give the focus to a text box this
would popup giving the details of what is in the unit (filter it on
LocationID).
Etc
Etc.
I hope this has given you some ideas. This seems to be one of the DB that
unless you understand the actual process you will never be able to come up
with a good idea so you are the one who needs to look at it.
Good luck
