Modifying the Standard Inventory DB to allow for individual items?

Discussion in 'Microsoft Access Database Table Design' started by Swin, Aug 5, 2009.

  1. Swin

    Swin Guest

    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, Aug 5, 2009
    #1
    1. Advertisements

  2. Swin

    Swin Guest

    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" wrote:

    > 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, Aug 5, 2009
    #2
    1. Advertisements

  3. Swin

    Swin Guest

    Re: Modifying the Standard Inventory DB to allow for individual it

    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?



    "Steve" wrote:

    > <...get multiple entries of same type items with individual serial number
    > working ...>
    >
    > TblEquipmentType
    > EquipmentTypeID
    > EquipmentType
    >
    > TblEquipmentManufacturer
    > EquipmentManufacturerID
    > EquipmentManufacturer
    >
    > TblEquipmentModel
    > EquipmentModelID
    > EquipmentManufacturerID
    > EquipmentTypeID
    > EquipmentModel
    >
    > TblEquipmentSerialNumber
    > EquipmentSerialNumberID
    > EquipmentModelID
    > EquipmentSerialNumber
    >
    > If this works for you, you then would record equipment at locations .......
    >
    > TblLocation
    > LocationID
    > Location
    >
    > TblEquipmentLocation
    > EquipmentLocationID
    > EquipmentSerialNumberID
    > LocationID
    >
    >
    > Steve
    >
    >
    >
    >
    >
    > "Swin" <> wrote in message
    > news:...
    > > 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, Aug 5, 2009
    #3
  4. Swin

    Swin Guest

    Re: Modifying the Standard Inventory DB to allow for individual it

    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, Aug 5, 2009
    #4
  5. Swin

    Swin Guest

    Re: Modifying the Standard Inventory DB to allow for individual it

    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?

    "Steve" wrote:

    > I would suggest adding EquipmentLocationDate to TblEquipmentLocation. It
    > would then be easy to get the location from where the equipment came from by
    > looking at the EquipmentLocationDate just prior to the date an equipment was
    > moved to the current location.
    >
    > EquipmentLocationDate also will allow you to answer where was a piece of
    > equipment on April 1, 2009 as well as allow you to display a history of
    > where a piece of equipment has been.
    >
    > Steve
    >
    >
    >
    >
    >
    > "Swin" <> wrote in message
    > news:...
    > > 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, Aug 5, 2009
    #5
  6. Swin

    Swin Guest

    Re: Modifying the Standard Inventory DB to allow for individual it

    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?

    "Steve" wrote:

    > Four suggestions to start off with .........
    > 1. Rename the PKs in your tables to table name less "Tbl" plus ID; ex.
    > EmployeeID. You then will always know what table you are working with in
    > queries, forms and reports.
    > 2. Where you have a foreign key, give it the same name as the PK field it
    > is related to, ex. from 1, in TblTransactionType change PK to
    > TransactionTypeID. Then in TblInventoryTransactionList, change Transaction
    > Type to TransactionTypeID.
    > 3. After you do 1 and 2, remove all spaces in field names. You will be
    > glad you did as you create queries, forms and reports.
    > 4. Be consistent in naming your tables. Most tables begin with "TBL".
    > That's very good! You will always know that you are working with a table in
    > queies, forms and reports. Change Suppliers to TblSuppliers. Also, remove
    > the "e" in TbleEquipmentManufacture.
    >
    >
    > Steve
    >
    >
    >
    > "Swin" <> wrote in message
    > news:...
    > > 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
    > >
    > > in > 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?
    > >
    > > "Steve" wrote:
    > >
    > >> I would suggest adding EquipmentLocationDate to TblEquipmentLocation. It
    > >> would then be easy to get the location from where the equipment came from
    > >> by
    > >> looking at the EquipmentLocationDate just prior to the date an equipment
    > >> was
    > >> moved to the current location.
    > >>
    > >> EquipmentLocationDate also will allow you to answer where was a piece of
    > >> equipment on April 1, 2009 as well as allow you to display a history of
    > >> where a piece of equipment has been.
    > >>
    > >> Steve
    > >>
    > >>
    > >>
    > >>
    > >>
    > >> "Swin" <> wrote in message
    > >> news:...
    > >> > 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, Aug 5, 2009
    #6
  7. Swin

    Swin Guest

    Re: Modifying the Standard Inventory DB to allow for individual it

    Arggghhh.

    Just updated all the inbuilt Table, Query, Form and Field names in the
    standard DB, now I've broke a lot of things!!!!!
     
    Swin, Aug 6, 2009
    #7
  8. Swin

    BruceM Guest

    Re: Modifying the Standard Inventory DB to allow for individual it

    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
    news:...
    > 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, Aug 6, 2009
    #8
  9. Swin

    Swin Guest

    Re: Modifying the Standard Inventory DB to allow for individual it

    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, Aug 6, 2009
    #9
  10. Swin

    Swin Guest

    Swin, Aug 7, 2009
    #10
  11. Swin

    Swin Guest

    Re: Modifying the Standard Inventory DB to allow for individual it

    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.
     
    Swin, Aug 7, 2009
    #11
  12. Swin

    BruceM Guest

    Re: Modifying the Standard Inventory DB to allow for individual it

    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
    news:...
    > 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, Aug 7, 2009
    #12
  13. Swin

    Swin Guest

    Re: Modifying the Standard Inventory DB to allow for individual it

    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.
     
    Swin, Aug 7, 2009
    #13
  14. Swin

    BruceM Guest

    Re: Modifying the Standard Inventory DB to allow for individual it

    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
    news:...
    > 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, Aug 7, 2009
    #14
  15. Swin

    Swin Guest

    Re: Modifying the Standard Inventory DB to allow for individual it

    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.
    >
     
    Swin, Aug 8, 2009
    #15
  16. Swin

    Swin Guest

    Re: Modifying the Standard Inventory DB to allow for individual it

    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, Aug 10, 2009
    #16
  17. Swin

    Swin Guest

    Re: Modifying the Standard Inventory DB to allow for individual it

    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, Aug 11, 2009
    #17
  18. Swin

    Swin Guest

    Re: Modifying the Standard Inventory DB to allow for individual it

    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.
     
    Swin, Aug 12, 2009
    #18
  19. Swin

    BruceM Guest

    Re: Modifying the Standard Inventory DB to allow for individual it

    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.

    "Swin" <> wrote in message
    news:...
    > 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, Aug 13, 2009
    #19
  20. Re: Modifying the Standard Inventory DB to allow for individual it

    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

    "BruceM" <bamoob_at_yawhodotcalm.not> wrote in message
    news:%...
    > 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, Aug 14, 2009
    #20
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Lynn Trapp

    Re: Inventory/asset tracking question

    Lynn Trapp, Sep 15, 2003, in forum: Microsoft Access Database Table Design
    Replies:
    2
    Views:
    423
    Lynn Trapp
    Sep 16, 2003
  2. Guest

    Inventory Tables Design

    Guest, Dec 23, 2003, in forum: Microsoft Access Database Table Design
    Replies:
    2
    Views:
    311
    Guest
    Dec 30, 2003
  3. DD

    Equipment Inventory and Issues Template

    DD, Jan 19, 2004, in forum: Microsoft Access Database Table Design
    Replies:
    1
    Views:
    1,455
    Petrucci2000
    Jan 20, 2004
  4. chang

    Modifying northwind to allow a variable price (or in a new databse)

    chang, Jul 27, 2004, in forum: Microsoft Access Database Table Design
    Replies:
    1
    Views:
    173
    Guest
    Jul 28, 2004
  5. Guest

    Access 2003: Can a control/field allow multiple items to be select

    Guest, Feb 18, 2005, in forum: Microsoft Access Database Table Design
    Replies:
    1
    Views:
    384
    Douglas J. Steele
    Feb 18, 2005
Loading...

Share This Page