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

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 Removed)

"Pamela" <(E-Mail Removed)> wrote in message
news:88E9C415-EA53-4895-AD71-(E-Mail Removed)...
> 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

>>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

--
Build a little, test a little.


"Pamela" wrote:

> 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

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

Steve


"Steve" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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 Removed)
>
> "Pamela" <(E-Mail Removed)> wrote in message
> news:88E9C415-EA53-4895-AD71-(E-Mail Removed)...
>> 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
>
>

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" wrote:

> 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 Removed)
>
> "Pamela" <(E-Mail Removed)> wrote in message
> news:88E9C415-EA53-4895-AD71-(E-Mail Removed)...
> > 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
>
>
> .
>

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 Removed)


"Pamela" <(E-Mail Removed)> wrote in message
news:C8E35D38-A18A-4328-BBDC-(E-Mail Removed)...
> 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" wrote:
>
>> 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 Removed)
>>
>> "Pamela" <(E-Mail Removed)> wrote in message
>> news:88E9C415-EA53-4895-AD71-(E-Mail Removed)...
>> > 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
>>
>>
>> .
>>

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

"Steve" wrote:

> 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 Removed)
>
> "Pamela" <(E-Mail Removed)> wrote in message
> news:88E9C415-EA53-4895-AD71-(E-Mail Removed)...
> > 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
>
>
> .
>

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" wrote:

> 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 Removed)
>
> "Pamela" <(E-Mail Removed)> wrote in message
> news:88E9C415-EA53-4895-AD71-(E-Mail Removed)...
> > 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
>
>
> .
>

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" wrote:

> 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 Removed)
>
> "Pamela" <(E-Mail Removed)> wrote in message
> news:88E9C415-EA53-4895-AD71-(E-Mail Removed)...
> > 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
>
>
> .
>

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


"Pamela" <(E-Mail Removed)> wrote in message
news:C21F2706-90C0-405A-883A-(E-Mail Removed)...
> 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" wrote:
>
>> 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 Removed)
>>
>> "Pamela" <(E-Mail Removed)> wrote in message
>> news:88E9C415-EA53-4895-AD71-(E-Mail Removed)...
>> > 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
>>
>>
>> .
>>