Hi! I really need help with a form and subforms. This is a long difficult question so I will try to give all the information needed for someone to help me with this.
I have 4 main tables. 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) (This is a recycling company information table)
AREA - Area ID (Which is the primary key and an Auto Number)
(this is a street and community table)
CIVIC - Civic ID (which is the primary key and an Auto Number) Area ID (Which is an integer linked to the AREA table) (this is a civic number 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). (this is information on types of contaminants found)
I 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).
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.
The Comany ID is not in the AREA table 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.
I have created a main form which contains a subform and that subform also contains a subform. The main form is called Company Form, the subform is Area subform and the sub-subform is called Civic subform.
The main form is where the Company information is input and then a subform with the Area information. The sub-subform contains information for both Civic and Contaminants in datasheet format.
The problem is, the 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 form and Area subform everything appears fine until I try to input info into the Civic table in the sub-subform and the AreaID is not getting updated. I get a message saying I need a related field in the AREA table.
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) The sub-subform has a record source created from a query (SQL statement) that joins CIVIC and CONTAMINANTS and is in datasheet view. The master/child link there is Area ID and Company ID.
It may help if I explain to why I have set up my tables this way. Any advice on how they might need to be changed is also welcomed.
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.
Please help!!!! Thanks!
I have 4 main tables. 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) (This is a recycling company information table)
AREA - Area ID (Which is the primary key and an Auto Number)
(this is a street and community table)
CIVIC - Civic ID (which is the primary key and an Auto Number) Area ID (Which is an integer linked to the AREA table) (this is a civic number 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). (this is information on types of contaminants found)
I 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).
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.
The Comany ID is not in the AREA table 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.
I have created a main form which contains a subform and that subform also contains a subform. The main form is called Company Form, the subform is Area subform and the sub-subform is called Civic subform.
The main form is where the Company information is input and then a subform with the Area information. The sub-subform contains information for both Civic and Contaminants in datasheet format.
The problem is, the 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 form and Area subform everything appears fine until I try to input info into the Civic table in the sub-subform and the AreaID is not getting updated. I get a message saying I need a related field in the AREA table.
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) The sub-subform has a record source created from a query (SQL statement) that joins CIVIC and CONTAMINANTS and is in datasheet view. The master/child link there is Area ID and Company ID.
It may help if I explain to why I have set up my tables this way. Any advice on how they might need to be changed is also welcomed.
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.
Please help!!!! Thanks!
