Run Time Error 3101

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.
 
M

Michel Walsh

Hi,


What is the text of the SQL statement you use?


Vanderghast, Access MVP
 
L

Leonard

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;
 
L

Leonard

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;
 
M

Michel Walsh

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
 
L

Leonard

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
 
M

Michel Walsh

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
 
L

Leonard

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;
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run-time error 3101 : Access is looking for a table that doesn't e 0
Trapping Error 3101 5
Error 3101 for illogical reason 1
Cannot find a matching key. Error 3101. 11
Error 3101 2
form/subform error 2
Error 3101 9
Error 3101 cannot find record in the table with key matching field 1

Top