Form using a many to many relationship

[jalbright1957]

Little stymied by behavior for a form I have. To begin, the tables being used are tblCompany, tblJobs, tblCompanyJobs, tblCity & tblState. tblCompany & tblJobs are many to many with junction table tblCompanyJobs. tblCity & tblState are master tables for tblCompany & tblJobs.

The main form is used to create Job records which includes the city & state location. City & state are recorded elsewhere and are chosen via the use of combo boxes.

Subform is a datsheet used to select the name of a company that is bidding the job and who we will submit our bid to. The company information is recorded elsewhere and is chosen via the use of a combo box. The city and state is also entered when the company information is entered in another part of the application. The company name is all that needs to be shown in the subform.

The form works up to the point where I select the company in the subform combo box and then try to save the record or move to add another. Access states that it cannot save the record as it is missing the City field. Have tried entering the fields directly onto the subform and modifying the query but then I cannot select a company name from the combo box as the error that flashes across in the bottom status bar states the record is not bound to the dataset.

[PhilOfWalton]

I presume that city table has a StateID Foreign Key so that once you select your city, the state is automatically defined.

Have you got a company form with a combo box to select the city already setup. You need this before you can select the company in your subform.

I appreciate it's difficult to be sure, but is the error about the missing city referring t the Job or the Company?

Phil

[jalbright1957]

I did not setup the state table that way. I never thought about that but may be the better way to go as far as the city & state work.

In the subform, I have a combo box to select the company but do not have a combo box to select the city & state. I was trying to populate the city and state records automatically based upon the company name. I can appreciate that in order to use the child record, the parent has to be present. Users I'm afraid will find it a bit odd to have to select the city & state of a company they have already entered into the company table in order to assign them as someone that is bidding a job. I set the city & state to be master records of the company & jobs tables to keep with database normalization along with the most likely occurance of mis-spelling a city name or state abbreviation.

I'm quite certain the city error is for the subform. I'm also quite certain that it will do the same for the state, but haven't gotten that far. Since the job has to have a city & state associated with it, those fields are combo boxes on the main form when recording job information.

[PhilOfWalton]

The normal way to set this up is that the Company record has a foreign key CityID (which points to the city) and on the company form, you have a dropdown to select the city.

Similarly in the City record you have a field called StateID which points to the State, and in the City form, you have a dropdown to select the appropriate state. So in your relationships you see a chain
Company --> City --> State

Phil

[jalbright1957]

Company table has a FK pointing at the city & state tables, I added them into the subform as a combo box. Selectin the city and state from the combo box removes the error dealing with the city & state records but now am getting a duplicate warning when saving. I believe my logic in all of this is wrong in how I have the forms designed.

[PhilOfWalton]

You're not that far off, but the state combo must be part of the city table NOT the Company table.

I am unfamiliar with the US, if that is where you are from, but you could pick say Las Vegas as the city and Oklahoma as the state - Nonsense.

That is why I said that in your City form you have a drop down to pick State Nevada and thereafter, every time you chose a company in Vegas it "knows" the State is Nevada.

I stress again, there should be NO FK in the company table pointing to State.

Phil

[jalbright1957]

Hi Phil,
Been putzing with this for the past several days when I am able to afford the time. I broke off the master table "State" and made it a child table of the City table. That does populate the State field when the City is selected but since the Company table is a child table of the City table also, I can't seem to find a way to pre-populate the City field when the end user selects an existing Company as a bidder for a job record instead of forcing the end user to hit the dropdown and select it. I guess my use of forms with many to many relationsship skills needs a whole lot of polishing....

[PhilOfWalton]

No No No!

The City is a child of the Company, not the other way round.

Company--> City--> State.

So in practice, first you build up states your table of states. Then you build up your table of Cities and select the appropriate state using a Combo Box.
Then you build your table of companies and select the City (and by implication the State) using a Combo Box

So your company table contains CityID as a foreign key.

When you select a Bidder, you simply select a pre-existing company from a dropdown combo box.

So the outline of the relationships now becomes:-
Bidder--> Company--> City--> State.

If you are still stuck, can you send an image of your relationships with all the tables at full size so that all the fields are visible

Phil

[jalbright1957]

Hi Phil,
So I understand you to say add a FK to the company table which points to the PK of the city table. The city table would have a FK pointing to the PK of the state table. Never mind the bidder that is listed in your diagram, the company is the bidder.

I made an attempt and got it working or at least at this point. I manually entered records into the city & state tables. I just haven't tested out my routine for the not in list event that I have been using elsewhere. Thanks for your help.