Limiting user choice to subform contents

[Jane Shinnie]

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

 

[Graham Mandeno]

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).