For what follows, assume all ID fields that match the table names (e.g.
PartID in tblPart) are autonumber fields, and all other ID fields are Number
(Long Integer) fields.

As I understand, "parts" are general areas of the car, not specific parts.
The Parts table would contain a listing of all possible parts for all cars.
tblVehType is a listing of all vehicle types. One vehicle type has many
associated parts, and one part may be associated with many vehicle types,
which is why you need tblVehTypePart (a junction table). It is joined to
tblVehType and tblPart through the like-named fields (e.g. PartID joined to
PartID). To create the lists, create a form based on tblVehType (or better,
on a query based on the table), with a subform based on tblVehTypePart (or a
query). Bind a combo box to PartID on the subform. Use a query based on
tblPart as the Row Source for the combo box. Now you can create your list
for each vehicle type.

As was suggested elsewhere in this thread, you need a Claims record to enter
the actual information about what was damaged. The Claims table would have
ClaimID, Vehicle information, VehType (a number field), and other fields such
as ClaimDate and so forth. You will also need a ClaimDetails table. This
would be linked to tblClaim by way of ClaimID.

tblClaim
ClaimID
VehMake
VehModel
VehType
ClaimDate
etc.

tblClaimDetails
DetailID
ClaimID
PartID

Create a form based on tblClaim, with a subform based on tblClaimDetails.
The subform has a combo box bound to PartID. You will need code something
like this in the main form:

Function PartList()

Dim strSQL as String
Dim lngVehType as Long

lngVehType = Me.VehType

strSQL = "SELECT * FROM tblVehTypePart " & _
"WHERE [VehType] = " & lngVehType
Me.SubfromControlName.Form.ComboBoxName.RowSource = strSQL

End Function

Then in the AfterUpdate event of the combo box on the main form bound to
tblVehType, call the function.

Call PartList

Do the same in the form's Current event.

What you are asking is somewhat involved, although quite common. This is
just a summary sketch. Ask more questions as needed.


Pamela wrote:
Thanks so much, Steve. I did exactly as you instructed but I don't
understand how/where the data is going to get connected. I have my VehTypes
in its table and I have the Parts List in its table. I recognize that we
established a junction table for the two but how does it get specified which
parts go with which VehType?? Do I need to create another form to try to do
that and, if so, what's the best method for this??

Thanks so much!

Pamela

First you need tables like the following:
TblVehType
[quoted text clipped - 59 lines]

.

--
Message posted via http://www.accessmonster.com