Multiple Combo Boxes in SubForm with Many to Many Relationship

[LBinGA]

Hi all:

I have a subform with a many to many relationship to the main form and I've successfully done this a few times with just one combo box field. My trouble today is that I need the subform to contain 5 combo boxes and I'm stumped. I've tried various solutions with limited success so I've reverted the db back to the one combo box and am reaching out for help.

The set up is as follows:




I want to see more than just the State in the subform, which currently looks like this:


The record source for the form is TblFleet Data.
FleetID Rowsource is set as follows: SELECT FleetID, St FROM tblFleet ORDER BY FleetID;
The Property Sheet for the Subform Master & Child Links are both Set to ID.

I need to show the St, Distribution, Type, and Radius as additional comboboxes, and number as a enterable field. I've tried setting the Row Source as a Query and pulling in the data that way but it didn't work the way I did it, and numerous other solutions since this morning but no avail.

What am I doing wrong? Anyone?

Thanks in advance,
LBinGA

[aytee111]

What is this subform doing? Your record source is one table - if this is an update form then it needs to stay one table. It looks like you are trying to update multiple tables - what is the purpose of those other comboboxes?

[John_G]

Create a query with tblFleetData and tblFleet joined on FleetID.

Then tblData as the recordsource in the main form, and use the query as the recordsource for the subform. Link the Main form and the subform using ID.

You can then use any of the fields in tblFleet in the subform.

If you wanted to do it the other way around, with tblFleet in the main form, you would need to have another form.

[LBinGA]

Create a query with tblFleetData and tblFleet joined on FleetID.
Then tblData as the recordsource in the main form, and use the query as the recordsource for the subform. Link the Main form and the subform using ID.
You can then use any of the fields in tblFleet in the subform.
If you wanted to do it the other way around, with tblFleet in the main form, you would need to have another form.

Thank you, John. I did just what you said and it worked mostly. And, now the same subform data appears on every record though. Here's a picture of my form with the subform below, now showing all the fields from tblFleet, if I change it in record 1, it changes it on all records. Is there something more I should do? My Master/Child links are ID-ID.



I changed the properties in each field to look at the proper column and updated the columns widths with 0;.75, for instance for State, so that it would look just at state. It works except for Radius, which is in the 5th column. There, it's pulling the number 6...not sure why.

[John_G]

It works except for Radius, which is in the 5th column. There, it's pulling the number 6...not sure why.

Radius is in the 5th column of what? You said in your initial post that all the controls in the subform are combo boxes. So, some questions:

What is the record source for the subform?
What is the row source for the radius combo box?
What field is the radius control bound to?
"Radius" in your diagram is showing "6" - why is that wrong - what should it be?

Are any of your fields multivalue or lookup fields? I can't help much if they are, because I know virtually nothing about them - I avoid them like the plague.

I changed the properties in each field to look at the proper column and updated the columns widths with 0;.75, for instance for State, so that it would look just at state.

I have the same concern here as before - the proper column of what? Your settings suggest that what the combo box displays and what is being stored in the underlying table are not the same thing. Is that true for State?

[LBinGA]

Radius is in the 5th column of what? You said in your initial post that all the controls in the subform are combo boxes. So, some questions:

What is the record source for the subform?
What is the row source for the radius combo box?
What field is the radius control bound to?
"Radius" in your diagram is showing "6" - why is that wrong - what should it be?

Are any of your fields multivalue or lookup fields? I can't help much if they are, because I know virtually nothing about them - I avoid them like the plague.

I have the same concern here as before - the proper column of what? Your settings suggest that what the combo box displays and what is being stored in the underlying table are not the same thing. Is that true for State?

What is the record source for the subform? QryFleet
What is the row source for the radius combo box? FrmFleet
What field is the radius control bound to? FrmFleet
"Radius" in your diagram is showing "6" - why is that wrong - what should it be? LOC;INTER;ZONE

I see now that I cannot have all the choices in FrmFleet, like I do, and that it will never work that way. I did try making the State the only choice field on that sheet and I changed the combo boxes to pick from a Value. Didn't work either. I'm afraid I'm confused myself into oblivion.

I designed an Excel spreadsheet that does this perfect, trouble is, I need for Access to do it so that we can run reports off the records. So, bottom line: Each Insured may have different Numbers & Types of Vehicles that run in a different Distribution Area and Radius, like so:

Named Insured: XYZ Corp.
FLEET
St Distr Type Radius No of Vehs
GA METRO LT LOCAL 3
GA CITY HVY INTER 1
GA CITY XHVY LOCAL 2


So XYZ Corp has 3 LIGHT Vehs in GA that Distribute in a METRO zone, Locally AND
They have 1 Heavy Veh in GA that Distributes in the City, Intermediately AND
they have 2 Extra Heavy Vehs in GA that Distribute in the City, Locally.

Any given Named Insured could have any combination, and any number of vehicles, though typically it is a small fleet in the same city.

Therefore, I put it in a Many-to-Many Relationship with a subform but I can't get it to work. I'll be happy to send it somewhere with the accompanying spreadsheet so show what I'm trying to do. I've tried to attach photos and this morning it's not working for me.

I'm stuck but good.

Ok, on Edit, it let me upload the spreadsheet portion I'm trying to mimic. There are 53 state choices, 4 Dist choices, 7 Type Choices, 3 Radius Choices:

Thanks for any help.....

LBinGA

[LBinGA]

Can anyone assist?

What is the record source for the subform? QryFleet
What is the row source for the radius combo box? FrmFleet
What field is the radius control bound to? FrmFleet
"Radius" in your diagram is showing "6" - why is that wrong - what should it be? LOC;INTER;ZONE

I see now that I cannot have all the choices in FrmFleet, like I do, and that it will never work that way. I did try making the State the only choice field on that sheet and I changed the combo boxes to pick from a Value. Didn't work either. I'm afraid I'm confused myself into oblivion.

I designed an Excel spreadsheet that does this perfect, trouble is, I need for Access to do it so that we can run reports off the records. So, bottom line: Each Insured may have different Numbers & Types of Vehicles that run in a different Distribution Area and Radius, like so:

Named Insured: XYZ Corp.
FLEET
St Distr Type Radius No of Vehs
GA METRO LT LOCAL 3
GA CITY HVY INTER 1
GA CITY XHVY LOCAL 2


So XYZ Corp has 3 LIGHT Vehs in GA that Distribute in a METRO zone, Locally AND
They have 1 Heavy Veh in GA that Distributes in the City, Intermediately AND
they have 2 Extra Heavy Vehs in GA that Distribute in the City, Locally.

Any given Named Insured could have any combination, and any number of vehicles, though typically it is a small fleet in the same city.

Therefore, I put it in a Many-to-Many Relationship with a subform but I can't get it to work. I'll be happy to send it somewhere with the accompanying spreadsheet so show what I'm trying to do. I've tried to attach photos and this morning it's not working for me.

I'm stuck but good.

Ok, on Edit, it let me upload the spreadsheet portion I'm trying to mimic. There are 53 state choices, 4 Dist choices, 7 Type Choices, 3 Radius Choices:

Thanks for any help.....

LBinGA

[LBinGA]

Can anyone assist?

I figured it out. I had both tables with a Primary Key. I removed the primary key from the 2nd table and it works fine.

Thanks,
LBinGA