Can't add table to Query?

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

Leonard

Can anyone tell me why I can't add a table to my query
with getting a 3101 error when I try to add an record in
my form. All I do is add a table to the query without
entering any of the fields in the table to the query. Just
the mere fact of adding the table to the query seems to
really screw up my form.

I have been trying to solve this one for a month now and I
think I am going to end up sucking eggs on this one. I
have been working on this order form DB for a year now and
needless to say I will slash my wrists if I can't solve
this problem.
 
Can anyone tell me why I can't add a table to my query
with getting a 3101 error when I try to add an record in
my form. All I do is add a table to the query without
entering any of the fields in the table to the query. Just
the mere fact of adding the table to the query seems to
really screw up my form.
Click to expand...

Please post the SQL of the query and explain why you need two tables
in the Recordsource of the form. If you're not including any fields,
why include the table? Would a Subform, or a DLookUp, perhaps be
useful? Have you established a Relationship between the two tables?
What are the Primary Keys of the two tables?
I have been trying to solve this one for a month now and I
think I am going to end up sucking eggs on this one. I
have been working on this order form DB for a year now and
needless to say I will slash my wrists if I can't solve
this problem.
Click to expand...

I certainly hope not. If you'll give us some more information
(remember, you've been working on it for a month, but none of the
volunteers here have even SEEN it even once) we'll certainly try to
help.
 
See SQL below.

The second table in the query is a test table only. I am
using it to see if I can solve the problem.

This all started when I tried to change a field from one
table in the query to another table in the same query.
When I did so I started getting the 3101 error... "Can't
find a record in the table '<name>' with key matching
field '<name>'".

I then proceeded to remove all references to the field in
question but no matter what, I kept getting the 3101 error.

I have repacked and repaired the DB many times and also
compiled the DB before I save it. I have tried coping all
the tables, forms and queries to a new DB but I still get
the same error. The only way I can stop the error is to
include the original "second" table in the query and
reference the field in on the form. When I do that all
works fine. I can also stop the error by deleting all but
the primary table in the query, but that is a rather
severe solution which is not acceptable.

So. to try to locate the problem I created a test table
with only two fields. A key field and a text field.
Everything linked up OK in the relationship window and all
seemed to go fine when I added the test table to the
query. I did not add either of the two fields in the test
table to the query proper. I only included the table in
the upper portion of the query design window. When I added
the test table it automatically linked to the primary
table.

But I still get the same error code in my form.

My apologies if I haven't used the correct terminology for
this post. It's like not being lost in your home city but
not being able to tell someone how to get from point A to
point B.

Thanks for taking the time to respond.

I hope you realize the wrist thing was just a joke :)


SELECT OrdDetailsTbl.OrdDetailsID,
OrdDetailsTbl.TestTblID,
TestTbl.TestTblID,
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
FROM TestTbl INNER JOIN OrdDetailsTbl ON TestTbl.TestTblID
= OrdDetailsTbl.TestTblID
ORDER BY OrdDetailsTbl.OrdDetailsID;
 
Back
Top