Run Time Error 3101

  • Thread starter Thread starter Leonard
  • Start date Start date
L

Leonard

I keep getting this message when I try to add a record.

The Microsoft Jet database engine cannot find a record in
the table <name> with key matching field(s) <name>.

Any help appreciated.
 
Hello Vanderghast... Thanks for responding.

I have included the SQL statement you asked for, I
THINK???. My apologies if it is not what you meant.

I have been chewing on this problem (run time error 3101)
for a couple of weeks now, and it is driving me crazy.

Here is some additional info on the matter:

I can't add a table (ErrorTestTbl) to my query without
getting the 3101 error.

All I do is add the table to the query without referencing
the fields in the table on the form, and I get the error
message.

As long as I don't try to add a new table to my query
everything works fine in my form.

I have tried creating a new form and copying all items to
the new form but I still have the same problem.

I don't need to add a table to my query at the present
time but if I need to in the future, I will be F$%#@*.

I have also repaired and compacted many times.

Thanks again for responding.

Leonard

=========================================================

SELECT OrdDetailsTbl.OrdDetailsID,
OrdDetailsTbl.CustomerID, OrdDetailsTbl.ErrorTestID,
ErrorTestTbl.ErrorTestNm, OrdDetailsTbl.OrdNumComplete,
OrdDetailsTbl.OrdNumNum, OrdDetailsTbl.CurRecNum,
OrdDetailsTbl.OrdTakerNm,
OrdDetailsTbl.BillingCityComboNum,
OrdDetailsTbl.BillingHouseNum, OrdDetailsTbl.BillingStrNm,
OrdDetailsTbl.BillingState,
OrdDetailsTbl.BillingOtherStateComboNum,
OrdDetailsTbl.BillingOtherCity,
OrdDetailsTbl.BillingOtherZip,
OrdDetailsTbl.TestLocComboNum,
OrdDetailsTbl.TestMethComboNum,
OrdDetailsTbl.DeployedByComboNum,
OrdDetailsTbl.RetrievedByComboNum,
OrdDetailsTbl.AnalyzedByComboNum,
OrdDetailsTbl.InchesWaterComboNum,
OrdDetailsTbl.FanModManComboNum,
OrdDetailsTbl.MitigationSysComboNum,
OrdDetailsTbl.REAgentComboNum, OrdDetailsTbl.BuyerAgentCB,
OrdDetailsTbl.SellerAgentCB,
OrdDetailsTbl.JobCityComboNum,
OrdDetailsTbl.FieldStaffComboNum,
OrdDetailsTbl.MeansOfAccessComboNum,
OrdDetailsTbl.RadonComboNum,
OrdDetailsTbl.HouseStyleComboNum,
OrdDetailsTbl.HouseAgeComboNum,
OrdDetailsTbl.HouseBedsComboNum,
OrdDetailsTbl.HouseSizeComboNum,
OrdDetailsTbl.HouseStatusComboNum,
OrdDetailsTbl.GarageTypeComboNum,
OrdDetailsTbl.BsmntWallComboNum,
OrdDetailsTbl.BsmntAccessComboNum,
OrdDetailsTbl.BsmntFinComboNum,
OrdDetailsTbl.BsmntConfigComboNum,
OrdDetailsTbl.BsmntFlrTypComboNum,
OrdDetailsTbl.BsmntFlrCondComboNum,
OrdDetailsTbl.BsmntFlrConfigComboNum,
OrdDetailsTbl.NumOfBedRms, OrdDetailsTbl.SourceComboNum,
OrdDetailsTbl.RefByComboNum, OrdDetailsTbl.OrdByComboNum,
OrdDetailsTbl.JobPrice, OrdDetailsTbl.PayDueComboNum,
OrdDetailsTbl.CloseDate, OrdDetailsTbl.SumpPumpYCB,
OrdDetailsTbl.SumpPumpNCB, OrdDetailsTbl.AdditionYCB,
OrdDetailsTbl.AdditionNCB, OrdDetailsTbl.CrawlSpaceYCB,
OrdDetailsTbl.CrawlSpaceNCB, OrdDetailsTbl.DirtFloorYCB,
OrdDetailsTbl.DirtFloorNCB, OrdDetailsTbl.OrdHead,
OrdDetailsTbl.CustHead, OrdDetailsTbl.JobAddressHead,
OrdDetailsTbl.JobDetailsHead,
OrdDetailsTbl.CompletedJobDetailsHead,
OrdDetailsTbl.AgentHead, OrdDetailsTbl.JobState,
OrdDetailsTbl.SchedRecNumOnOrd, OrdDetailsTbl.OrdNumMo,
OrdDetailsTbl.OrdNumYr, OrdDetailsTbl.MonthlyCPxSxNm,
OrdDetailsTbl.DailyCPxSxNm, OrdDetailsTbl.OrdDate,
OrdDetailsTbl.OrigOrdDate, OrdDetailsTbl.OrdDay,
OrdDetailsTbl.OrdTime, OrdDetailsTbl.OrigOrdTime,
OrdDetailsTbl.JobDate, OrdDetailsTbl.JobDay,
OrdDetailsTbl.JobTime, OrdDetailsTbl.JobHouseNum,
OrdDetailsTbl.JobStrNm, OrdDetailsTbl.JobStrNumNm,
OrdDetailsTbl.Directions, OrdDetailsTbl.Comments1,
OrdDetailsTbl.Comments2, OrdDetailsTbl.Comments3,
OrdDetailsTbl.Comments4, OrdDetailsTbl.Void,
CustomerTbl.CustBuyerCB, CustomerTbl.CustSellerCB,
CustomerTbl.****edUpComboNum, CustomerTbl.CustLNm1,
CustomerTbl.CustFNm1, CustomerTbl.CustLNm1FNm1,
CustomerTbl.CustCurHmPh, CustomerTbl.CustWrkPh,
CustomerTbl.CustWrkPhExt, CustomerTbl.CustHmFax,
CustomerTbl.CustWrkFax, CustomerTbl.CustFax,
CustomerTbl.CustCellPhone, CustomerTbl.CustPager
FROM ErrorTestTbl INNER JOIN (CustomerTbl INNER JOIN
OrdDetailsTbl ON CustomerTbl.CustomerID =
OrdDetailsTbl.CustomerID) ON ErrorTestTbl.ErrorTestID =
OrdDetailsTbl.ErrorTestID
ORDER BY OrdDetailsTbl.OrdDetailsID;
 
I have included the SQL statement you asked for, I
THINK???. My apologies if it is not what you meant.

I have been chewing on this problem (run time error 3101)
for a couple of weeks now, and it is driving me crazy.

Here is some additional info on the matter:

I can't add a table (ErrorTestTbl) to my query without
getting the 3101 error.

All I do is add the table to the query without referencing
the fields in the table on the form, and I get the error
message.

As long as I don't try to add a new table to my query
everything works fine in my form.

I have tried creating a new form and copying all items to
the new form but I still have the same problem.

I don't need to add a table to my query at the present
time but if I need to in the future, I will be F$%#@*.

I have also repaired and compacted many times.

Thanks for responding.

Leonard

=======================================================

SELECT OrdDetailsTbl.OrdDetailsID,
OrdDetailsTbl.CustomerID,
OrdDetailsTbl.ErrorTestID,
ErrorTestTbl.ErrorTestNm,
OrdDetailsTbl.OrdNumComplete,
OrdDetailsTbl.OrdNumNum,
OrdDetailsTbl.CurRecNum,
OrdDetailsTbl.OrdTakerNm,
OrdDetailsTbl.BillingCityComboNum,
OrdDetailsTbl.BillingHouseNum,
OrdDetailsTbl.BillingStrNm,
OrdDetailsTbl.BillingState,
OrdDetailsTbl.BillingOtherStateComboNum,
OrdDetailsTbl.BillingOtherCity,
OrdDetailsTbl.BillingOtherZip,
CustomerTbl.CustBuyerCB,
CustomerTbl.CustSellerCB,
CustomerTbl.****edUpComboNum,
CustomerTbl.CustLNm1,
CustomerTbl.CustFNm1,
CustomerTbl.CustLNm1FNm1,
CustomerTbl.CustCurHmPh,
CustomerTbl.CustWrkPh,
CustomerTbl.CustWrkPhExt,
CustomerTbl.CustHmFax,
CustomerTbl.CustWrkFax,
CustomerTbl.CustFax,
CustomerTbl.CustCellPhone,
CustomerTbl.CustPager
FROM ErrorTestTbl INNER JOIN (CustomerTbl INNER JOIN
OrdDetailsTbl ON CustomerTbl.CustomerID =
OrdDetailsTbl.CustomerID) ON ErrorTestTbl.ErrorTestID =
OrdDetailsTbl.ErrorTestID
ORDER BY OrdDetailsTbl.OrdDetailsID;
 
Hi,


You supplied the exact error message, but not the text of the SQL
statement. If you switch into SQL view, while you are in the query designer,
you should see something like:


SELECT table1.*, table1.field1
FROM table1
WHERE table1.field2=0


as example. Your SQL text is probably much more complex, and there is
probably an error of syntax in it... that is why it would be nice to get a
look at it. You can easily cut and paste the text: select it, ctrl_C to
place it in the clipboard, ctrl_V to paste it into a newsgroup message.




Vanderghast, Access MVP
 
Hello,

I did include a view of the SQL statement from my query,
just scroll down in my last message to see it.

Thanks again for taking the time to respond.

It is really apprciated.

Leonard
 
Hi,

I should have scrolled down... :-)


I would try to remove the join and to remake it graphically through the
query designer, or, by hand, to change the syntax to :


FROM (CustomerTbl INNER JOIN
OrdDetailsTbl ON CustomerTbl.CustomerID =
OrdDetailsTbl.CustomerID) INNER JOIN ErrorTestTbl
ON ErrorTestTbl.ErrorTestID = OrdDetailsTbl.ErrorTestID



Hoping it may help
Vanderghast, Access MVP
 
I have included the SQL statement you asked for.
See below in this post.

I have been chewing on this problem (run time error 3101)
for a couple of weeks now, and it is driving me crazy.

Here is some additional info on the matter:

I can't add a table (ErrorTestTbl) to my query without
getting the 3101 error.

All I do is add the table to the query without referencing
the fields in the table on the form, and I get the error
message.

As long as I don't try to add a new table to my query
everything works fine in my form.

I have tried creating a new form and copying all items to
the new form but I still have the same problem.

I don't need to add a table to my query at the present
time but if I need to in the future, I will be F$%#@*.

I have also repaired and compacted many times.

Thanks for responding.

Leonard

=======================================================

SELECT OrdDetailsTbl.OrdDetailsID,
OrdDetailsTbl.CustomerID,
OrdDetailsTbl.ErrorTestID,
ErrorTestTbl.ErrorTestNm,
OrdDetailsTbl.OrdNumComplete,
OrdDetailsTbl.OrdNumNum,
OrdDetailsTbl.CurRecNum,
OrdDetailsTbl.OrdTakerNm,
OrdDetailsTbl.BillingCityComboNum,
OrdDetailsTbl.BillingHouseNum,
OrdDetailsTbl.BillingStrNm,
OrdDetailsTbl.BillingState,
OrdDetailsTbl.BillingOtherStateComboNum,
OrdDetailsTbl.BillingOtherCity,
OrdDetailsTbl.BillingOtherZip,
CustomerTbl.CustBuyerCB,
CustomerTbl.CustSellerCB,
CustomerTbl.CustLNm1,
CustomerTbl.CustFNm1,
CustomerTbl.CustLNm1FNm1,
CustomerTbl.CustCurHmPh,
CustomerTbl.CustWrkPh,
CustomerTbl.CustWrkPhExt,
CustomerTbl.CustHmFax,
CustomerTbl.CustWrkFax,
CustomerTbl.CustFax,
CustomerTbl.CustCellPhone,
CustomerTbl.CustPager
FROM ErrorTestTbl INNER JOIN (CustomerTbl INNER JOIN
OrdDetailsTbl ON CustomerTbl.CustomerID =
OrdDetailsTbl.CustomerID) ON ErrorTestTbl.ErrorTestID =
OrdDetailsTbl.ErrorTestID
ORDER BY OrdDetailsTbl.OrdDetailsID;
 
Back
Top