Novice Error: The Microsoft Jet database engine cannot find a related record

[Lance]

Hello,

as the title says, I shouldn't be having this problem, so I am probably
missing something obvious.

I am getting the error

"The Microsoft jet datbase engine cannot find a related record in the
table 'tblOrg' with key matching fields 'CoID'"

I poked around for a while in the groups, and found a few things, of
which I tried, and of which all failed.

This error is occuring when I try to add a new record using a form. The
new record is created, I can type information, then when I try to leave
the form, it tells me this error is happening.

CoID is an autonumber relation to a company table, which can be
selected from a dropdown menu.

I have deleted the "default value" for the CoID in the form, as well as
in the tbl design, just to make sure I wasn't inserting the infamous
"0" into my combo box, thus entering a value I didn't intend to.

If the default value box is empty, then what other things could be
causing this error to occur? Is it a problem in my relationships set up
between the two tables, or something else?

Thanks,

Lance

 

[Steve Schapel]

Lance,

I assume the form you are using at the time you get this error, is based
on a table that is on the "many" side of a one-to-many relationship with
another table, tblOrg. Is that right? I think the meaning of the error
message is that you are trying to insert a record into the table where
there is no existing matching record in tblOrg. Can you say:
- is the form you are using a subform?
- what fields in the two tables are the basis of the relationship
between them?
- what are the data types of these fields in each table?
- by what means is the CoID field being entered on the form?

 

[Lance]

Steve,

Yes, the form I am using is on the "many" side of the relationship. I
am using tblClient, and referencing tblOrg.

I am not using any sort of subform, it is just a forum.

On the form (frmClient refering to tblClient), I have a combo box which
is bound to a field CoID (long integer)in tblClient (This might be an
issue?). This field is meant to store the OrgID (autonumber on "one"
side)) from tblOrg, where the combo box consists of 2 columns, OrgID,
and OrgName (OrgName displayed, OrgID bound).

Once the user selects an OrgName from the combobox, the form then
updates more fields, which are pulled from tblOrg based on the OrgID.
(These fields are not stored in anyway in the Client table, but are
merely displayed for the convenience of the user). Each of these fields
on the form has a default value of null, so that it isn't trying to
"create" a new OrgID, should none be selected.

 

[Steve Schapel]

Lance,

Thanks for the further explanation. Hmmm, I'm sorry, on the face of
what you have siad so far, I'm afraid I can't see the reason for this error.

It sounds like the Record Source of the form must be a query. Could you
please copy/paste the SQL view of this query into your reply? (If it is
a saved query, you can see it from the View menu in design view of the
query.)

 

[Lance]

Steve,

The recordsource for the form is a query, composed of the tblClient
information, then bringing in the related tblOrg information from CoID

The SQL is as follows

SELECT tblClient.*, tblOrg.OrgName, tblOrg.Description,
tblOrg.StreetLn1, tblOrg.StreetLn2, tblOrg.City, tblOrg.State,
tblOrg.Zip, tblOrg.Country, tblOrg.Website
FROM tblClient INNER JOIN tblOrg ON tblClient.CoID = tblOrg.OrgID

I thought perhaps the INNER JOIN was the problem, and did a LEFT JOIN
(and a RIGHT JOIN) to make sure that it wasn't an issue with how the
query was bringing in the records.

Another symptom I noticed, is when I try to delete the company
association from a pre-existing Client, I get the message
"You tried to assign a Null value to a variable that is not a Variant
type data".

I am not sure if this is because of the fact that I tried to just clear
teh combo box, without the other fields being cleared as well, or what,
but perhaps that will help in solving this problem.

Thanks again

 

[Steve Schapel]

Lance,

Sorry to be peppering you with more questions than answers at this stage

It seems like there might be some code running on the After Update
event, or perhaps similar such as Exit event, of the CoID combobox. Can
you please check this, and if so post the code here?

 

[Lance]

Steve,

On the CoID combo box (cmbEmp1), I have the following code:

Private Sub cmbEmp1_GotFocus()
Me!cmbEmp1.Requery
End Sub

The reason for this was that it is possible to "add" a missing
organization, and when you return to the screen, I need the combobox to
update with the "new" organization.

Another thing, that might be an issue, is that I have code on some
other fields, which are directly linked to the OrgID present in the
CoID field.

For example,

When you select a name from the CoID combo box, the form then displays
all the contact information for that company (including address1, and
state)

However, when I had this initially, making any changes to the
information within these fields was causing problems, it wasn't
updating properly. i'm not sure if this was a problem with my defined
relationship between the tables, or my form. So to remedy this update
problem, I added the code:

Private Sub tblOrg_StreetLn1_AfterUpdate()
Dim strClient As Integer
Refresh
[tblOrg.StreetLn2].SetFocus

This being so that once I updated the information, it would refresh the
form, and then put the cursor where it belonged next. This is not an
ideal situation, but I couldn't think of a better way to do it. Could
this be a problem as well?

Thanks again for your help

 

[Lance]

Steve,

On the CoID combo box (cmbEmp1), I have the following code:

Private Sub cmbEmp1_GotFocus()
Me!cmbEmp1.Requery
End Sub

The reason for this was that it is possible to "add" a missing
organization, and when you return to the screen, I need the combobox to
update with the "new" organization.

Another thing, that might be an issue, is that I have code on some
other fields, which are directly linked to the OrgID present in the
CoID field.

For example,

When you select a name from the CoID combo box, the form then displays
all the contact information for that company (including address1, and
state)

However, when I had this initially, making any changes to the
information within these fields was causing problems, it wasn't
updating properly. i'm not sure if this was a problem with my defined
relationship between the tables, or my form. So to remedy this update
problem, I added the code:

Private Sub tblOrg_StreetLn1_AfterUpdate()
Dim strClient As Integer
Refresh
[tblOrg.StreetLn2].SetFocus

This being so that once I updated the information, it would refresh the
form, and then put the cursor where it belonged next. This is not an
ideal situation, but I couldn't think of a better way to do it. Could
this be a problem as well?

Thanks again for your help

 

[Lance]

Well Steve,

I figured it out, and as my title implied, it was definetely a novice
error.

What I ended up doing, was just created a subform to bring up the
OrgInfo.

It turns out the problem was that since I was bringing both tables in,
building a relationship between the two within the form, it was
requiring me to have a reference to both. By building the subform, and
linking it through COID, I was able to remove all reference to the
second table in the main form, and leave that all in the subform. This
enabled me to have clientIDs without COIDs.

Thanks for all of your help, I have wasted way too much time on this
error.