If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Best way to design tables for cascading on my form
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 |
#3
|
|||
|
|||
Best way to design tables for cascading on my form
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 |
#4
|
|||
|
|||
Best way to design tables for cascading on my form
Set the rowsource of the listbox to the query you created.
Steve "Steve" wrote in message ... 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 "Pamela" wrote in message ... 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 |
#5
|
|||
|
|||
Best way to design tables for cascading on my form
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 "Pamela" wrote in message ... 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 . |
#6
|
|||
|
|||
Best way to design tables for cascading on my form
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 "Pamela" wrote in message ... 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 "Pamela" wrote in message ... 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 . |
#7
|
|||
|
|||
Best way to design tables for cascading on my form
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 |
#8
|
|||
|
|||
Best way to design tables for cascading on my form
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 "Pamela" wrote in message ... 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 . |
#9
|
|||
|
|||
Best way to design tables for cascading on my form
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 "Pamela" wrote in message ... 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 . |
#10
|
|||
|
|||
Best way to design tables for cascading on my form
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 "Pamela" wrote in message ... 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 . |
|
Thread Tools | |
Display Modes | |
|
|