Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Limiting user choice to subform contents

Discussion in 'Information Technology' started by Jane Shinnie, Jul 28, 2006.

  1. Jane Shinnie

    Jane Shinnie
    Expand Collapse
    Guest

    Hi

    I hope someone can help with this.
    I am trying to set up a database to track our schools use of printer
    cartridges. We have a variety of printers which can use just a single black
    cartridge or up to 5 colour cartridges at the same time. Each cartridge has
    a unique code and each printer has defined cartridges which fit it.
    I have set up 5 tables
    1 Cartridge Type (fields CartridgeCode (primary key) and CartridgeType
    (black or colour)
    2.Cartridge Code (fields CartridgeCode, (PK) CartridgeColour, Stock Level,
    ReorderLevel)
    3. Orders (OrderID (PK), PrinterID, CartridgeCode, UserID, DateTaken,
    CartridgeType)
    4. Printers (PrinterID (PK), PrinterLocation, PrinterMake, PrinterModel,
    BlackCartridge, ColourCartridge1, ColourCartridge2,ColourCartridge3,
    ColourCartridge4, ColourCartridge, these latter are text and contain the
    cartridgecode relevant)
    5. Users (UserID (PK), LastName, FirstName)
    There are relationships established between PKs and foreign keys in the
    tables

    I want the database user to be able to select the printerID and then have
    the form show only those cartridges which go with that printer. I have done
    this by using the Order table as the record source of the form and putting
    in a subform based on the Printers table and using comboboxes on the
    mainform to select the PrinterID, the UserID and text boxes to input the
    date taken. The subform shows all the cartidges which will "fit" the chosen
    printer, but how can I present the form user with only the option to input
    one or several of the cartridges listed on the subform? I have struggled
    with this for weeks, my visual basic is not up to the task, and any help
    most gratefully received!!
    Thanks
    Jane
     
  2. Loading...

    Similar Threads Forum Date
    After FIR, Social Media Users Wary of Offensive Jokes on Sardars Information Technology Oct 2, 2013
    USA US govt secretly collecting data on millions of Verizon users: Report Breaking News Jun 6, 2013
    using spiritual name as a username Questions and Answers Aug 23, 2012
    Audience of 3 Million Users Receiving Anti-Sikh Messages Hard Talk Sep 17, 2010
    General How can I change my username ? Hard Talk Sep 4, 2010

  3. Graham Mandeno

    Graham Mandeno
    Expand Collapse
    Guest

    Hi Jane

    The problem is with table #4 (Printers). You have six fields which all
    contain data of the same type (cartridge code) and this violates the
    principles of normalisation. This is possibly *the* most important factor
    in database design, so if it's something unfamiliar to you, check out some
    of these excellent links on Jeff Conrad's site:
    http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

    Let's assume you have one or more printers of the same make and model, and
    that each make/model uses a defined combination of cartridges. However, a
    given cartridge could be used in more than one printer model.

    What you have here is a one-to-many relationship between printers and
    printer models and a *many to many* relationship between printer models and
    cartridges.

    So, your tables should look more like this:

    Printers: (PrinterID [PK], PrinterLocation, PrinterModel [FK to
    PrinterModels])

    PrinterModels: (PrinterModelID [PK], PrinterMake, PrinterModel)

    PrinterCartridges: (PrinterModelID, CartridgeCode [both fields make
    composite PK])

    This last table is called a "junction table" and represents the many-to-many
    relationship through two one-to-many relationships. Each PrinterModel is
    relates to one or more PrinterCartridges records (the cartridges used in
    that model printer) and each CartridgeCode record is related to one or more
    PrinterCartridges records (the printer models that use that cartridge).

    Now it's easy to make a query which lists only those cartridges that go with
    a particular printer model (and therefore with a particular printer).
    --
    Good Luck!

    Graham Mandeno [Access MVP]
    Auckland, New Zealand

    "Jane Shinnie" <jshinnie@lineone.net> wrote in message
    news:447079f2_3@mk-nntp-1.news.uk.worldonline.com...
    > Hi
    >
    > I hope someone can help with this.
    > I am trying to set up a database to track our schools use of printer
    > cartridges. We have a variety of printers which can use just a single
    > black cartridge or up to 5 colour cartridges at the same time. Each
    > cartridge has a unique code and each printer has defined cartridges which
    > fit it.
    > I have set up 5 tables
    > 1 Cartridge Type (fields CartridgeCode (primary key) and CartridgeType
    > (black or colour)
    > 2.Cartridge Code (fields CartridgeCode, (PK) CartridgeColour, Stock Level,
    > ReorderLevel)
    > 3. Orders (OrderID (PK), PrinterID, CartridgeCode, UserID, DateTaken,
    > CartridgeType)
    > 4. Printers (PrinterID (PK), PrinterLocation, PrinterMake, PrinterModel,
    > BlackCartridge, ColourCartridge1, ColourCartridge2,ColourCartridge3,
    > ColourCartridge4, ColourCartridge, these latter are text and contain the
    > cartridgecode relevant)
    > 5. Users (UserID (PK), LastName, FirstName)
    > There are relationships established between PKs and foreign keys in the
    > tables
    >
    > I want the database user to be able to select the printerID and then have
    > the form show only those cartridges which go with that printer. I have
    > done this by using the Order table as the record source of the form and
    > putting in a subform based on the Printers table and using comboboxes on
    > the mainform to select the PrinterID, the UserID and text boxes to input
    > the date taken. The subform shows all the cartidges which will "fit" the
    > chosen printer, but how can I present the form user with only the option
    > to input one or several of the cartridges listed on the subform? I have
    > struggled with this for weeks, my visual basic is not up to the task, and
    > any help most gratefully received!!
    > Thanks
    > Jane
    >
     

Share This Page