A2010 run-time error 3079

My application is based on a tblContact, which contains detailed info for each client in our database.  An associated form, frmContact, uses this table for most of its fields, the top of the form is general info and there are 5 tabs for specific info.

I'm adding a new tab which needs to use data in a new table, tblContactTCP.  These 2 tables are connected with a INNER JOIN via the fields tblContact.ContactID and tblContactTCP.TContactID.

SELECT tblContact.*, tblContact.ContactID, tblContactTCP.*
FROM tblContact INNER JOIN tblContactTCP ON tblContact.ContactID = tblContactTCP.TContactID
WHERE (((tblContact.ContactID)>1));


The app also has a form showing a list of all these Contacts filtered by type.  When I go to the list and select the contact I'm interested in and click on the detail button, the first form mentioned opens on the screen.  The code used to open the form is:

DoCmd.OpenForm "frmContact", , , "ContactID = " & Me.frmSub.Form.ContactID, , , "Type = " & Me.cboType

However, after adding the new tab and try to get to the first form I get the error 3079, whether I try the INNER JOIN as the form's RecordSource or just reference the second table via subforms in the new tab I get the error:

Run-time error 3079
The specified fied'ContactID' could refer to more that one table listed in the FROM clause
of your SQL statement.

What am I missing??

Judith
JudithARyanTech SupportAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
Off the top of my head, I don't know what the error message is so I'm going to guess what the problem might be.

The new data is from a many-side table and therefore, although it is fine to show it on a new tab, the new tab should contain a subform that is bound to either the new table or a query of that table.  You do not need to (and should not) include the main form table.  You would set the master child links for the subform control (if Access didn't do it automatically) to link the subform to the main form on ContactID.  Remove this contact table from the main form's RecordSource query.  It will only appear to cause duplicates since the main form record will appear as many times as there are contacts.  So if there are three contacts for customer 123, then if you scroll the main form, you will see three instances of customer 123.
Dale FyeOwner, Developing Solutions LLCCommented:
The query you are using for frmContact must contain data from more than one table which contains the ContactID field, and for some reason contains both of these fields in the query.  There should be no need to included both of those fields in the query, so select the most appropriate one and delete the other one.
JudithARyanTech SupportAuthor Commented:
I think I've already tried what you suggest, but I'll try again.

The error text is shown at the bottom of my question

Thanks,  Judith
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Dale FyeOwner, Developing Solutions LLCCommented:
That error is generally encountered when you try something like:

SELECT TableA.ContactID, TableA.SomeOtherField, TableB.SomeField
FROM TableA INNER JOIN TableB on TableA.ContactID = TableB.ContactID
WHERE ContactID = 23

or

SELECT TableA.ContactID, TableA.SomeOtherField, TableB.Another, TableB.ContactID
FROM TableA INNER JOIN TableB on TableA.SomeField = TableB.SomeField
WHERE ContactID = 23

If you don't specify the table that the ContactID criteria is supposed to be applied to, then that error will occur.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
Judith,
I missed the error text.  I think the others have identified the problem as the field occurring multiple times in the query but the root problem is most likely what I mentioned.   You should be creating a separate subform and the query that is the recordSource for the subform should NOT include the parent table.
JudithARyanTech SupportAuthor Commented:
Actually, I tried a combination of several of your suggestions.  If you look at the SQL statement above you'll see that I did try the SELECT statements that Dale just showed and I did qualify all mentions of ContactID.  However, by deleting the WHERE clause for "ContactID > 1" and not including the second table in the form's RecordSource and by only referencing the second table in the new tab's subforms , the thing worked.

What's strange is that before adding the new tab, the WHERE clause was always there, and it worked that way!

Did I reach some weird limit or level of complexity?

Judith
PatHartmanCommented:
SELECT tblContact.*, tblContact.ContactID, tblContactTCP.*
BOTH tblContact.* AND tblContact.ContactID select the ContactID so this query was always bad.  Perhaps you recently added tblContact.ContactID and that is why it broke?

You never addressed my comments about the new tab requiring a subform.  Are you using a subform?
JudithARyanTech SupportAuthor Commented:
Thanks for all the work you all do for answering questions and get us out of trouble!
Dale FyeOwner, Developing Solutions LLCCommented:
happy to help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.