Best way to design tables for cascading on my form

[Pamela]

My company inspects damaged cars for insurance purposes. Up to now,
(relating to this issue) I've had tblDamageArea populate a list box on my
form where the user can select the various parts of the car that were
damaged. I've had this list include parts for multiple kinds of cars and I'd
like to make this list cascade to include only those parts relevant to the
car type inspected.

I've created a tblVehType to separate Sedan, Coupe, 4DoorTruck, SUV, etc.
which populates a cbo on my form where the user will select the type which
will then be used as the criteria for cascading the list box.

Before that, I created tblParts w/ the Parts but then also had each vehicle
type listed out w/ Yes/No boxes where I then selected which parts go with
which veh. This is where I'm really needing help. I know this isn't the
best way to do it but can't figure out how else at this point to use the
tblVehType and connect the related parts to each veh.

Many of the parts are the same for all of the vehicles -- for instance the
front end parts - they all have bumpers, fenders, hoods, windshields, etc.
but the doors (2 vs. 4), quarters (or beds), and rear ends all vary. I
imagine that I'll use a query to put them all together for the list box but
what is the easiest and best way to set up my tables & dictate which parts go
with which vehs?

Thanks for your help!

Pamela

 

[Steve]

First you need tables like the following:
TblVehType
VehTypeID
Vehtype

TblPart
PartID
Part

TblVehTypePart
VehTypePartID
VehTypeID
PartID

Then create a query that includes the three above tables. The columns in the
query need to be:
VehTypePartID from TblVehTypePart
Part from TblPart
VehTypeID from TblVehType

Set Part to sort ascending. Set the criteria for VehTypeID to:
Forms!NameOfYourForm!cboVehType

Put the following code in the AfterUpdate event of cboVehType:
Me!NameOfTheListbox.Requery

Steve
(e-mail address removed)

 

[KARL DEWEY]

had each vehicle type listed out w/ Yes/No boxes where I then selected
which parts go with which veh.
No check boxes. Consider this scheme --
Vehicles - field for VehicleType
Parts - one-to-many relationship VehicleType_Parts
VehicleType - one-to-many relationship VehicleType_Parts

Claim - one-to-many relationship Claim_Parts

Form (Claim) - select Vehicle - select VehicleType
Subform (Claim_Parts) - select Parts from VehicleType_Parts

 

[Steve]

Set the rowsource of the listbox to the query you created.

Steve

 

[Pamela]

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

 

[Steve]

You record which parts go with which VehType in TblVehTypePart. For data
entry you need a form/subform. Base the main form on TblVehType. You need a
visible textbox on this form to record VehType and a not visible textbox to
hold VehTypeID. Base the subform on TblVehTypePart. Make the subform a
continuous form. You need a visible combobox and two not visible textboxes
on the subform. One textbox is for VehTypePartID and the other is for
VehTypeID. The combobox is for PartID. You need a query for the rowsource of
the combobox. Create a query based on TblPart and include PartID and Part.
Set sort for Part to ascending. Open the subform and set the rowsource of
the combobox to the query. Select the combobox and open Properties. On the
Data tab set Bound Column to 1. On the Format tab, set Column Count to 2 and
Column Width to 0;2. Now open the main form, select the subform control and
open Properties. On the Data tab, set the source object as the query and set
the LinkMaster and LinkChild properties to VehTypeID. You will now be able
to select a VehType on the main form and create a list of parts that go with
the VehType. Once you have done this for all the VehTypes, you will be able
to open the main form and automatically display all the parts that go any
VehType you select.

Steve
(e-mail address removed)

 

[BruceM via AccessMonster.com]

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.

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]

.

 

[Pamela]

Thanks so much, Steve, for that suggestion and I followed your instructions.
I don't see, however, where/how the VehType & the Parts get matched. I
believe the 3rd table you had me create (I already had the first 2) is a
junction table for a many-to-many relationship (each veh has many parts, each
part has many vehs). Maybe I'm going down the wrong path here in my thinking
but what I've learned is that queries with more than 3 tables such as the one
you described cannot be updated so, again, how would I match VehType w/
Parts???
Thanks so much for helping me through this!!
Pamela

 

[Pamela]

Thanks for that suggestion, Steve. I followed your instructions and created
the 3rd table, which as I understand is a junction table between VehType &
Parts. I don't see, however, how/where the VehTypes get matched with their
respective parts. As I understand, the query you had me build is not
updateable so how do I match them?? Thanks so much for your help with this!
Pamela

 

[Pamela]

Thanks for that suggestion, Steve. I followed your instructions and created
the 3rd table, which as I understand is a junction table between VehType &
Parts. I don't see, however, how/where the VehTypes get matched with their
respective parts. As I understand, the query you had me build is not
updateable so how do I match them?? Thanks so much for your help with this!
Pamela

 

[Steve]

Hi Pamela,

Forget the query in my first reply, forget my second post and forget your
form and listbox. Follow my suggestion in my third post.

Steve

 

[BruceM via AccessMonster.com]

You are correct about the junction table. Note that what Steve suggested (I
described something similar) is only a way of building a list of parts that
are associated with vehicle types (although I don't see that you need hidden
text boxes). My previous posting described how to use the resulting data to
limit the listing of available parts on the Claims form.

To sum up, you need a list of parts associated with a Vehicle Type

THEN

you need to use that list to provide the Row Source for a combo box on the
Claim Details subform on the Claims form.

It should be possible to add an extra field to tblPart to indicate whether
the part is on all vehicles. Your combo box list could incorporate the
custom list for the vehicle type, plus the default parts that are associated
with all vehicles.

Thanks for that suggestion, Steve. I followed your instructions and created
the 3rd table, which as I understand is a junction table between VehType &
Parts. I don't see, however, how/where the VehTypes get matched with their
respective parts. As I understand, the query you had me build is not
updateable so how do I match them?? Thanks so much for your help with this!
Pamela

First you need tables like the following:
TblVehType

[quoted text clipped - 59 lines]

.

 

[Pamela]

Thanks so much, Bruce. I'm sorry that this appears to be a very late reply,
but for whatever reason, my system (or perhaps it was the website) wasn't
correctly displaying the responses here for a few days.

This VehType/Part situation is really just what someone else has called
ancillary data - going to be used only one time per file which is why the
idea of the Parts being displayed in a Multi-Select list box which doesn't
save the users choices after leaving that record really isn't a problem.

I want to get this portion up and running but then I also want to grow this
db to emcompass much more of the work we have to do so I'm always wanting to
make sure I'm not hindering myself for that future growth. With that in
mind, you suggested to make a tblClaim (which I have) but in it, you listed
the vehicle Make & Model. All of my designs have had a separate tblVehicle
because it seemed that having the Vehicle info in w/ the Claim info broke
normalization rules. Now if that was just a fast example to try to
illustrate to me how to connect all of this VehType/Parts to the actual
claim, I can understand that but if there is another reason you would put
those together, I'd be very interested in the thought behind it.

Thanks so much!

Pamela

You are correct about the junction table. Note that what Steve suggested (I
described something similar) is only a way of building a list of parts that
are associated with vehicle types (although I don't see that you need hidden
text boxes). My previous posting described how to use the resulting data to
limit the listing of available parts on the Claims form.

To sum up, you need a list of parts associated with a Vehicle Type

THEN

you need to use that list to provide the Row Source for a combo box on the
Claim Details subform on the Claims form.

It should be possible to add an extra field to tblPart to indicate whether
the part is on all vehicles. Your combo box list could incorporate the
custom list for the vehicle type, plus the default parts that are associated
with all vehicles.

Thanks for that suggestion, Steve. I followed your instructions and created
the 3rd table, which as I understand is a junction table between VehType &
Parts. I don't see, however, how/where the VehTypes get matched with their
respective parts. As I understand, the query you had me build is not
updateable so how do I match them?? Thanks so much for your help with this!
Pamela

First you need tables like the following:
TblVehType

[quoted text clipped - 59 lines]

.