Master and Subform Recordsource Query Returns Error in From Clause: may refer to more than one table 1

[tlafferty]

Hi There -
I have a parent form called [/b]Companies[/b] populated by a Companies table and a subform called People_Subform populated by the People table and linked to the parent form via the People_ID primary key.

I'm using a toggle button to toggle the recordsource for these forms between their native tables and a query, with parameters supplied by text boxes in the header of the Companies form.

If I execute the query from the query window, I get the expected results, but when I try to use the query as a record source for my forms, I get this message:

Run-time error '3079':The specified field 'Company' could refer to more than one table listed in the FROM clause of your SQL statement.

Here's the code behind my toggle button:

Private Sub Toggle_Filter_Companies_AfterUpdate()
    If Toggle_Filter_Companies.Value = -1 Then
        Toggle_Filter_Companies.Caption = "Remove Filter"
        Me.RecordSource = "Companies_Filter_Query"
        Me.People.Form.RecordSource = "Companies_Filter_Query"
        Me.Requery
    Else
        Toggle_Filter_Companies.Caption = "Apply Filter"
        Me.RecordSource = "Companies"
        Me.People.Form.RecordSource = "People"
    End If
    Me.Requery
End Sub

And here's the SQL statement from my query:

SELECT Companies.Company_ID, Companies.Company, Companies.Address, Companies.Address2, Companies.City, Companies.State_Province, Companies.Zip_Postal_Code, Companies.Address_Type, Companies.Use_For_Billing, Companies.Primary, Companies.Website, People.FirstName, People.MiddleName, People.LastName, People.Status, People.Company, People.Job_Title, People.Incomplete
FROM Companies INNER JOIN People ON Companies.Company_ID = People.Company
WHERE (((People.FirstName) Like '*' & [Forms]![Companies]![First_Name_Search] & '*') AND ((People.LastName) Like '*' & [Forms]![Companies]![Last_Name_Search] & '*'));

The textboxes used for search criteria are in the header area of the parent form Companies and are named First_Name_Search and Last_Name_Search. They are unbound.

If I remove the field People.Company from my query, the query returns the expected result in the query window, but it won't populate my subform; it does, however, return the Company to the parent form Companies. When I leave the [People].[Company] field as part of my query, the company field on my Companies parent form returns #Name?, and all employees at that company show in my subform.

What's going on? Suggestions?

Thanks for you help!

Tom

 

[MajP]

my first guess is that the problem, may not be as it appears. It may possibly not be the query. Check the forms filter and orderby properties. They may have gotten set in design time to "Company

 

[tlafferty]

Hi MajP -
I just looked at the property sheet for the master and subforms and the filter and order by properties are blank. Thanks for the suggestion! Other thoughts?

Tom

 

[tlafferty]

OK - so I'm a little closer. I changed People.Company to People.Company AS Business. Now the code doesn't err, the main form returns the correct company name instead of #Name? but the subform doesn't populate... 0_o

 

[MajP]

SELECT 
 Companies.Company_ID, 
 Companies.Company, 
 Companies.Address, 
 Companies.Address2, 
 Companies.City, 
 Companies.State_Province, 
 Companies.Zip_Postal_Code, 
 Companies.Address_Type, 
 Companies.Use_For_Billing, 
 Companies.Primary, 
 Companies.Website, 
 People.FirstName, 
 People.MiddleName, 
 People.LastName, 
 People.Status, 
 People.Company, 
 People.Job_Title, 
 People.Incomplete
FROM 
 Companies 
INNER JOIN 
 People ON Companies.Company_ID = People.Company
WHERE 
 People.FirstName Like '*' & [Forms]![Companies]![First_Name_Search] & '*' 
 AND People.LastName Like '*' & [Forms]![Companies]![Last_Name_Search] & '*'

Sorry I do not see any problem that relates to that error message. Maybe someone else will see an issue. I formatted you code a little. Just to verify if the form is open and you select first and last names, the query window provides the correct result?

 

[tlafferty]

That's correct... I get the correct query result in the query window when the form is open and I've entered criteria. I managed to remove the error that VBA is throwing by aliasing the reference to Company in the people table as shown below.

SELECT
Companies.Company_ID,
Companies.Company,
Companies.Address,
Companies.Address2,
Companies.City,
Companies.State_Province,
Companies.Zip_Postal_Code,
Companies.Address_Type,
Companies.Use_For_Billing,
Companies.Primary,
Companies.Website,
People.FirstName,
People.MiddleName,
People.LastName,
People.Job_Title,
People.Incomplete,
[b]People.Company AS Business[/b]
FROM Companies INNER JOIN People ON Companies.Company_ID = People.Company
WHERE (((People.FirstName)=[Forms]![Companies]![First_Name_Search]) AND ((People.LastName)=[Forms]![Companies]![Last_Name_Search]));

 

[tlafferty]

IS the idea of running a remote session so you can see what I'm looking at off limits? I'm running Team Viewer 9.0 which is free. You can download it from download.cnet.com if you'd like and I'll allow you a remote view of my machine. Hope that doesn't violate forum rules which I just reviewed. Let me know...

Tom

 

[MajP]

That sees a little much. Often people will post a dummy copy with limited data (or notional date) on drop box or another file sharing site. I do it often. It may be one of those cases when you cannot figure out why it fails, so you just take a different approach to accomplish the same thing. There is a few other ways to do this. If you can post a version with the respective forms and queries I can take a look.

 

[tlafferty]

I've uploaded to Google Drive. Here's the link:
[URL unfurl="true"]https://drive.google.com/file/d/0B6UWnUCrVFiBT21sX2lhdUNPbVk/edit?usp=sharing[/url]

This is the full version of the database, and it's very much a work in progress, so be gentle with the ridiculous design stuff you're sure to find ;-)

Take a look at the Companies form and Companies_Filter_Querya (I've built 2 versions of the query to see which one I can get to work. Now the problem I'm having is referring to the subform in code, and still have query problems....

Thanks for looking...

 

[tlafferty]

bump... ;-)

 

[MajP]

Please read

http://access.mvps.org/access/lookupfields.htm

http://dbageek.blogspot.com/2004/10/access-web-evils-of-lookup-fields-in.html

This is just another example of the Evils of Table lookups. You need to get rid of all of them, they will cause nothing but problems.

This is how confusing it is and took me forever to figure it out.
In the company table you have a field called "Company" which is a text field storing the company name. In the People table you have a field called "Company" which is a numeric field storing the company ID but displaying the company name in a table lookup. I cannot explain how bad an idea that is.
Also you need to read about an outer and inner join. This would never have worked because people without emails or phones do not show in your query.

I renamed the "Company" field in the people table to "CompanyID_FK" and got rid of the lookup in the table. Now someone looking at this has an idea of what is going on and that it is a foreign key. You will have to modify your record counter in the subform. You need to link the subform using this new name.

the updated Querya looks like

SELECT 
 People.FirstName, 
 People.MiddleName, 
 People.LastName, 
 People.Job_Title, 
 People.Incomplete, 
 People.People_ID, 
 People.CompanyID_FK
FROM 
  (((Companies INNER JOIN People ON Companies.Company_ID = People.[CompanyID_FK]) LEFT JOIN CRM_Activity ON   People.People_ID = CRM_Activity.People_ID) LEFT JOIN Email_Addresses ON People.People_ID =   Email_Addresses.People_ID) LEFT JOIN PhoneNumbers ON People.People_ID = PhoneNumbers.People_ID
WHERE 
 (((Companies.Company) Like '*' & [Forms]![Companies]![Company_Search] & '*') AND ((People.FirstName) Like '*' & [Forms]![Companies]![First_Name_Search] & '*') AND ((People.LastName) Like '*' & [Forms]![Companies]![Last_Name_Search] & '*'));

Now to create the filtered company query
Companies_Filter_Company

SELECT DISTINCT 
 Companies.Company_ID, 
 Companies.Company, 
 Companies.Address, 
 Companies.Address2, 
 Companies.City, 
 Companies.State_Province, 
 Companies.Zip_Postal_Code, 
 Companies.Address_Type, 
 Companies.Use_For_Billing, 
 Companies.Primary, 
 Companies.Website
FROM 
 Companies 
INNER JOIN 
 Companies_Filter_Querya ON Companies.Company_ID = Companies_Filter_Querya.CompanyID_FK
ORDER BY Companies.Company;

I added the following code to avoid an error when there are no records returned by the filter.

If Toggle_Filter_Companies.Value = -1 Then
        Toggle_Filter_Companies.Caption = "Remove Filter"
        Me.RecordSource = "Companies_Filter_Company"
        If Me.Recordset.RecordCount > 0 Then
          Me.People.Form.RecordSource = "Companies_Filter_Querya"
        End If
        Me.Requery
 Else

You Filter is strange to me as a user interface. You have "ands" between the criteria.
I would think you want to either filter by name or filter by company. I would think a better interface would be two combos on for names and one for company and then two filter toggles "by name" and "By company". In my combo names I would concatenate the name.

Brown, Jack
Smith, Brian
Smith, John
etc

Remember combos are auto find. So if I type in "S" it will scroll to the first S field. Since you have Ands if you get one of them wrong it will not make sense.

Also if it is the case where you might know a first name and not a last name to search then I would have a button next to my people combo to change the recordsource to first name last name. So the data is sorted like

Brian Smith
Jack Brown
John Smith.

Then my filter would be based on a person ID returned by a combo not first name last name. Personally it is far easier for me to scan a list of possible names then type in a guess at a spelling. Example. I think their name is something like Meyers.
So I type Meyers and no hit. Type Meyer and no hit. Type in Myer no hit. Their name is Mayer. Super easy to scroll down a combo box especally when I type in M and it goes to all the M names.

 

[tlafferty]

Thank you so much for your work on this! I will be looking at your recommendations over the next few days and will implement as I'm able. Thanks for pointing out the problems with table lookups - I've never had training in this, so I just made some assumptions about how to develop this. Thankfully, I'm not too far into this, so I can make adjustments.

Thanks again!

Tom

 

[MajP]

Microsoft does a lot of "Favors" for you in Access that are not really favors. Table lookups, not declaring variables, multi-value fields, not enforcing field naming conventions, default methods and properties, etc all seem like nice short cuts. They all can cause severe headaches.

One thing is you have a lot going on in that form. You can search, edit, and add all on the same form. It gets busy and confusing.
I like to separate some of that. I would not allow the user to add a company on this form, but have a button to bring up a company form. You may want a separate form for navigation. I like a search form that has a lot of searching features, then when I find a record it returns me to that record.

For example instead of the filter I may have a button that pops open a tabular form that lists first, last, company, etc. I would make each label sortable so I can choose to sort by any of those fields. That way I can quickly look for a "Michael" or "Jones" or "Company A". Once I find it then I can return to my edit form on that record. You are filtering instead of navigating which makes it a little complicated and confusing. For me having a search that employs a list so you can see all the choices makes it much more intuitive.

 

[Andrzejek]

Along with what MajP said, I assume you have this Access app just for one or two users so you can do all in one Form. Then your supervisor tells you to give it to other 5 people. Then another 15. Then tells you “Only John can introduce a new Company, nobody else”. And “Only John and Susie can edit the data, everyone else can only see (read-only) the information”.
I know it is next to impossible to read your manager’s mind and see the future, but if you start splitting the functions early ‘by default’ – a lot of your life later on will be a lot easier.

Just my $0.02


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.