Relatively basic design help please

[leezer3]

OK, so I've managed to give myself a nice headache and nothing much else

What I'm trying to do is create a simple database tracking a model railway collection. From this, I have four basic item types (Loco, wagon, coach, other), for each of which I've created a table.
I've also created a secondary lookup table to hold manufacturer details, which works as intended.

Where I'm falling down is in creating an overall index, and from there a cascading form for data entry. I was hoping (Assuming?) that I could create a 'root' table, holding a unique ID for each model, and from there create a 1-1 relationship between these and the secondary tables, but I can't get that working right

My current structure looks like this:
Table: Stock Records
StockID (Autonumber, primary key)
StockType

Table: Locomotives
StockID (Primary key)
Various secondary data fields, these work.....

Table: Coaches
StockID (Primary key)
Various secondary data fields, these work.....

Table: Freight
StockID (Primary key)
Various secondary data fields, these work.....

Table: Other
StockID (Primary key)
Various secondary data fields, these work.....

Basically then, I'm hoping to create a form that cascades so when I select the stock type, it'll automatically cascade and create an entry in the correct table.
I've defined 1-1 relationships between all instances of StockID, but I'm confused and stuck
(I can create forms or enter data into any one table at the moment, but not cascade between them)

 

[Pat Hartman]

This isn't a cascading situation. Cascade Update only cascades changes in the PK to related records in dependent tables. It can never create records under any condition. You are using an autonumber which can never change and therefore there would never be a cascade.

The StockRecords table should contain all the common fields. The ancillary tables should only contain the StockID plus any attributes that are unique to that StockType.

Each StockType needs to be displayed on a separate form. I would use a mainform that displays the common information and a subform for the variable information. You can set the borders for the subform so it is seamless and just appears to be part of the main form. In the current event of the form, you would change the ControlSource property of the subform control to swap subforms. You would also have to do this in the AfterUpdate event of the Stocktype field when you are adding a new record.

 

[plog]

The ancillary tables should only contain the StockID plus any attributes that are unique to that StockType

What Pat said about his is very important. Does each item type table (Locomotives, Coaches, etc.) contain unique attributes to that type? Can you post the fields of those 4 tables? If they all have the same fields, you wouldn't need 4 tables, you would need only 1.

 

[leezer3]

What Pat said about his is very important. Does each item type table (Locomotives, Coaches, etc.) contain unique attributes to that type? Can you post the fields of those 4 tables? If they all have the same fields, you wouldn't need 4 tables, you would need only 1.

Some of the fields are unique, some aren't
Fields list:
Common:
StockID
Number
Livery
Weathered
Boxed
Coupler type
Notes
Catalog Number
Purchase Price
Model Value
Location
Image (Attachment)

Locomotives:
Locomotive type
Class
SubClass
Tractive Effort
Designer
Route Restriction
Weight

Coaching Stock:
Type
Designer
Passenger Capacity
Rake

Freight Stock:
Tare weight
Brake type
Wheel type
Intended Load
Removable Load Location

Other:
Powered
Weight
Restrictions

Basically, I've got a lot of unique fields for locomotives, and a few unique fields for everything else

Doing it with a single common table, and attributes for each single item was actually an initial thought, but I couldn't make this work correctly either. The attempt I made there was to add an autonumber primary key field to each of these subsidiary tables, but I couldn't figure out where/ how to link that to, so I shelved the idea.

In essence, how do I tell that a record is a locomotive & from there link the two together? I'm not well up on VB scripting, but are you suggesting adding something like this:
Add a new lookup wizard field to select the common choices, and in the form properties key an afterupdate event to trigger when this is changed.
Use the afterupdate to copy the autonumber StockID across into the appropriate table, and then use this to key back & retrive the data later?

 

[Pat Hartman]

When you create 1-1 relationships, only the "main" table has an autonumber PK. The other PKs are defined as Long Integer because they are technically foreign keys.

Create the main form and bind it to a query of the Common table. Then have 1 subform control large enough to hold the largest of the subforms. In the Current event of the form, place the name of the appropriate subform in the SourceObject property of the subform control:

Select Case Me.StockType
    Case "Locomotive"
        Me.CommonSubform.SourceObject = "sfrmLocomotive"
    Case "Coaching Stock"
        Me.CommonSubform.SourceObject = "sfrmCoachingStock"
    Case "Freight Stock"
        Me.CommonSubform.SourceObject = "sfrmFreightStock"
    Else
        Me.CommonSubform.SourceObject = "sfrmOther"
End Select

You need the same code for StockType. I would create a procedure that I call from both the Current Event of the Form and the AfterUpdate event of StockType.

Be careful with changing StockType. If you allow it after a record has been saved, you should delete the related record for the "old" type.