Microsoft Access Forms and Subforms

[piovesan]

I am new at creating forms, and especially subforms in Access. I don't know if my problems are my relationships among my tables, or if it's really about the forms but here goes.

I have 4 main tables. Two of the tables have lookup tables. Of the four tables I have a COMPANY table that has a company name and date and a CompanyAutoID which links to an AREA table which consists of a Street name, Community name and its own AreaAuto ID. These are the two tables that have look ups for Company and Street Names respectively. Then, my AREA table is linked to a CIVIC Table that consists of civic numbers, and a CivicAutoID and links to AREA by the AREA Auto ID. The reason I have the AREA seperated from CIVIC is because I want to only have to input the Street Name once for several civic numbers. Then my CIVIC table is linked through it's CivicAuto ID to a CONTAMINANTS table which consists of various types of contaminants found in the recyclables of the homes visited. In the meantime, my COMPANY table is also related to the CONTAMINANTS table through the CompanyAuto ID.

I have created a form where the Company information and Area (Street) information will be input and then a subform with the Civic and Contaminants information together in datasheet format so that you can input the company and Street information once and then in the datasheet subform add all the civic numbers and types of contaminants found along that street. Seems like it should work but my AreaAutoID won't automatically go into the CIVIC table even though I have a relationship between the two. As I put information into my Company table and Area table in the form everything appears fine until I try to input info into the Civic table in the subform and the AreaID is not getting updated and I get a message saying I need a related field in the AREA table.

Can anyone fiqure this out???? Help please!!!!

 

[RickSpr]

I reproduced your tables, as described, in order to study the relationships. It appears to me that you have one-to-many relationships from AREA to COMPANY, from AREA to CIVIC, from COMPANY to CONTAMINANTS, and from CIVIC to CONTAMINANTS. Is that right?

If so, there's a subtle little problem here. There are two ways to find the AREA related to a particular CONTAMINANT: one through the COMPANY, and one through the CIVIC. I suspect that your business rules--that is, the real-world logic of your business--require that these would always be the same area, but you don't have a relationship that enforces that. This might be causing your problem. When you join all four tables together, which you are indirectly doing with your form and subform, your relationships require that Access be able to use two rows from AREA for each set of related rows from the other tables. But the form only has a place for one set of columns from AREA, so Access has a problem. I think it is, in effect, ignoring one of the paths to AREA from CONTAMINANTS. (This is not a bug. It's not really ignoring anything, it's just using the single relationship that you implicitly chose when you specified the master/child link fields.)

I think you may be able to fix this by selecting the right fields in the record sources for your form and subform, but I need some more information. Specifically, I need:
1. The Record Source for the mainform and subform.
2. If either Record Source is a query name, the SQL statement of the query (from SQL View).
3. The master/child link fields between the main form and subform.
4. Verify my understanding of the relationships you have among these tables, and tell me, if I missed any, what they are, including which table is the "one" side.
5. Verify that for a given CONTAMINANTS, the related COMPANY and CIVIC should have the same AreaAutoID, or tell me if I'm wrong about that.
6. Explain how your form/subform works--what sequence do you want the user go through to add records, and which tables are they adding to?
7. If your form or subform has code behind it, what does the code do? (Use your best judgment about what's relevant.)
8. The solution may require adding code to the form. How familiar are you with writing VBA event procedures?

That's a lot of questions, I know, but your tables have an unusual relationship structure, and trying to update them all from one form may be tricky. By asking for everything up front, I'm trying to avoid having to come back again and again with more questions. (I think I understand your table design, and it looks good to me. So does the concept of updating everything in one form/subform.) Rick Sprague

 

[piovesan]

Hi Rick, you don't know how happy I am to have gotten a reply! You seem to have it pretty much correct too. However, the relationships aren't exactly as you described in your first paragraph. I do have a one to many relationship from AREA to CIVIC (one area to many civics) and a one to many relationship from CIVIC to CONTAMINANTS (one civic to many contaminants) and a one to many relationship from COMPANY to CONTAMINTANTS (one company to many contaminants).

I actually have a subform within my subform too which I think I failed to mention. The Record Source for the Main form is COMPANY. The Subform within that is basically just the AREA info. The record source for that subform is an SQL statement that links AREA, CIVIC and CONTAMINANTS so that I can link AREA and COMPANY through the COMPANY ID which only exists in COMPANY and CONTAMINANTS. (therefore, the master/child link is Company ID) I have a subform within the AREA subform which has a record source created from a query (SQL statement) that joins CIVIC and CONTAMINANTS and the subform shows them in datasheet view. The master/child link there is Area ID and Company ID.

I have never done any VBA programming or Event procedures or anything like that. I am so new to this! It may help if I explain to you why I have set up my tables this way and maybe you will even tell me this is not the best procedure. On a particular day, our monitor goes to a particular area and monitors the types of recyclables put to curbside (or whether or not the neighborhood is even participating in recycling.) What she does is records the day, the company responsible for the area, then the streets and each civic number she checks and whether or not they have anything to curbside and if so, whether or not there are any "unusual" items in the bags. (If they are unwanted items we want to send out information packets to educate these people.) Anyway, she may need to go back to these same homes to revisit and we keep statisics on whether neighborhoods have changed their practices and are benefiting from our educational info.

So, I want her to be able to enter the information in the following manner:
1. Enter the information on the Company involved in the area, as well as the date of the monitoring visit.
2. Enter the Street visited.
3. Enter the civic number information and whether there were recyclables at the curb and if so, were there any contaminants.
4. There may be other streets to enter for this same COMPANY and AREA info and therefore 2. and 3. will be repeated.

I really hope you can help further! Thanks!!

 

[RickSpr]

This is an interesting problem; don't worry, I'll stick it out with you--but I hope you aren't in a big hurry, 'cause I can't usually spend as much time here as I have today.

In your first post you said COMPANY was linked to AREA. In the second you didn't mention it. Is there a relationship from AREA to COMPANY, and if so, which is the "one" side? You used the phrase "the company responsible for the area". But you also said "Company ID...exists only in COMPANY and CONTAMINANTS". If a company is responsible for an area, shouldn't the responsible Company ID be in the AREA table? (BTW, if it were, your problem would go away, I think.)

Actually, it would help a lot if you'd list the names of the primary key fields for each table, and of the foreign key fields (that is, the "linking" fields) in each table. Also, the names of the main, sub-, and sub-subforms. You've been changing names on me--CompanyAutoID in the first post, Company ID in the second--and that leads to a lot of confusion. You may know it's the same thing, but I don't, and I can't make that assumption. If we use the real names, it'll speed things up.
Rick Sprague

 

[piovesan]

Yes, I'm sorry, i made a mistake in the first message. COMPANY is not directly linked to AREA. COMPANY is directly linked to CONTAMINANTS only. Here is a list of the tables, and the fields I believe you need to know about:

COMPANY - Company ID (which is the primary key and an Auto Number)

AREA - Area ID (Which is the primary key and an Auto Number)

CIVIC - Civic ID (which is the primary key and an Auto Number) Area ID (Which is an integer linked to the AREA table)

CONTAMINANTS - Contam ID (which is the primary key and an Auto number) Civic ID (which links to the CIVIC table) Company ID (which links to the COMPANY table).


You asked why the Comany ID is not in the AREA table. I did not do this because some streets have two or more companys responsible for them. As an example, a major street like Yonge Street in Toronto (not where I am from BTW), may have many many companys responsible for garbage pick up. One company may be responsible for all civics in the 1-2000 range, while another is responsible for 2001-6000 range.........Therefore, the AREA table would contain two rows of Yonge Street both with different Company Ids. This may not be a problem at all and I may have to do this but I was just trying to clean up my AREA table as much as possible. Probably dosn't make much sense to have it the way that I do currently but this is why speaking with someone like yourself is quite helpful.

The main form is called Company Form, the subform is Area subform and the sub-subform is called Civic subform.

Thanks again and I hope I didn't leave anything out.