Hi Bhav
What you are talking about here is usually called "subclassing", not
"inheritance" in the context of database design - I think that's where the
confusion has crept in.
You can create your "Item" table with a primary key (an autonumber
"ItemID" might be safer than using serial number) and another byte numeric
field named "ItemType". Then add another unique index comprising ItemID
*and* ItemType.
Create another table named "ItemTypes" with PK "ItemType" (byte numeric)
and a text field "ItemTypeName". Populate this with:
1 Base Unit
2 Printer
3 Monitor
...etc
Now, for each type of item, create another table with a two-field primary
key: ItemID/ItemType, and whatever other item-specific fields you require.
For example, for BaseUnits you have:
ItemID (long numeric)
ItemType (byte numeric, default value:1 validation rule: =1)
CPUType
RAM
...etc
Note that for each of these tables, the ItemType defaults to (and can only
be) the corresponding type code from the ItemTypes table.
Next, create relationships:
ItemTypes.ItemType > Items.ItemType (one-many)
and for each of the subclass tables:
Items.ItemID/ItemType > Subtable.ItemID/ItemType (one-one)
(this relationship can have cascading deletes if you wish)
Now, you cannot add a record to one of the subtables unless a record
matching both ItemID *and* ItemType exists in the Items table.
Furthermore, you cannot change the ItemType in the Items table if a
corresponding record exists in a subtable.
For data entry, create a main form based on the Items table and a subform
based on each of the subtables. Add one of the subforms (the largest) to
your main form, and set the properties of the subform control as follows:
Name: sbfSubClass
SourceObject: <blank>
LinkMasterFields/LinkChildFields: ItemID;ItemType
(these should have been set automatically)
For the ItemType control on the main form, use a combo box with its
RowSource set to the ItemTypes table.
Now some code... add the following to your form module:
Dim aTables As Variant
Dim aSubforms As Variant
Private Sub Form_Load()
aTables = Array("", "BaseUnits", "Printers", "Monitors")
aSubforms = Array("", "sbfBaseUnits", "sbfPrinters", "Monitors")
End Sub
Private Function LoadSubform()
Dim sSubForm As String
sSubForm = aSubforms(Nz(ItemType, 0))
With sbfSubClass
If .SourceObject <> sSubForm Then
.SourceObject = sSubForm
End If
End With
End Function
Private Sub ItemType_BeforeUpdate(Cancel As Integer)
Dim sTable As String, sMsg As String
sTable = aTables(Nz(ItemType.OldValue, 0))
If Len(sTable) <> 0 Then
If DCount("*", sTable, "ItemID=" & ItemID) <> 0 Then
sMsg = "There is data in the " & sTable & " table for this item." _
& vbCrLf & "If you change this item to a " & ItemType.Text _
& " then this " & sTable & " record must first be deleted." _
& vbCrLf & "Do you wish to continue?"
If MsgBox(sMsg, vbYesNo Or vbExclamation _
Or vbQuestion Or vbDefaultButton2) = vbYes Then
CurrentDb.Execute "Delete * from " & sTable _
& " where ItemID=" & ItemID
Else
Cancel = True
ItemType.Undo
End If
End If
End If
End Sub
Insert your own table names and subform names in the Array statements.
Note that these names could all be stored in tables if you prefer.
Set both the form's OnCurrent property and the ItemType combo box's
AfterUpdate property to:
=LoadSubform()
Hope this gives you enough to get going. Please post back and report how
you got on
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
Bhav said:
well it's not the case of making a query
basically what is it I'm creating a system that will hold details of
monitors, base units, laptop and printer... which of all will have a
serial number, make and model but after the difference will be that they
will hold different details out it's self like printer type, spec of the
pc/laptop and size of the monitor.
so the idea was that I would create a super-table called Item which would
hold common details like serial number, make and model then would be
linked to the 3 different tables Base unit/Printers and monitor which
would inherit the common fields from the 'item' table
Thanks for help
