Hi,
I have had no training in Access and have picked up all of my knowledge as I have gone along so please excuse the probably glaring error.
I am building a database to replace a rubbish one made by my predecessor, I have loads of tables, probably more than I need, but my philosophy was get lots of tables and link them together - I think this may be wrong though.
I have the "main" table = tblSites and lots of others that I have just about kludged together to work. I am now trying to add on one final massive feature to essentially automate report writing. I have another table "tblAnalyses" that has fields related to the analysis of each site.
This is how they are related:
tblSites to tblAnalyses (one to many) linked by "ID" of the site in tblSites to "SiteID" in tblAnalyses (with referential integrity)
When I open up the subform, having used the wizard to link the subform to the main form, I enter text and try to close the form and it says "The Microsoft Jet database engine cannot find a record in the table tblSites with key matching field(s) SiteID".
Also, the recordsource for the subform is based on a query of tblSites and tblAnalyses which includes the field SiteName and SiteNumber from tblSites, but the fields I have on the subform for SiteName and SiteNumber are blank.
This leads me to believe there is something fundamentally wrong with the setup. I have attached a screenshot of the relationship diagram, showing all the fields on each table.
So I have two main questions:
1) Can someone tell me how to get my subform working properly.
2) Can someone appraise my design of the database and tell why it is good or bad and what is better practice.
Thanks,
Tayo
I have had no training in Access and have picked up all of my knowledge as I have gone along so please excuse the probably glaring error.
I am building a database to replace a rubbish one made by my predecessor, I have loads of tables, probably more than I need, but my philosophy was get lots of tables and link them together - I think this may be wrong though.
I have the "main" table = tblSites and lots of others that I have just about kludged together to work. I am now trying to add on one final massive feature to essentially automate report writing. I have another table "tblAnalyses" that has fields related to the analysis of each site.
This is how they are related:
tblSites to tblAnalyses (one to many) linked by "ID" of the site in tblSites to "SiteID" in tblAnalyses (with referential integrity)
When I open up the subform, having used the wizard to link the subform to the main form, I enter text and try to close the form and it says "The Microsoft Jet database engine cannot find a record in the table tblSites with key matching field(s) SiteID".
Also, the recordsource for the subform is based on a query of tblSites and tblAnalyses which includes the field SiteName and SiteNumber from tblSites, but the fields I have on the subform for SiteName and SiteNumber are blank.
This leads me to believe there is something fundamentally wrong with the setup. I have attached a screenshot of the relationship diagram, showing all the fields on each table.
So I have two main questions:
1) Can someone tell me how to get my subform working properly.
2) Can someone appraise my design of the database and tell why it is good or bad and what is better practice.
Thanks,
Tayo
