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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
The error text is shown at the bottom of my question
Thanks, Judith
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
ASKER
Thanks for all the work you all do for answering questions and get us out of trouble!
happy to help.
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.