Modifying the Standard Inventory DB to allow for individual items?

[Swin]

Hi all,

I'm looking to create a stock control log where by we can record goods
moving in and out of our company. We are a Not for Profit organisation that
looks after Video equipment for schools and colleges and so have many items
coming for our suppliers and moving to and from various studios. We need to
keep track or ALL individual serial numbers. We also have a need to link in
test records on this equipment.

The built in inventory DB in Access 2007 is quite good, but I'm sure there
would be a way to add a sub-form/table to the "Inventory Transaction List"
whereby multiple items in/out can have individual serial number recorded
(using a hand-held bar code scanner). Of course, I could just add one line in
this table for each individual item received or sent, but this will be time
consuming, especially if we have multiple items (or the same type - e.g. 20
specific camera models) move at one time. We don't really need to track
re-order levels but again we do need to track where an individual item is and
any given time.

On top of this we, test these individual items to make sure they all work
properly. These test are relatively simple but are different for each item -
e.g. a Camera will be tested for Pan/Tilt (up/down/left/right), Zoom in/out,
and focus; whereas a screen may be tested for showing an image at specific
resolutions.

I was not sure if I should simply put all possible tests for all items as
individual field in a single table, but this would be very wasteful of space.
Better (I think) would be to create individual tables for tests and then
somehow link these to the sub-table of the "Inventory Transaction List" so
that only the correct test will be shown for the item concerned. It guess it
would be possible to Use the "Category" field in the "Inventory" table to
link through individual items to the correct test schedule.

I'm thinking as I type this how it might be possible, but my first concern
is to get multiple entries of same type items with individual serial number
working. I am very rusty in this department.

Help Please...

 

[Swin]

Hmm, the more I think about this the more awkward I thinks it going to be.

In the "Inventory Transaction List" we have a Quantity field. Depending on
this quantity, will depend on how many items I will need to individually
record. If these individual items are recorded in a separate table, lets say
the "Individual Items" table, then changing the quantity field must
dynamically add records to this table that will then need to be updated with
the relevant serial numbers.

Of course, you may make a mistake with the quantity, so records will need to
be dynamically removed as well.

Whether something like this can be be dynamically added to a form I don't
know.

Any thoughts?

 

[Swin]

Cheers Steve

To relate this to the standard Inventory DB in 2007:

Your TblEquipmentModel = "Inventory Table"

In this table there is a Value List box bound to the "Category" field, which
is similar to your TblEquipmentType. (BTW, is there a simply way to convert
the Value List to proper tables? I will be moving the tables to SharePoint
later and whilst Value List fields are supported, they do not stop the user
typing in anything they want and ignoring the list.)

There is also a single field in the "Inventory Table" that can be expanded
to link to the TblEquipmentManufacturer.

In the Inventory DB, there is an additional Table called "Inventory
Transaction Table" that will record alterations to the stock level.

I could then link to this the TblEquipmentSerialNumber, so that indivual
item can be recorded for each transaction (i.e. we recieve 20 Cameras on the
4/5/09, then a camera goes out to location 1, on the 5/5 etc etc.

I should be able then create a query to let me know the history of a
specific item and it exact location!

It may also be posible to develop another form to make to movment of items a
little simpler. Once the serial has been entered, I should be able to find
the item and move it where I want....

Sounds simple!!!!

Someone came up with the idea of using a text box on the "Inventory
Transaction Form" that will allow the entry of a serial number via the Bar
Code Reader. The Bar code reader enters a Caridge Return after the bar code,
so the After Update even of the text box could be used to scan for this
character. A Record could then be added to the TblEquipmentSerialNumber that
relates to the current record selected in the "Inventory Transaction Table".

Have I got my thinking right?

 

[Swin]

Ok, one question about this - do I actually need the intermediate table?: -

If this works for you, you then would record equipment at locations .......

TblLocation
LocationID
Location

TblEquipmentLocation
EquipmentLocationID
EquipmentSerialNumberID
LocationID

Any individual item will only ever be one location (it can't be in two place
at one time). A single location may have multiple items. I would say that the
location is an extended property of the individual item - it always HAS to be
somehwere

In this case couldn't I just add to the TblEquipmentSerialNumber thus,

TblEquipmentSerialNumber
EquipmentSerialNumberID
EquipmentTransactionID
EquipmentSerialNumber
FromLocationID
ToLocationID

Then junk TblEquipmentLocation?

 

[Swin]

Ok, I've been struggling with this. the more I think about it, the more items
you realise you need to take care of

So Far table are

TblEmployees
ID
Company
Last Name
First Name
E-mail Address
Job Title
Business Phone

TblTransactionType
ID
Description
Add/Remove

TblInventoryTransactionList
ID
Transaction Item
Employee
Transaction Type
Quantity
Created Date
P0 Number
Comments

Tblnventory
ID
Item
Description
Equipment Type
Supplier
Manufacturer
Model
Reorder Level

TblEquipmentManufacture
ID
Equipment Manufacturer

TblEquipmentType
ID
Equipment Type

TblEquipmentSerial
ID
Serial Number
InventryItemID

TblEquipmentLocation
ID
TransactionID
EquipmentSerialNumberID
EquipmentLocationID

Suppliers
ID
Company
Last Name
First Name
E-mail Address
Job Title
Serial Number

TbleEquipmentManufacture
ID
Equipment Manufacturer

As with the original, the DB will be based around the "inventory Transaction
List". This is roughly what I "should"/want to be able happen.

1) Enter a new transaction (good in/out) for an item.

2) Most of the time have serial number attached, but even if they don't, we
need to record that an item is gone too/come from a specific location.

3) I then should scan in the individual items associated with the
transaction.

4) If the item scaned has a record in the TblEquipmentSerials Table, then
add a record to the TbleEquipmentLocation Table linking the Serial Number to
the Transaction number.

5) If the serial Number doesn't exist in the Serial Table, then we need to
add it to the table and link it to the Item, then do 4) above.

6) (similar to 5) If there is no serial number we need to a an "NA" record
to the Serial Table, link it to the Item, then do 4) above.


The date is included in the Transaction Table.

Does this sound reasonable?

 

[Swin]

Ahh... The benifits of "borowing" a template - even if it is an MS in built
one!

Barr the 'protocol', is the design saine and workable?

 

[Swin]

Arggghhh.

Just updated all the inbuilt Table, Query, Form and Field names in the
standard DB, now I've broke a lot of things!!!!!

 

[BruceM]

I hope you worked with a copy. In any case, most likely you need to go into
all your queries and SQL code to change the table and field names. Then you
will need to change the record source of your forms and reports to show the
new table and field names. You will also have to change the Control Source
of your controls to match the field names. In VBA code, and domain
functions such as DMax will have to be changed, along with any form or
report names.

Compiling the code may help you to find some of the names in the VBA code.
There is some code here that may help with field names:
http://allenbrowne.com:80/ser-73.html

I have not used the code, but considering who posted it I would imagine it
works as intended for the stated purpose.

Good luck!

 

[Swin]

Cheers Bruce. I think I have managed to sort out the vast majority of
refernce changes.

Here is an updated image of the table design, plus an update list regarding
what i think the DB should do and what I need to achieve (not complete)


[image]http://i157.photobucket.com/albums/t70/swinster/Relationships2.png[/image]

As with the original, the DB will be based around the "inventory Transaction
List". This is roughly what I "should"/want to be able happen.

1) Enter a new transaction (goods in/out) for an product.
2) Most of the time we have a serial number attached to an individual item,
but even if they don't, we need to record that an item is gone too/come from
a specific location.
3) I then should scan in the individual items associated with the transaction.
4) If the item scanned has a record in the TblEquipmentSerials Table, then
add a record to the TbleEquipmentLocation Table linking the Serial Number to
the Transaction number - also indicating the new location of the item
a. Could also automatically create a new transaction with the relevant item
if we need to.
b. It we have selected/editing a record with associated item but the scanned
item does not match the item selected then warn the user.
5) If the serial Number doesn't exist in the Serial Table, then we need to
add it to the table and link it to the Item selected, then do 4) above.
a. If no tem is selected then warn user and get them to select an item.
6) (similar to 5) If there is no serial number we need to add a "NA" record
to the Serial Table, link it to the Item, then do 4) above.
a. Could extend this later to provide for our own Tracking Number label.

What should we be able to do:

1) Look at a product and see the number we have plus the serials for
individual items and their location.
2) Check the history of a specific item.
3) Check what/how many items are at what locations.
4) Find the information for a individual item based on its serial.
5) Check Who supplies/manufactures what
6) Can’t think of anything else at the mo, but something will probably come
up.

 

[Swin]

This link should be better for Table/relationship diagram

http://i157.photobucket.com/albums/t70/swinster/Relationships2.png

 

[Swin]

To achieve this, I was thinking of using an unbound Text box (as suggested by
pbaldy on another forum) on the Transactions form that ran VBA code using the
AfterUpdate method. When an item is scanned or the serial in entered, there
could be a couple of possibilities: -

Firstly, the user may NOT have selected/created a record for a Transaction,
so the code will look up matching serial number in the
TblEquipmentSerialNumbers. It is possible that multiple matches maybe found
relating to different products, so the user must select the correct product.
Alternatively no serial would be matched, therefore the user must select the
appropriate product for the item scanned and so a new record must be added to
TblEquipmentSerialNumbers relating the serial to the Inventory Product. Once
the correct serial number record has been established, a new transaction
record will need to be created based on the Inventory Product related to the
Individual item, then a linking record needs to be added to
TblEquipmentLocation, so that the selected Serial is related to the
Transaction, finally the location the Item has come from and going to needs
to be assigned - hopefully both the Transaction and Serial should relate back
to the SAME Inventory Product....

Secondly, the user may have selected/created a transaction with an item
pre-selected. Therefore the serial number lookup in TblEquipmentSerialNumbers
only need to be concerned with matching Inventory Products. Again, if the
Serial doesn't exist, it needs to be added to TblEquipmentSerialNumbers, then
a record needs to be added to TblEquipmentLocation in the same way as shown
above.


Although the tables appear to work, I'm useless in creating/understanding
queries. For instance, the table TblEquipmentLocation links to both the
TblEquipmentSerialNumbers and TblInventoryTransactions, which in turn both
link back to TblInventory. Records added to the table need to be such that
for a given Transaction for a Inventory Product, the individual items must
also be related to the Product.

In the raw table design for:
TblEquipmentLocation
TransactionID -- FK to - TransactionID in TblInventoryTransactions
EquipmentSerialNumberID - FK to - EquipmentSerialNumberID in
TblEquipmentSerialNumbers

Whilst the look ups in the table work for the individual fields, editing the
raw tables 'could' give a spurious result in the the related Transaction and
Serial do not match to the same Inventory Product.

However, if I'm not sure if this matters as I obviously won't be editing the
table directly, and most of the record manipulation of this table will (!) be
handled by code - although I'm not sure if this is the best method.


The biggest hassle I can see is with item that don't have serial numbers.
Ideally we would assign a tracking number (possibly based on the AutoNumber
for the table) and print out a label with bar code - unfortunately we don't
have a bar code printer, but I suppose these are reasonably cheap.

 

[BruceM]

I do intend to look at this, but the situation is somewhat complex and I
won't have much time until maybe 6 hours from now.

 

[Swin]

No worries. Posting helps me focus my thoughts anyhow.

I'm trying to figure out the code need to return record sets and select the
correct record now.

 

[BruceM]

If it's a specific question I may be able to help, even if I can't do big
picture stuff just now.

 

[Swin]

Ok, getting there.

During my code execution, I setup an ADO recordset that is based on a query
using the scanned bar code and selected product of the Transaction. If no
product is selected and no serial number is matched (i.e. and empty
recordset), I need to ask the user to select a matching Product so I can ADD
a new record to the table.

I can open a new form suspending code execution using, :-

DoCmd.OpenForm FormName:="FrmInventorySelect", WindowMode:=acDialog

Then when I double click on a item I want the info to be sent back to the
calling form then close the FrmInventorySelect with DoCmd.Close.

I can get the ID of the Product selected, but how to I pass this info back
into the calling form?

I suppose I could set up an invisible text box on the calling form, then
alter the value of that, but this seem a bit messy. Normally I would pass a
parameter between routines, but I'm not sure how to do this with forms?

 

[Swin]

All is now good passing a parameter back.

I have another seemingly 'simple' question.

How do I maintain focus on the text box after it has been update using the
carriage return?

When you hit return the focus shifts from the text box to the next item in
the TAB list. I have tried a few separate methods to set the focus back to
the text box but nothing seem to work.

I basically want the "AfterUpdate" event to fire but then for the focus to
remain on the text box. I'm sure I'm missing something simple here.

Other than that, all is going well. I'll post the DB when functionally
complete.

 

[Swin]

Thanks very much for this. I completely redid the data structure today as it
wasn't making sense. I have one more issue again I suspect it is relativly
stright forward. Sorry to be a pain.

I want to copy the last record in a form/recordset to a new record in the
same form/record set.

Basically, when opening the form I immediately set the recordset to a new
record using:

DoCmd.GoToRecord acActiveDataObject, , acNewRec

If the user then enters a serial, I want to copy the last record in the
record set to this new record - as a sort of starting "deafult" value set for
the user to update if needed.

 

[Swin]

Hi all

Hi again,

I have finally got to the point where we have a functional system, albeit
with certain problems. As the DB was based on a standard MS one and I have
played with all of the Table, Field, Query and Form names, plus added some
extra tables and re-arrange relationships, some of the original functionality
has broken. Still this can be fixed a a later date.

My main concern is with some really odd VBA happenings. When I first start
the DB and the code runs by entering a value into the scan box, I get a
compile error saying that one of my "with" statements is not a proper user
defined type!!!! I have also had an issue saying that my parameter passing
are one the wrong type - but this is totaly false (as they are both defined
as long)!!! In addiiton, I sometime get a break in the code although no
breakpoint has been defined!!!

The really odd thing though is that if I simply close the debugger and run
the same action again by making change in the scan box and pressing return,
everything works as it should!!! Argghhh. I really don't understand this. I
have tried a compact and repair but no joy.

Any ideas?

Also, I'm getting really frustrated at the form sizing. No mater what I do I
can't seem set a form to a fixed size. As they get opened in different modes,
the sizes change completely. Again, really frustrating.

 

[BruceM]

Place the following line at the top of the module's code, below Option
Compare Database:
Option Explicit

Try compiling the code after doing that.

Please post the code that is causing the problems.

 

[Chris Swinney]

Hi Bruce,

This appears to be a machine specific issue. I ran the DB on another machine
and had no problem. I then compacted/repaired the DB and moved it back to
the main machine and the DB ran as it should until I made another change in
the code, then it once again stoped execution at some spurious point.

I do often use the Option Explicit statement so that I can explicitly
declare variables. I don't think my coding is to shabby, its just working
with access I'm not that clued up on.

I'm not sure what else I can do here. I might try to repair the Office
install but I don't hold out much luck. I have asked MS athe question in the
direct access groups so I see what they say.

CHris