Modifying the Standard Inventory DB to allow for individual items?

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" wrote in message
...
Arggghhh.

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

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.

This link should be better for Table/relationship diagram

http://i157.photobucket.com/albums/t...tionships2.png

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.

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" wrote in message
...
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.

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.

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

"Swin" wrote in message
...
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.

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?

"BruceM" wrote:

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

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.

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.