SubForm Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am working on a database which involves 50+ vehicles, each of which can
carry from a list of 80+ items of equipment, and varying quantities of each
item. Also, most vehicles will carry most of the items in the list.

I have in mind a main form which shows the vehicle by registration number
and driver details, with a sub form (a spreadsheet) which lists all the items
of equipment, price, qty (for input) and total value.

My problem is linking the two forms. Does anybody have any suggestions,
please?
The obvious link would seem to be registration number, but if I add this
field to the equipment table I have to enter the registration number for
each item
of equipment.

Thanks in advance for any help on this one.
 
GeeJay said:
I am working on a database which involves 50+ vehicles, each of which
can carry from a list of 80+ items of equipment, and varying
quantities of each item. Also, most vehicles will carry most of the
items in the list.

I have in mind a main form which shows the vehicle by registration
number and driver details, with a sub form (a spreadsheet) which
lists all the items of equipment, price, qty (for input) and total
value.

My problem is linking the two forms. Does anybody have any
suggestions, please?
The obvious link would seem to be registration number, but if I add
this field to the equipment table I have to enter the registration
number for each item
of equipment.

Thanks in advance for any help on this one.
Click to expand...

No, if you build your form/subform properly the linking field is automatically
copied to the subform records.
 
I am working on a database which involves 50+ vehicles, each of which can
carry from a list of 80+ items of equipment, and varying quantities of each
item. Also, most vehicles will carry most of the items in the list.

I have in mind a main form which shows the vehicle by registration number
and driver details, with a sub form (a spreadsheet) which lists all the items
of equipment, price, qty (for input) and total value.

My problem is linking the two forms. Does anybody have any suggestions,
please?
The obvious link would seem to be registration number, but if I add this
field to the equipment table I have to enter the registration number for
each item
of equipment.
Click to expand...

You have a Many (vehicle) to Many (equipment) relationship - so you
need *another table*.

This table would have a VehicleID (linking to the vehicles table), an
EquipmentID (linking to the equipment table), a quantity field and any
other fields needed for *this* item on *this* vehicle. Your subform
would be based on this table.

John W. Vinson[MVP]
 
The obvious link would seem to be registration number

table relationships need to be definitely and specifically defined long
before you get to the point of building forms. recommend you read up on data
modeling and normalization, then review your current tables/relationships
design and make any corrections or adjustments needed to bring your design
in line with data normalization rules. see
http://home.att.net/~california.db/tips.html#aTip1 for more information.

once you've got your tables/relationships set up correctly, you're ready to
build forms that will support those relationships, and work well for you.
mainform/subform setups will usually duplicate the table relationships
you've already established at the table level. if you run into specific
problems when you get to this point, post a request for specific advice,
suggestions, etc - there's always folks here to give you a hand.

hth
 
Back
Top