About Geoff Expertise I specialise in database analysis and design, SQL and database queries using QBE and VBA. In my work, I use MS Access together with MS SQL Server as ETL (Extraction - Transformation - Loading) tools for migrating data between business ERP systems and data stores. My forte is building bespoke functions and applications.
See my
website for example apps and downloads
Experience I am a chartered engineer with 30 years of engineering and business experience, member of the BCS and have been working specifically in database applications, including SQL Server (v7/8/2000) for the last 9 or so years. I previously taught a course in Database Analysis and Design, but am now a freelance consultant and systems analyst.
Commercial database design and development work undertaken.
Using MS Access - Why error? "Cannot find a record...with key matching fields..."
Expert: Geoff - 2/11/2005
Question "The Microsoft Jet database engine cannot find a record in the table
'tblOrgs' with key matching fields(s) 'tblDocs_fldOrgId.'
This is the error that pops up when I try to create a record... I've tried
everything countless times, but I can't fix it! Please help! The tiny
database is zipped up and ready to send.
I've got a main form with two combo boxes, some text boxes, and a datasheet
view of a subform on it. If I enter stuff into the combo boxes and text
boxes on the main form first and then go to the datasheet, I can almost
create a new record, but the error message pops up.
If I populate the datasheet first, the error pops up as soon as I click one
of the combo boxes.
Help!
Answer Hi Etalent
It would appear that your have two tables:
tblDocs is your main table and tblOrgs a look-up table or other parent table to tblDocs. This being the case, it would appear that there is a relationship between the tables and that prior to moving off the new record created in tblDocs, a value needs to be entered in the Foreign Key (FK) field for tblOrgs (OrgID ?)
The easiest way to resolve this is to set a default value for the FK value in tblDocs
So if tblOrgs looks like
OrgID Organisation
1 Aaaaa
2 Bbbbb
Then set the default value for OrgID in tblDocs = 1
